Re: Long running query

Is there an index on physician beginning with physician_key? Are there useful indexes on the filter columns in claim and month_service, especially a compound index on the several filter columns in claim? Are the data distributions for these tables (UPDATE STATISTICS...) up to date using the recommended suite of commands from the Performance Guide? If not start there. Build missing indexes then update statistics. There are several scripts and my dostats utility available for download from the IIUG Software Repository that will implement the recommended UPDATE STATISTICS commands for you automatically with various options. Dostats is the most powerful and flexible of these and is part of the package utils2_ak. Art S. Kagel ----- Original Message ----- From: Tony Demeis <Tony.Demeis@moh.gov.on.ca> At: 3/19 11:13 > 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