Re: Severe Performance Problem - Infomix 7.3 WGS & HPUX 10.20

eric wrote: > > We're in the process of upgrading from Informix 5 to Informix 7.3 Workgroup > Server but are experiencing severe performance problems. Some programs > which used to take 5 hours to complete are now taking 19 hours, on identical > hardware with identical indexing etc. > > Has anyone had similar problems and can suggest a solution? 1) Follow Juri's advice and post some more configuration data. 2) IDS 7.3x requires a higher level of UPDATE STATISTICS information to perform correctly. The optimizer is much "smarter" but it needs more data to go on than the 5.xx optimizer did. Follow the UPDATE STATISTICS recommendations in the release notes for 7.2 and 7.3 or get my dostats.ec utility from the IIUG Software Repository in the package named utils2_ak. This is the MOST important thing. 3) Set OPTCOMPIND to 0 in the ONCONFIG file, the default is only good for very complex DSS style queries and Data Warehouse servers with few indexes. 4) Once you have taken care of the above if performance is still not right run some of the offending queries with SET EXPLAIN ON. If you have queries with ORDER BY that used to use an index matching an order by clause and now use another index chances are this is actually faster. To find out try the query with an optimizer directive forcing the use of the original index and time the query each way. You can incorporate the optimizer directive in your applications if it helps. If you cannot add directives try setting OPT_GOAL to 0 in the onconfig file and bouncing the engine which will cause the engine to favor using a matching index over sorting to satisfy ORDER BY clauses (use this with care as the 7.xx optimizer is usually right when it decides to use another index and sort rather than use the matching index as 5.xx did). Art S. Kagel

Thank you very much for the advice. The performance degradation appears to be most apparent with straightforward sequential processing i.e. select * from table (no where or order by clauses etc). These type of queries are taking 3 times as long to complete with Informix 7 (WGS). Does this mean anything to you? Thanks again. Art S. Kagel wrote in message <36ED7068.57BB@bloomberg.net>... >eric wrote: >> >> We're in the process of upgrading from Informix 5 to Informix 7.3 Workgroup >> Server but are experiencing severe performance problems. Some programs >> which used to take 5 hours to complete are now taking 19 hours, on identical >> hardware with identical indexing etc. >> >> Has anyone had similar problems and can suggest a solution? > >1) Follow Juri's advice and post some more configuration data. >2)

eric wrote: > Thank you very much for the advice. The performance degradation appears to > be most apparent with straightforward sequential processing i.e. select * > from table (no where or order by clauses etc). These type of queries are > taking 3 times as long to complete with Informix 7 (WGS). Does this mean > anything to you? How are the queries submitted? We experienced some fairly serious performance degredations with code using the 5.x API against a 7.x engine (the bridging layer is quite inefficient). I don't recall that they were 3X slower, but it was some time ago. -Richard

Richard Stanford wrote: > > eric wrote: > > > Thank you very much for the advice. The performance degradation appears to > > be most apparent with straightforward sequential processing i.e. select * > > from table (no where or order by clauses etc). These type of queries are > > taking 3 times as long to complete with Informix 7 (WGS). Does this mean > > anything to you? > > How are the queries submitted? We experienced some fairly serious performance > degredations with code using the 5.x API against a 7.x engine (the bridging > layer is quite inefficient). I don't recall that they were 3X slower, but it > was some time ago. Agreed the SQLRM is very slow. That is why I recommend porting your apps to ESQL/4GL 7.xx first and running them against the 5.x engine and porting the data after everything is tested. 7.xx apps actually run faster against a 5.xx database than 5.xx native apps do, and that without taking advantage of 7.xx extentions. Art S. Kagel

"Art S. Kagel" wrote: > Agreed the SQLRM is very slow. That is why I recommend porting your > apps to ESQL/4GL 7.xx first and running them against the 5.x engine > and porting the data after everything is tested. 7.xx apps actually > run faster against a 5.xx database than 5.xx native apps do, and that > without taking advantage of 7.xx extentions. Hmm, that's useful information. I wish I'd known that a couple of years ago... ah, well. -Richard

On older servers Informix 7.xx work very slower than 5.xx. E.g HP class F, E, D with one processor and <128 MB memory. "Art S. Kagel" wrote: > > Richard Stanford wrote: > > > > eric wrote: > > > > > Thank you very much for the advice. The performance degradation appears to > > > be most apparent with straightforward sequential processing i.e. select * > > > from table (no where or order by clauses etc). These type of queries are > > > taking 3 times as long to complete with Informix 7 (WGS). Does this mean > > > anything to you? > > > > How are the queries submitted? We experienced some fairly serious performance > > degredations with code using the 5.x API against a 7.x engine (the bridging > > layer is quite inefficient). I don't recall that they were 3X slower, but it > > was some time ago. > > Agreed the SQLRM is very slow. That is why I recommend porting your > apps to ESQL/4GL 7.xx first and running them against the 5.x engine > and porting the data after everything is tested. 7.xx apps actually > run faster against a 5.xx database than 5.xx native apps do, and that > without taking advantage of 7.xx extentions. > > Art S. Kagel -- Jarosław Stanisz jareks@galkom.com.pl tel.852-43-25