Re: Performance - Informix Se 7.22

Tom Innes wrote: > > We have recently upgraded SE from SE V5.04 to SE 7.22. At the same > time HPUX was upgraded from 9.04 to 10.10. Since then, all queries > running on the large sales history tables are running about 12X slower. > The problem appears to be related to the creation of temphash overflow > files in DBTEMP. I have tried > > 1) update statistics with all its flavours ( High, low, Medium ), > 2) Re-writing the query to force different access plans > 3) Drop and Re-create of the tables involved > 4) All related HP-Ux Informix patches - Even those for on-line > 5) Sort the lookup tables by their primary index > 6) Removing the order by from the select > What is the value of OPTCOMPIND? > My sample query is a three table join between > 1) Sales History (SH) - 1 Million rows, Record Size 1728 Bytes > 2) Item (IT) - 5,000 Rows, Record Size 480 Bytes > 3) Customer (CU) - 9,000 Rows, Record Size 650 Bytes > > The Access plan is essentialy > > 1) Sequential Scan Customer > 2) Index Read to Sales History on customer > 3) Index Read to Item on item > > The query basically sums sales by customer and Item Group ( on the Item > Table ).i.e. select SA.div_code, CU.terr_code, SA.cust_num, SA.ship_num, > IT.sa_item, sum(SA.sales_1), sum(SA.sales_2) ... sum(SA.sales_12) > from customer CU, sales_hist SA, item IT where blah, blah > > The system almost immediately creates 5 temphash overflow files in > DBTEMP and spends approximately 50 minutes populating these files. The > system then spends the next 12 hrs spinning through these five temphash > overflow files creating a srt file. It is only with a group by and sum, > that the temphash overflow files are created. > > Any in-sight into what creates these files and how they can be prevented > would be greatly appreciated. > > Tom What are the table schemas? What are the indices? Please post. John Carlson Informix DBA WH Smith, Inc.