Re: Performance - Informix Se 7.22

In article <6are8n$6md@news.inforamp.net>, Tom Innes <tinnes@inforamp.net> writes >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 > >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 > sqexplain.out output please + table schemas. Blah, blah mean nothing to me. > >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 -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care