Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    21

    Unanswered: tune the query with more left outer joins

    what are the best tuning ways when more than 2 Left outer joins?

  2. #2
    Join Date
    Nov 2007
    Posts
    21
    I've to tune the below query.
    SELECT A.RUN_DATES_STATIONS_ID,
    A.STATION_MASTER_ID, A.RUN_DATES_ID,B.CALL_LETTERS, B.DMA_ID
    FROM
    CONTRACT.RUN_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
    ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    (A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0' OR
    B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')


    When I searched for Access plan & Optimized query in DB2 command center, I found the below optimized query.

    SELECT Q3.$C4 AS "RUN_DATES_STATIONS_ID", Q3.$C6 AS
    "STATION_MASTER_ID", Q3.$C3 AS "RUN_DATES_ID", Q3.$C1
    AS "CALL_LETTERS", Q3.$C0 AS "DMA_ID"
    FROM
    (SELECT Q2.DMA_ID, Q2.CALL_LETTERS, Q2.IBMSNAP_LOGMARKER,
    Q1.RUN_DATES_ID, Q1.RUN_DATES_STATIONS_ID, Q1.IBMSNAP_LOGMARKER,
    Q1.STATION_MASTER_ID
    FROM CONTRACT.RUN_DATES_STATIONS AS Q1 LEFT OUTER JOIN
    CUSTOMER.STATION_MASTER AS Q2 ON (Q1.STATION_MASTER_ID
    = Q2.STATION_MASTER_ID)) AS Q3
    WHERE ((Q3.$C5 = '1900-01-01-11.16.30.000000') OR (Q3.$C2
    = '1900-01-01-11.16.30.000000'))

    The query is not working and giving an error Q3.$c5, Q3.$c2 are not valid.
    both should refer to IBMSNAP_LOGMARKER field for the two tables.

    Can anyone suggest/improve the query?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure about the syntax problem, but anytime you have a query with an OR predicate, and different table columns are involved in the OR predicate, then sometimes a rewrite with UNION can dramatically improve the performance (so that the OR's are eliminated, or at least they refer to the same column).
    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
    Nov 2007
    Posts
    21
    I tried with UNION but it's taking more time than the original.

    The given query is showing 11535 Timer's On and the UNION shows 16450 Timer On's in the Access Plan Graph.


    Below is the query.

    SELECT A.RUN_DATES_STATIONS_ID,
    A.STATION_MASTER_ID, A.RUN_DATES_ID,B.CALL_LETTERS, B.DMA_ID
    FROM
    CONTRACT.RUN_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
    ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    (A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0' OR
    B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't see your UNION query.

    11535 timerons is not a lot for a query of this type, and it may actually run faster is stats are not accurate. Did you actually run the query?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2007
    Posts
    21
    yes. the query with union is
    SELECT A.RUN_DATES_STATIONS_ID,
    A.STATION_MASTER_ID, A.RUN_DATES_ID,B.CALL_LETTERS, B.DMA_ID
    FROM
    CONTRACT.RUN_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
    ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0'
    UNION
    SELECT A.RUN_DATES_STATIONS_ID,
    A.STATION_MASTER_ID, A.RUN_DATES_ID,B.CALL_LETTERS, B.DMA_ID
    FROM
    CONTRACT.RUN_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
    ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')



    I've also tried with sorting of the 2nd table
    below is the query.

    SELECT  A.RUN_DATES_STATIONS_ID,A.STATION_MASTER_ID,
            A.RUN_DATES_ID,B.CALL_LETTERS,B.DMA_ID from
    CONTRACT.RUN_DATES_STATIONS A  LEFT OUTER JOIN
     (SELECT  STATION_MASTER_ID,CALL_LETTERS,IBMSNAP_LOGMARKER,
            &# 160;      DMA_ID FROM CUSTOMER.STATION_MASTER
    ORDER BY STATION_MASTER_ID) AS B ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    (A.IBMSNAP_LOGMARKER=  '1900-01-01 11:16:30.0' OR
    B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')

    This is taking 12539.84 Timer on's.

  7. #7
    Join Date
    Nov 2007
    Posts
    21
    Is there any chance to improve the performance still? since there are large number of data, it's affecting the execution of time.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Look at the execution plan and determine what operation contributes most to the query cost, then try to eliminate or speed up that operation.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2007
    Posts
    21
    There are few table scans in the Access paths. Please suggest me how to avoid the table scans.

  10. #10
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by anant123123
    [...]
    UNION
    SELECT A.RUN_DATES_STATIONS_ID,
    A.STATION_MASTER_ID, A.RUN_DATES_ID,B.CALL_LETTERS, B.DMA_ID
    FROM
    CONTRACT.RUN_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
    ON
    A.STATION_MASTER_ID=B.STATION_MASTER_ID
    WHERE
    B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')
    You can rewrite this query to an inner join.
    The WHERE-clause requires that a column of table B is equal to a value and is therefore not <NULL>.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by anant123123
    There are few table scans in the Access paths. Please suggest me how to avoid the table scans.
    Are there indexes on the predicates? Keep in mind that DB2 will do a table scan once the number of rows is below a certain threshold, regardless of whether an index exists.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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