Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Unanswered: Long running query

    The following query takes more than 1.5 hours to fetch 90000 rows in CLFO table.

    WITH ELM AS
    (
    Select CF.CL_ID as CL_ID,
    CF.ER as CLER,
    CF.OII as OII,
    CF.RS as RS,
    CF.CA as CA,
    CF.STS_DT as STS_DT,
    CF.STS as STS,
    CF.VFD AS SRC_UPD_TS,
    CF.SRC_ENT_TS AS SRC_ENT_TS,
    CF.OPENING_DATE as OPENING_DATE
    FROM CLFO CF
    INNER JOIN
    (
    SELECT CFI.ER, MAX(CFI.VFD) as MAX_VFD
    FROM
    CLFO CFI
    WHERE
    CFI.Ty_id = (Select Ty_id from TY WHERE NAME = 'CO_CL' AND CFI.STS IN ('A','F','R','D')) AND
    CFI.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000') AND
    (CFI.VTD = TIMESTAMP('9999-12-31-23.59.59.000000')OR
    CFI.VTD >TIMESTAMP('2010-06-24-23.59.59.000000') )
    GROUP BY CFI.ER
    ) B
    ON (CF.ER = B.ER AND CF.VFD = B.MAX_VFD)

    There are indexes on
    CLAIM_ID
    TYPE_ID
    CLAIM_FOLDER_ID
    VALID_FROM_DATE
    EXTERNAL_REFERENCE+VALID_FROM_DATE
    VALID_TO_DATE
    in CLFO table.

    Could you pls. suggest ways to improve the performance ?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is that complete query?

    I think that there is no corresponding closing parenthese with "WITH ELM AS (".
    And there is no select statement which refers ELM.

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    Pls. ignore With CLM and the paranthesis.
    The problem is that the indexes are not being used in this table of 4.8 milion records.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about the following example with index (CF , VFD DESC , STS , Ty_id , VTD) on CLFO (or similar index).

    Code:
    Select CF.CL_ID        -- as CL_ID
         , CF.ER           as CLER
         , CF.OII          -- as OII
         , CF.RS           -- as RS
         , CF.CA           -- as CA
         , CF.STS_DT       -- as STS_DT
         , CF.STS          -- as STS
         , CF.VFD          AS SRC_UPD_TS
         , CF.SRC_ENT_TS   AS SRC_ENT_TS
         , CF.OPENING_DATE -- as OPENING_DATE 
     FROM  CLFO CF 
     INNER JOIN 
           LATERAL
           ( 
           SELECT MAX(CFI.VFD) as MAX_VFD 
             FROM CLFO CFI 
            WHERE CFI.ER = CF.ER
              AND CFI.Ty_id
                  = (Select Ty_id
                       from TY
                      WHERE NAME = 'CO_CL'
                      --  AND CFI.STS IN ('A','F','R','D')
                    )
              AND CFI.STS IN ('A','F','R','D')
              AND CFI.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')
              AND CFI.VTD >  TIMESTAMP('2010-06-24-23.59.59.000000')
           ) B 
       ON  CF.VFD = B.MAX_VFD

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    Thanks. I will try this. Could you pls. let me know if 'Lateral' is required along with the change in the inner query ?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LATERAL

    Please see "DB2 for LUW SQL Reference, Volume 1"
    ---> Chapter 5. SQL queries ---> subselect ---> table-reference ---> Correlated references in table-references

    compare Example 3: and Example 6:


    You can download DB2 manuals in PDF format:
    IBM - DB2 Version 9.7 for Linux, UNIX, and Windows English manuals

  7. #7
    Join Date
    Aug 2010
    Posts
    5
    Thanks a ton. This worked. The query now runs and completed in 15 minutes.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1. My first impression was 15 minutes to fetch 90000 rows was too long.

    It would be necessary to get explain to analyze more.


    2. Some question I had were...

    1) Are there any tablespace scan, except for CLFO CF?

    2) How many rows in CLFO?

    3-1) How many rows in CLFO which satisfy condition
    CLFO.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')?

    3-2) How many rows in CLFO which satisfy condition
    CLFO.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')?
    AND CFI.STS IN ('A','F','R','D')
    AND CFI.VTD > TIMESTAMP('2010-06-24-23.59.59.000000')

    4) Did access of TY optimized?

    ...


    3. Anyhow, the following additional conditions(marked in bold) (and indexes) may improve the performance.
    Code:
    Select CF.CL_ID        -- as CL_ID
         , CF.ER           as CLER
         , CF.OII          -- as OII
         , CF.RS           -- as RS
         , CF.CA           -- as CA
         , CF.STS_DT       -- as STS_DT
         , CF.STS          -- as STS
         , CF.VFD          AS SRC_UPD_TS
         , CF.SRC_ENT_TS   AS SRC_ENT_TS
         , CF.OPENING_DATE -- as OPENING_DATE 
      FROM CLFO CF 
     INNER JOIN 
           LATERAL
           ( 
           SELECT MAX(CFI.VFD) as MAX_VFD 
             FROM CLFO CFI 
            WHERE CFI.ER = CF.ER
              AND CFI.Ty_id
                  = (Select Ty_id
                       from TY
                      WHERE NAME = 'CO_CL'
                      --  AND CFI.STS IN ('A','F','R','D')
                    )
              AND CFI.STS IN ('A','F','R','D')
              AND CFI.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')
              AND CFI.VTD >  TIMESTAMP('2010-06-24-23.59.59.000000')
           ) B 
       ON  CF.VFD = B.MAX_VFD
     WHERE CF.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')
    /*
       AND CF.STS IN ('A','F','R','D')
       AND CF.VTD >  TIMESTAMP('2010-06-24-23.59.59.000000')
    */
    The follwing conditions were not derived directry from your original query.
    But, sometimes that were implicitly assumed in this pattern of query.

    /*
    AND CF.STS IN ('A','F','R','D')
    AND CF.VTD > TIMESTAMP('2010-06-24-23.59.59.000000')
    */


    Additional candidate indexes:
    (VFD DESC) on CLFO
    or
    (VFD DESC , STS , VTD) on CLFO
    or
    (VFD) on CLFO
    or
    (VFD , STS , VTD) on CLFO

  9. #9
    Join Date
    Aug 2010
    Posts
    5
    Actually the query fetched 4.8 million records in 34 minutes. There are no full table scans now.

    Pls. find the answers inline.
    1) Are there any tablespace scan, except for CLFO CF? - No.

    2) How many rows in CLFO? - CLFO contains around 10 million rows

    3-1) How many rows in CLFO which satisfy condition
    CLFO.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')? - 10 million

    3-2) How many rows in CLFO which satisfy condition
    CLFO.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')?
    AND CFI.STS IN ('A','F','R','D')
    AND CFI.VTD > TIMESTAMP('2010-06-24-23.59.59.000000')

    - 4.8 million
    4) Did access of TY optimized?- yes, indexed scan

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is another idea.

    Code:
    Select CF.CL_ID        -- as CL_ID
         , CF.ER           as CLER
         , CF.OII          -- as OII
         , CF.RS           -- as RS
         , CF.CA           -- as CA
         , CF.STS_DT       -- as STS_DT
         , CF.STS          -- as STS
         , CF.VFD          AS SRC_UPD_TS
         , CF.SRC_ENT_TS   AS SRC_ENT_TS
         , CF.OPENING_DATE -- as OPENING_DATE 
      FROM CLFO CF
     INNER JOIN
           TY
       ON  TY.NAME = 'CO_CL'
     WHERE CF.VFD
           = (SELECT MAX(CFI.VFD) as MAX_VFD 
                FROM CLFO CFI 
               WHERE CFI.ER    =  CF.ER
                 AND CFI.Ty_id =  TY.Ty_id
                 AND CFI.STS   IN ('A','F','R','D')
                 AND CFI.VFD   <= TIMESTAMP('2010-08-01-14.52.23.000000')
                 AND CFI.VTD   >  TIMESTAMP('2010-06-24-23.59.59.000000')
             )
       AND CF.VFD <= TIMESTAMP('2010-08-01-14.52.23.000000')

    Make index
    (ER , Ty_id , VFD DESC , STS , VTD) on CLFO

Posting Permissions

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