Severe performance problems since upgrade
_______________________________________________________________ This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. Thank you. _______________________________________________________________ Last fall, we upgraded our IDS to 7.24.UC5, 4GL from 6.xx to 7.20.UD6, and a third party software package written in 4GL called Elite. After the upgrade, there was a severe decline in the performance of certain Elite programs, but no change in others. Of the programs that slowed down, a couple are reports, another updates tables. I realize that there's 3 variables that could have caused the problem, and there's no way now to test each piece individually, but I guess I'd like to know: 1) Are there any known problems with the versions of Informix that we upgraded to? 2) What, if anything, can I do to pinpoint the problem and/or improve performance? Also, we're running on HP-UX 10.20. TIA Colleen
"Colleen M. Morrow" wrote: > > _______________________________________________________________ > > This message and any attachments are intended for the individual or entity > named above. If you are not the intended recipient, please do not read, > copy, use or disclose this communication to others; also please notify the > sender by replying to this message, and then delete it from your system. > Thank you. > _______________________________________________________________ > > Last fall, we upgraded our IDS to 7.24.UC5, 4GL from 6.xx to 7.20.UD6, and > a third party software package written in 4GL called Elite. After the > upgrade, there was a severe decline in the performance of certain Elite > programs, but no change in others. Of the programs that slowed down, a > couple are reports, another updates tables. I realize that there's 3 > variables that could have caused the problem, and there's no way now to > test each piece individually, but I guess I'd like to know: > > 1) Are there any known problems with the versions of Informix that we > upgraded to? > 2) What, if anything, can I do to pinpoint the problem and/or improve > performance? > > Also, we're running on HP-UX 10.20. What IDS or OL version did you upgrade from? Did you run UPDATE STATISTICS as described in the Performance Guide for IDS 7.3 or later (the same text MAY be in the release notes for 7.24 there were different versions of the notes and some did not have the guidelines)? If you have come from OL5.xx the standard UPDATE STATISTICS you are used to from that environment will not suffice for the more sophisticated optimizer in 7.2x. Also you mention 4GL programs, if the affected programs are screen forms you may be experiencing a "feature" problem. OL5's optimizer was not nearly as smart as 7.2x's and tended to be dominated by any ORDER BY clause causing it to almost always select an index that matched the ORDER BY. IDS 7 is smarter and has better stats (if the recommended suite of commands is run) and also 7.2x sorts faster than 5.xx did. Because of all of this it often selects a different index to speed joins and filters knowing it can sort very quickly. The end result is a query that runs faster overall but because of the sort returns the first few rows more slowly than 5.xx would have. In 7.24 and later there are the optimizer directives FIRST_ROWS and ALL_ROWS which control this behavior. With FIRST_ROWS set (SET OPTIMIZATION FIRST_ROWS or placing the inline directive {FIRST_ROWS} in the query string) the optimizer will favor query paths that return the first data rows early in the process, which normally means avoiding sorts if possible, even if the total cost of the query is higher. SET OPTIMIZATION ALL_ROWS is the normal setting but the default can be controlled in 7.30 or later with an onconfig parameter. Art S. Kagel
My top suspects would be 1) You didn't do a full update stats 2) You are using kaio Neil Colleen M. Morrow wrote in message <8gjav2$72f$1@news.xmission.com>... > > >_______________________________________________________________ > >This message and any attachments are intended for the individual or entity >named above. If you are not the intended recipient, please do not read, >copy, use or disclose this communication to others; also please notify the >sender by replying to this message, and then delete it from your system. >Thank you. >_______________________________________________________________ > >Last fall, we upgraded our IDS to 7.24.UC5, 4GL from 6.xx to 7.20.UD6, and >a third party software package written in 4GL called Elite. After the >upgrade, there was a severe decline in the performance of certain Elite >programs, but no change in others. Of the programs that slowed down, a >couple are reports, another updates tables. I realize that there's 3 >variables that could have caused the problem, and there's no way now to >test each piece individually, but I guess I'd like to know: > >1) Are there any known problems with the versions of Informix that we >upgraded to? >2) What, if anything, can I do to pinpoint the problem and/or improve >performance? > >Also, we're running on HP-UX 10.20. > >TIA >Colleen >
Neil, After 3 years with 7.24UC4 under HP-UX 10.20, no probs with KAIO on. IMHO, UPDATE STATISTICS + application design. Regards, Sergey Neil Truby <ntruby@netcomuk.co.uk> wrote in message news:8gk5a4$7ta$1@taliesin2.netcom.net.uk... > My top suspects would be > > 1) You didn't do a full update stats > 2) You are using kaio > > Neil > > > > >Also, we're running on HP-UX 10.20. > > > >TIA > >Colleen > >
Neil, After 3 years with 7.24UC4 under HP-UX 10.20, no probs with KAIO on. IMHO, UPDATE STATISTICS + application design. Regards, Sergey Neil Truby <ntruby@netcomuk.co.uk> wrote in message news:8gk5a4$7ta$1@taliesin2.netcom.net.uk... > My top suspects would be > > 1) You didn't do a full update stats > 2) You are using kaio > > Neil > > > > >Also, we're running on HP-UX 10.20. > > > >TIA > >Colleen > >