Re: IDS 9.4 Slower then IDS 7.31

This is a not written anywhere but is a known bug in 9.4 and is not platform specific. I have struggled with open cases to IBM for the past year and a half and finally they admitted it. From what I can gather it has been sent back to programming to "rework" the engine. Until then, if you can get hold of Mark Scranton he will be able to give you some new variables to put in your onconfig. One thing you did not mention is what kind of hardware you are using and if you are using any kind of SAN/NAS technology. This would be helpful information to better understand your disk/dataspace layout. On 3/22/06, Obnoxio The Clown <obnoxio@serendipita.com> wrote: > > > cgates said: > > We just recently upgraded from IDS 7.31-UC5 to IDS 9.4-FC6. We have a > > handful of queries that are running orders of magnitude longer under > > 9.4 than they did in 7.31. I'm noticing pour SQL performance in a > > couple of areas when compared to 7.31. > > > > One is index searches with lots of duplicate key values combined with > > other filters. I have a query that joins a 500 million row table with > > a forty row table. The large table does an indexed search to isolate > > 21 million records, and then filters those based on the forty rows plus > > a hand full of other filters. 7.31 did this in 30-60 minutes, 9.4 is > > taking approximately 15 hours. I compared the table structure, > > indexing, distribution data and query plan. They are all identical. > > If I select the 21 million into a temp table first and then join the > > temp table to the 40 row table and apply the other filters, 9.4 gets > > back down into the 30 to 60 minute range. > > > > The other area of concern has to do with nesting function calls and > > sub-selects. I put together a temp table with about four million rows > > in it. The table contains a decimal values and a join key to a 5 > > million row permeate table. If I do a straight inner join the query > > runs in under 10 minutes. If I add a sum() on the decimal values the > > runtime only goes up by a few minutes. If I add an nvl() on the > > decimal value, again the runtime only goes up by a few minutes over the > > original time. If I nest the sum() inside the nvl() the run time goes > > up by a factor of three. If I change the inner join into a sub-select, > > the run time goes up to several hours. > > > > I have been digging through the IDS documentation looking for something > > in the IDS config that might be causing this. So far I haven't been > > able to find anything that makes any difference. > > > > Any insight that anybody might have would be welcomed. > > UPDATE STATISTICS?> > -- > Bye now, > Obnoxio > > "It's easier with pictures." > -- Cosmo > > "But wait, it gets worse." > -- Cosmo > > "Run, don't walk, for the nearest exit." > -- Cosmo > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- ------------------------------------------------------------------------------------------------ This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that arrangements can be made for proper delivery, and then please delete the message from your inbox. ------------------------------------------------------------------------------------------------