Performance - Informix SE 7.22
Posted in 1998
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 toforce 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