Re: Performance - Informix SE 7.22

Joe Vigil wrote: > > 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 was> comparing 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 We've been having a similar problem with both 7.12 and 7.23. We found that if we did an explicit 'update statistics for procedure', that the problem went away.