Re: Performance - Informix SE 7.22

We did much of the same. but on an AIX box (3.2.5 to 4.2.1) SOME of the queries ran WAY slower (but not all). We used SET EXPLAIN ON to figure out what index path was being used. In some cases, SET OPTIMIZE LOW did the trick. In another case, I wascomparing a char field to a numeric variable and changing that variable worked. What a high PITA factor it was! On 16 Jan 1998 01:48:19 GMT, Tom Innes <tinnes@inforamp.net> wrote: >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