Long running query
Hi, I was wondering if anyone could explain the reason for this extremely long running query. Hope the following is enough info to go on. ------------------------ Query: select c_claim_mnthsv.fiscal_start_yr, claim.fee_schedule_code, claim.fee_sched_suffix, SUM ((claim.number_of_services + claim.number_of_units )), claim.group_num_ind_cd, c_phys_bil.phy_county_cd from claim, month_service c_claim_mnthsv, physician c_phys_bil where ((c_claim_mnthsv.fiscal_start_yr = 2000) and (claim.fee_schedule_code IN ('X184', 'X185', 'X186', 'X187', 'X192', 'X194', 'X201')) and (claim.fee_sched_suffix IN ('A', 'B')) and (claim.amount_paid <> 0.00)) and claim.phy_billing_key = c_phys_bil.physician_key and c_claim_mnthsv.service_period = claim.service_period group by 1, 2, 3, 5, 6 ; ------------------------ Results from sqexplain.out: Estimated Cost: 296993 Estimated # of Rows Returned: 9 Maximum Threads: 6 Temporary Files Required For: Group By 1) informix.c_phys_bil: SEQUENTIAL SCAN 2) informix.claim: INDEX PATH Filters: (informix.claim.fee_schedule_code IN ('X184' , 'X185' , 'X186' , 'X187' , 'X192' , 'X19 4' , 'X201' )AND (informix.claim.fee_sched_suffix IN ('A' , 'B' )AND informix.claim.amount_paid != $ 0.00 ) ) (1) Index Keys: phy_billing_key (Parallel, fragments: ALL) Lower Index Filter: informix.claim.phy_billing_key = informix.c_phys_bil.physician_key NESTED LOOP JOIN 3) informix.c_claim_mnthsv: INDEX PATH (1) Index Keys: fiscal_start_yr Lower Index Filter: informix.c_claim_mnthsv.fiscal_start_yr = 2000 DYNAMIC HASH JOIN Dynamic Hash Filters: informix.claim.service_period = informix.c_claim_mnthsv.service_period ------------------------ Table Info: Claim # of rows for year 2000: 200,000,000 total # of rows in table: 800,000,000 month_service # of rows: 50 physician # of rows: 190,000 Thank you, Tony
Problem is that Canadians are not so healthier people. 200 Millions claim per year/divided 30 Million inhabitants... yep they get sick too often, that is the problem :) -----Original Message----- From: Demeis, Tony [mailto:Tony.Demeis@moh.gov.on.ca] Sent: Wednesday, March 19, 2003 9:00 AM To: ids@iiug.org Subject: Long running query [763] Hi, I was wondering if anyone could explain the reason for this extremely long running query. Hope the following is enough info to go on. ------------------------ Query: select c_claim_mnthsv.fiscal_start_yr, claim.fee_schedule_code, claim.fee_sched_suffix, SUM ((claim.number_of_services + claim.number_of_units )), claim.group_num_ind_cd, c_phys_bil.phy_county_cd from claim, month_service c_claim_mnthsv, physician c_phys_bil where ((c_claim_mnthsv.fiscal_start_yr = 2000) and (claim.fee_schedule_code IN ('X184', 'X185', 'X186', 'X187', 'X192', 'X194', 'X201')) and (claim.fee_sched_suffix IN ('A', 'B')) and (claim.amount_paid <> 0.00)) and claim.phy_billing_key = c_phys_bil.physician_key and c_claim_mnthsv.service_period = claim.service_period group by 1, 2, 3, 5, 6 ; ------------------------ Results from sqexplain.out: Estimated Cost: 296993 Estimated # of Rows Returned: 9 Maximum Threads: 6 Temporary Files Required For: Group By 1) informix.c_phys_bil: SEQUENTIAL SCAN 2) informix.claim: INDEX PATH Filters: (informix.claim.fee_schedule_code IN ('X184' , 'X185' , 'X186' , 'X187' , 'X192' , 'X19 4' , 'X201' )AND (informix.claim.fee_sched_suffix IN ('A' , 'B' )AND informix.claim.amount_paid != $ 0.00 ) ) (1) Index Keys: phy_billing_key (Parallel, fragments: ALL) Lower Index Filter: informix.claim.phy_billing_key = informix.c_phys_bil.physician_key NESTED LOOP JOIN 3) informix.c_claim_mnthsv: INDEX PATH (1) Index Keys: fiscal_start_yr Lower Index Filter: informix.c_claim_mnthsv.fiscal_start_yr = 2000 DYNAMIC HASH JOIN Dynamic Hash Filters: informix.claim.service_period = informix.c_claim_mnthsv.service_period ------------------------ Table Info: Claim # of rows for year 2000: 200,000,000 total # of rows in table: 800,000,000 month_service # of rows: 50 physician # of rows: 190,000 Thank you, Tony