temporary tables-update statistics

Hi All, A few weeks ago we have migrated from IDS 7.31 to 9.30. Generally, the performance is much better but a couple of options in our application work dramatically slower. We extracted sql code from sqexplain.out files (we haven't access to 4GL programs source code) and saw that the problem concerns temporary tables : optimizer choose different ways depending on existing update statistic statement on temporary table. Without update statistics on temp table the query takes few minutes comparing to few seconds with update. In 7.31 IDS environment the same programs work fast. Is there any solution to avoid source code modification ? Thanks in advance for your help.

When you did your migration, did you drop and re-execute all of your update statistic statements over again? A new optimizer was put in place within 9.30 (if I remember right). You probably need to look at your onconfig file as well. Unless you are dealing with an older compiler, you should tell the optimizer to base all queries on cost alone. This should be done BEFORE re-running your Update Statistics. Regarding your queries with temp tables, we did not experience this in 9.40 (we skipped 9.30) so there may be a few bugs in some of the early versions of 9.30. Check to see that you are on the latest 9.30 version if you cannot upgrade to 9.40. Also in 9.40, you can do dynamic set explains on running processes on the fly, without coding in a set explain with the programs. Look at the onmode command for the syntax. Hope all this helps. Clifton "JACEK JAKUB...." <jacek.tomasz.jakubowicz@citigroup.com> wrote: Hi All, A few weeks ago we have migrated from IDS 7.31 to 9.30. Generally, the performance is much better but a couple of options in our application work dramatically slower. We extracted sql code from sqexplain.out files (we haven't access to 4GL programs source code) and saw that the problem concerns temporary tables : optimizer choose different ways depending on existing update statistic statement on temporary table. Without update statistics on temp table the query takes few minutes comparing to few seconds with update. In 7.31 IDS environment the same programs work fast. Is there any solution to avoid source code modification ? Thanks in advance for your help.