Re: Performance - Informix SE 7.22

I've run into similiar problem and found that the problem was with the way the optimizer was looking at the order of the indexes within the index list (systables). EX: select * from table1 where col1 = 1; I have two indexes on the table ione on table1 (col2,col1) and itwo on table1 (col1). The only thing that I could figure was that because index ione came first in the index list and COL1 was a part of that index, the optimizer was using that index first and causing the query to run sequentially. My solution was to drop the indexes and rebuild them in the opposite order. Now when I ran the query, the optimizer used the proper index and the query ran in seconds instead of minutes. Nick Leporacci, Jr. Principle System Analyst State of Rhode Island Tom Innes wrote in message <69me93$j0f@news.inforamp.net>... >We recently upgraded from Version 5 of the SE to version 7. At the same >time HP UX was upgraded from 9.04 to 10.10. > >When this occurred queries against the large sales tables are now >running approximately 12X slower. ( 14 hrs ) The engine is creating >temphash overflow files ( 5 ) in the DBTEMP directory. 98% of the >runtime exists in in spinning through these files creating srt files. >The query is a three table join with a 5 column order by and a 5 column >group by. When the order by and group by are removed the run time is 40 >minutes. > >Update statistics have had no effect. The query has been re-written to>force at least two different access plans with no decrease in run-time. >The system is not swapping and tests have been performed with no other >system activity. > >Using temp tables is not practical as these queries are dynamically >created by the user community, with limited technical experience. > >Any in-sight into what could be causing the problem or why these >temphash overflow files are being created would be greatly appreciated. > >Tom