IDS 9.4 Slower then IDS 7.31

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. Thanks Corey

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