Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Query Optimization

    Hello All,

    1) Need to know general techniques for DB2 SQL query optimization?

    2) What are the tools available in the IBM Mainframes OS/390 that supports in query optimization?

    Thanks.
    Last edited by prakash5686; 05-29-10 at 11:08.

  2. #2
    Join Date
    Jul 2009
    Posts
    150

    Red face

    Quote Originally Posted by prakash5686 View Post
    Hello All,

    1) Need to know general techniques for DB2 SQL query optimization?

    2) What are the tools available in the IBM Mainframes OS/390 that supports in query optimization?

    Thanks.
    You have to use indexes.
    You can check how your query works, using explain or visual explain.
    Some tools available in Platinum, also.

    Kara

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    You will have to use vendor products such as CA CDB2 tools or BMC PMT for query optimization in z/OS applications. If you don't want any of these vendor products, you may have create plan tables and execute explain statements through SPUFI or QMF.

  4. #4
    Join Date
    May 2010
    Posts
    21
    First, joins are expensive. If we take two tables with 3 collumns each and perform a join, the system must generate a table with 9 rows and then rule out 6 rows. If we want to optimize, and it is a pain, then we select from the two tables first. As an example, if we select from the two tables first so that the two tables are 2 rows each, the system then generates 4 rows and filters out 2. This example is clear though. Instead of generating 9 rows, the system generated 4. Clearly more efficient. However, this gets ugly quick. Also, refrain from wildcards in queries where possible. This results in scanning rather than indexing.
    Last edited by jkuyken; 06-02-10 at 11:42.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jkuyken, I have to disagree with most everything you posted. Joins are not expensive. You can write inefficient joins just like you can write any inefficient SQL. It is more expensive to open 2 cursors and 'join' the rows in an application.

    If you have values that will filter out rows, DB2 can do the filtering before doing the join.

    In general, I would first try to:

    Eliminate any table space scans (on tables with significant number of rows).
    Eliminate or reduce any sorting.
    Encourage index usage
    (This could include adding one or more indexes, adding the first key index value to the query that is only using the second value of a composite index, determine if index keys can be in a different order, etc.)

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Irrespective of table size, this is a good thing. If the small tables in question are accessed frequently, then becomes important.

    Quote Originally Posted by Stealth_DBA View Post
    Eliminate any table space scans (on tables with significant number of rows).
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    May 2010
    Posts
    21
    Stealth DBA, the following would seem to support what you are saying:

    When and why are database joins expensive? - Stack Overflow

    But in practice, on our system:

    Code:
    select a.f1,b.f2
    from (select f1 from a where cond1) a
    inner join (select f2 from b where cond2) b
    on a.pk = b.fk
    is much faster than:

    Code:
     select a.f1, b.f2
    from a
    inner join b on a.pk = b.fk
    where a.f1 = cond1 and b.f2 = cond2
    I imagine table structure and system setup greatly influence this, and it is not supposed to be true, but when I look at actual query execution times it seems that I can manually optimize better than whatever the standard optimization plan that occurs in our system. Perhaps the system is not setup properly. We all use what we have and it takes trying both to know. You should be right.

    In truth I rarely bother with the efforts of manual tuning because the layered selects is ugly and unmanageable when changes are needed. Do they run faster? On my system yes.

    Eliminate any table space scans (on tables with significant number of rows).
    Eliminate or reduce any sorting.
    Encourage index usage
    (This could include adding one or more indexes, adding the first key index value to the query that is only using the second value of a composite index, determine if index keys can be in a different order, etc.)
    This doesn't conflict with what I have said. And I would not advocate denormalizing the tables into one table. I would rather write clear code, take the performance hit and run the long querys on a schedule after typical business hours, but the truth is that an intelligent programmer can sometimes make better optimization decisions based on his data and system than a standard predetermined optimization routine performed by the system.

    The following site suggests precisely what I am mentioning reducing the set size on which work is done:
    http://www.sql-server-performance.co..._joins_p1.aspx

    http://www.devx.com/dbzone/Article/26995/1954 states that "[f]or complex queries involving joins between eight different tables, the optimizer could spend as much as 30 minutes to find an effective execution path before the server actually executes the query." This may mean that my tuned query is not really any quicker, but it executes 30 minutes faster because the effective execution path is immediately clear.
    Last edited by jkuyken; 06-02-10 at 14:08.

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Irrespective of table size, this is a good thing. If the small tables in question are accessed frequently, then becomes important.
    sathyaram_s, I should have been more specific. What I was trying to indicate is that for tiny code tables with all the rows on 1 page, you will have a table space scan that does not need to be eliminated.

    jkuyken, interesting example. Most of my experience is with the 'old sytle' join with the join predicate in the Where clause.

    In your example, this might work as good as your 2 nested tables:
    Code:
    select a.f1, b.f2
    from a
           inner join 
         b 
           on     a.pk = b.fk
              and a.f1 = cond1 
              and b.f2 = cond2
    By moving the extra filter predicates out of the Where clause and putting with the ON clause, DB2 should be able to filter while joining instead of completely joining the tables first and then filtering the rows.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, to rid your self of the high prepare times you could look at going with SQLJ or moving your SQL into a stored procedure. In this way your statements are bound to the database and access path is preselected. I have seen many queries that are joining up to 40 tables and they run subsecond. As to your issues with the queries above I am really suprised that the optimizer did not rewrite them to where they were pretty much the same, you should be taking a look at your explain to see why the difference in execution times.
    Dave

Posting Permissions

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