Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Angry Unanswered: Performance issue with Outer Joins

    Db2 8.1.5 on Windows 2000

    I'm looking for help with problems with very slow running queries. These have been narrowed down to an issue with OUTER JOINs.

    For example,
    FROM
    ...
    TAB1
    LEFT OUTER JOIN TAB2 ON TAB1.COL1 = TAB2.COL2,
    ...
    where there are indexes on both TAB1.COL1 and TAB2.COL2
    The query can be speeded up (though it loses rows) by changing this to
    FROM
    ...
    TAB1
    INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL2,
    ...

    The performance difference is a factor of around 20. I have looked at the explain plans for the 2 versions and the difference is that when the LEFT OUTER JOIN is used, a table scan of TAB2 is performed whereas for the INNER JOIN, the index on TAB2 is used.

    The problem does not relate just to this one query but seems to be inherent in the use of OUTER JOINs. Do you know of any way we can tune DB2 so that is uses indexes to process OUTER JOINs or is just the way the database works?

    Newbie

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    left outer join most of the time read the full outer table in full table scan mode. so increasing the prefetch size and other config parameter
    like IO* agents will help. similarly table of that sort ishould have been striped in multiple containers and in their own buffer pool is also helpful.
    Similarly applying the predicate to the outer join table will decrease the no of
    rows.
    But rewriting of the query is the most helpful.

    regards,

    mujeeb

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You might want to try to execute runstats with distribution on all columns (or at least key columns), and make use “indexes all” also.

    If that does not work, try to increase the query optimization level to 7 (instead of default of 5). Increasing query optimization level has no runtime impact on statically bound packages that are bound ahead of time, and a slight impact on dynamic SQL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Quote Originally Posted by dsusendran
    For example,
    FROM
    ...
    TAB1
    LEFT OUTER JOIN TAB2 ON TAB1.COL1 = TAB2.COL2,
    ...
    where there are indexes on both TAB1.COL1 and TAB2.COL2
    Outer joins will NEVER exclude any rows, so the join is always going to involve a full table scan.

    To tune your queries, you will have to reduce the number of rows that will be involved in the outer join (by filtering rows in a prior, inner join or possibly outer joining a nested select).

    Post a complete example of a query that is causing problems and I'm sure it can be tuned by someone here to perform satisfactorily.

    Damian

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Lightbulb

    Thank you all for valuable suggestions. Here is a sample query:

    select
    B.LAST_NAME,
    B.FIRST_NAME,
    D.NAME,
    C.CITY,
    C.STATE_PROVINCE,
    B.BUSINESS_PHONE,
    B.BUSINESS_PHONE_EXT,
    F.S_LYR_TOT,
    F.A_CURR_TOT,
    F.S_YTD_TOT,
    A.CONTACT_ID,
    B.CONTACT_ID,
    C.CONTACT_ADDRESS_ID,
    D.FIRM_ID,
    E.OFFICE_ID,
    F.CONTACT_ID
    from
    DPS.REP_PROFILE A
    inner join DPS.CONTACT B on ( A.CONTACT_ID = B.CONTACT_ID )
    left outer join DPS.CONTACT_ADDRESS C on ( B.ADDRESS_1_ID = C.CONTACT_ADDRESS_ID )
    left outer join DPS.FIRM D on ( A.FIRM_ID = D.FIRM_ID )
    left outer join DPS.OFFICE E on ( A.OFFICE_ID = E.OFFICE_ID )
    left outer join DPS.REP_CONSOLE F on ( A.CONTACT_ID = F.CONTACT_ID )
    where
    C.COUNTRY_ID = '0001'
    order by
    B.UPPER_LAST_NAME asc,
    B.UPPER_FIRST_NAME asc with UR;

    Newbie

  6. #6
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking Forgot to add:

    Is UNION ALL a better choice?

    Thanks in Advance.

    Newbie

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I reckon that you could repace one of the outer joins in your query with an inner join. CONTACT_ADDRESS is the null supplying table in an outer join, but you are only interested in records where the country id from this same table is '0001'.

    Code:
    select ...
    from DPS.REP_PROFILE A 
      inner join DPS.CONTACT B 
        on  A.CONTACT_ID = B.CONTACT_ID
      inner join DPS.CONTACT_ADDRESS C 
        on  B.ADDRESS_1_ID = C.CONTACT_ADDRESS_ID 
        and C.COUNTRY_ID = '0001'
      left outer join DPS.FIRM D 
        on A.FIRM_ID = D.FIRM_ID
      left outer join DPS.OFFICE E 
        on A.OFFICE_ID = E.OFFICE_ID
      left outer join DPS.REP_CONSOLE F 
        on A.CONTACT_ID = F.CONTACT_ID
    order by 
    B.UPPER_LAST_NAME asc, 
    B.UPPER_FIRST_NAME asc with UR;

  8. #8
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    Thank you Damian. Actually it was just a sample query. I need to build queries similar to that. I noticed that "UNION ALL" does seem to be helpful.

    Thanks,
    Newbie

Posting Permissions

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