Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: Regarding Query Optimization

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Kota
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Posts
    127
    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.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Kota
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Jun 2003
    Posts
    127
    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 ...

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    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

  7. #7
    Join Date
    Jun 2003
    Posts
    127
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •