I have a couple of queries which are running slow. Inside the java code, I have set different query optimization levels for each. That way there is a significant improvement in performance. Is this a good practice? With change in data (not sure how it might change), is there a chance of decrease in performance?

Thanks
Srini

It's a trade-off, and depends on the query. Higher optimization takes longer, but you'll have to benchmark it to find out which is faster.

If the data changes and the stats are current, it really shouldn't matter too much. If there's more data, the higher optimization may provide even more of an advantage, if anything.

Note that you may want to consider SQLJ... then you can bind with a very high optimization level without negatively effecting run-time. May not be suitable for your environment, but worth looking at.

Actually I did do extensive benchmarking. Since its a java code, we tried setting optimization levels using JDBC obviously. The query time came down from 5s to 200ms for that query. We were only concerned about any negative impact that it might have.
Also want to confirm about a registry variable named
DB2_EXTENDED_OPTIMIZATION. This registry variable specifies whether or not the query optimizer will use optimization extensions to improve query performance. I haven't found much documentation about how its going to affect the other applications or this particular query with data changes ....
Appreciate your help.

I'm not familiar with DB2_EXTENDED_OPTIMIZATION, maybe someone else could comment on that.

Typically the only penalty of higher optimization is the longer prep time. You are allowing the optimizer to consider more access plans before chosing which one it thinks is optimal. This does not guarantee a faster access plan, but that's why they tell you to benchmark. It will give you a plan with a lower or equal "timeron" estimate, whether it is actually faster depends on how closely the real work matches what you've told the optimizer (ie. statistics, CPUSPEED, etc.)

And sometimes the optimizer coughs up a hairball... but that can happen at any optimization level...

Just my thoughts/experience. If it's faster, I'd stick with it.

Appreciate your help. I think since it improved the performance, will go with the new optimization level as its a significant difference (5 sec to 120 ms).

Thanks ...

use db2batch to find out how much time it spends on preparing and running the query, also you can change the query class , but I strongly recommend that every dba should read the description about the query class.

regards,

mujeeb

Mujeeb, Thanks for the reply. Yes based on the doc. and benchmarks, I concluded about changing. Yes db2batch and some java benchmark programs were run. Anyway thanks for the suggestions.