Long running query

hidnana · #1 (**permalink**) 08-05-10, 09:17

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 ?

tonkuma · #2 (**permalink**) 08-05-10, 09:54

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.

hidnana · #3 (**permalink**) 08-05-10, 09:57

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.

tonkuma · #4 (**permalink**) 08-05-10, 10:49

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

hidnana · #5 (**permalink**) 08-05-10, 11:27

Thanks. I will try this. Could you pls. let me know if 'Lateral' is required along with the change in the inner query ?

tonkuma · #6 (**permalink**) 08-05-10, 12:18

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

hidnana · #7 (**permalink**) 08-06-10, 05:03

Thanks a ton. This worked. The query now runs and completed in 15 minutes.

tonkuma · #8 (**permalink**) 08-06-10, 14:51

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

hidnana · #9 (**permalink**) 08-11-10, 05:08

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

tonkuma · #10 (**permalink**) 08-11-10, 11:37

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

Internet Services

Web Development

Digital Marketing

Consumer Tech