Re: Performance - Informix SE 7.22

In article <69me93$j0f@news.inforamp.net>, Tom Innes <tinnes@inforamp.net> writes >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. > Give us a) tbale structures b) SET EXPLAIN ON output... >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 -- 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