Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: How to fine-tune a query?

    Hi:

    Recently I have been looking into our production environment and trying to figure out the problem with slow performance. One of the variable in this exercise is to look at existing queries and fine-tune them. My question is how?
    1. Re-write the queries (this is what I would like to do)
    2. Evaluate the access plan and do something
    3. Update statistics (REORG, RUSTATS)
    4. Update db or dbm cfg parameters

    Are there any set guidelines which helps someone write good queries? Question is vague so feel free to comment.

    dollar

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to fine-tune a query?

    My approach to your question on exisiting queries:

    First make sure your statistics are OK and do a REORG.
    Then evaluate the access plans and try to make changes to a part of the query to improve it
    You may consider tuning some db/dbm parameters (like query degree, parallelism, sortheap, queryopt etc and bufferpool size, tablespace-bufferpool mapping, indexes etc) and also OS related stuff like I/O ..

    If you think the query is very badly written, yes, you can consider a rewrite .. Rewriting all existing queries, is a very tedious and timeconsuming process ...


    As a quick note , for new queries :

    1) SELECT only the columns you want
    2) Create suitable indexes and keep statistics upto date and define appropriate bufferpools
    3) Minimize sorting (reduce DISTINCT, ORDER BY, use UNION ALL instead of UNION whenever possible)
    4) Primary-Foreign Key constraints columns are properly indexed
    5) For dynamic sql, Whenever possible, use parameter markers to avoid compilation overhead

    There could be more points, but I think these are the most basic and important ones

    You may want to refer Peter Gulutzan's SQL Performance tuning book ... Will cost you about $40-50



    Originally posted by dollar489
    Hi:

    Recently I have been looking into our production environment and trying to figure out the problem with slow performance. One of the variable in this exercise is to look at existing queries and fine-tune them. My question is how?
    1. Re-write the queries (this is what I would like to do)
    2. Evaluate the access plan and do something
    3. Update statistics (REORG, RUSTATS)
    4. Update db or dbm cfg parameters

    Are there any set guidelines which helps someone write good queries? Question is vague so feel free to comment.

    dollar
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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