Query performance needs to be increased

shravani3752 · #1 (**permalink**) 05-23-11, 09:53

Hi All,

I am new to DB2, I am running a query in DB2 on mainframes under production environment. But it is running for long time approx 2+ hrs. I am using 3 tables in the query and using simple join on the tables.

Can some one help me to show the methods to reduce the running time for query...

Thanks in advance,
Sravni.

n_i · #2 (**permalink**) 05-23-11, 13:10

One of the most effective methods is to add a WHERE clause to your query.

schintala · #3 (**permalink**) 05-23-11, 15:00

Can you share your SQL here to see how the tables are joined? Check your indexes defined on the columns used in join/where.

shravani3752 · #4 (**permalink**) 05-24-11, 02:09

Quote:

Originally Posted by schintala

Can you share your SQL here to see how the tables are joined? Check your indexes defined on the columns used in join/where.

Sure. Here is my sql:
SELECT
CAST(DIGITS(AU.CUS_ID_NO) AS CHAR(12))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,AU.BUS_NM
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_LN_ONE_AD
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_MEM_LN_AD
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_CTY_NM
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_STE_PVN_CD
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_ZIP_PTL_CD
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,CAST(DIGITS(PA.CUS_PHN_ARE_CD) AS CHAR(4))
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,CAST(DIGITS(PA.CUS_PHN_NO) AS CHAR(8))
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST(AU.CLS AS CHAR(3))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,AU.PRY_SRS_CNT_PRS
,CAST('"' AS CHAR(1))
FROM DB2NAD8.NADXTNM NM,
DB2NAD8.NADXTPA PA,
DB2NAD8.NADXTAU AU
WHERE NM.AD_ID_NO = AU.AD_ID_NO
AND NM.CUS_ID_NO = AU.CUS_ID_NO
AND AU.AD_ID_NO = PA.AD_ID_NO
AND (PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
OR NM.CUS_NM_CHG_DT >= CURRENT DATE - 7 DAY )

WITH UR;

Indexes are also defined for the tables.

tonkuma · #5 (**permalink**) 05-24-11, 03:51

What indexs did you defined?

I thought the following indexes might be worth to try.

DB2NAD8.NADXTNM NM
(CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)

DB2NAD8.NADXTPA PA
(CUS_PHN_AD_CHG_DT DESC , AD_ID_NO)

DB2NAD8.NADXTAU AU
(AD_ID_NO , AU.CUS_ID_NO)

shravani3752 · #6 (**permalink**) 05-24-11, 04:05

Quote:

Originally Posted by tonkuma

What indexs did you defined?

I thought the following indexes might be worth to try.

DB2NAD8.NADXTNM NM
(CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)

DB2NAD8.NADXTPA PA
(CUS_PHN_AD_CHG_DT DESC , AD_ID_NO)

DB2NAD8.NADXTAU AU
(AD_ID_NO , AU.CUS_ID_NO)

Thanks alot. I l try with this index and let you know.

tonkuma · #7 (**permalink**) 05-24-11, 04:40

I'm not sure that DB2 on mainframe implies addtional predicates.
I mean the following additional predicate.

If not, it may be worth.

Code:

 FROM  DB2NAD8.NADXTNM NM
     , DB2NAD8.NADXTPA PA
     , DB2NAD8.NADXTAU AU
 WHERE AU.AD_ID_NO  = NM.AD_ID_NO
   AND AU.CUS_ID_NO = NM.CUS_ID_NO
   AND PA.AD_ID_NO  = NM.AD_ID_NO  -- Add predicate.
   AND AU.AD_ID_NO  = PA.AD_ID_NO
   AND
   (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
    OR NM.CUS_NM_CHG_DT     >= CURRENT DATE - 7 DAY
   )

tonkuma · #8 (**permalink**) 05-24-11, 07:00

Why did you separated columns of constants values, like ...
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
or
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))

Why not like...
,CAST(',"' AS CHAR(2))
or
,CAST('","' AS CHAR(3))

tonkuma · #9 (**permalink**) 05-24-11, 08:15

If (AD_ID_NO , CUS_ID_NO) on DB2NAD8.NADXTNM was unique,
following rewritings might be worth to try.
(Because, no column of DB2NAD8.NADXTNM was in SELECT list.)

Example 1:

Code:

 FROM
       DB2NAD8.NADXTPA PA
     , DB2NAD8.NADXTAU AU
 WHERE AU.AD_ID_NO  = PA.AD_ID_NO
   AND EXISTS
       (SELECT 0
         FROM  DB2NAD8.NADXTNM NM
         WHERE NM.AD_ID_NO  = AU.AD_ID_NO
           AND NM.CUS_ID_NO = AU.CUS_ID_NO
           AND
           (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
            OR NM.CUS_NM_CHG_DT     >= CURRENT DATE - 7 DAY
           )
       )

or

Example 2:

Code:

/*
 The result of the proposed query
   may be slightly different from original query.

 The row with conditions:
        AU.AD_ID_NO          =  PA.AD_ID_NO
    AND PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
    AND NOT EXISTS
        (    NM.AD_ID_NO  = AU.AD_ID_NO
         AND NM.CUS_ID_NO = AU.CUS_ID_NO
         -- Don't care value of NM.CUS_NM_CHG_DT --
        )
 Original query: Not included in result.
 Proposed query: Included in result.
*/
 FROM
       DB2NAD8.NADXTPA PA
     , DB2NAD8.NADXTAU AU
 WHERE AU.AD_ID_NO  = PA.AD_ID_NO
   AND
   (   PA.CUS_PHN_AD_CHG_DT >= CURRENT DATE - 7 DAY
    OR EXISTS
       (SELECT 0
         FROM  DB2NAD8.NADXTNM NM
         WHERE NM.AD_ID_NO      =  AU.AD_ID_NO
           AND NM.CUS_ID_NO     =  AU.CUS_ID_NO
           AND NM.CUS_NM_CHG_DT >= CURRENT DATE - 7 DAY
       )
   )

with the index
(same as before)
DB2NAD8.NADXTNM NM
(CUS_NM_CHG_DT DESC , AD_ID_NO , CUS_ID_NO)
or
DB2NAD8.NADXTNM NM
(AD_ID_NO , CUS_ID_NO , CUS_NM_CHG_DT DESC)

shravani3752 · #10 (**permalink**) 06-03-11, 02:35

Thanks alot for all ur replies Tonkuma. Now it is running for 40 minutes around. Thanks for ur guidence.

shravani3752 · #11 (**permalink**) 06-03-11, 02:39

I am sending the file in CSV format. So there is address field which may contain comma in between that is why i am enclosing my fields in quotes. that is the reason for writing the query in this way.
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))
,PA.CUS_AD_LN_ONE_AD
,CAST('"' AS CHAR(1))
,CAST(',' AS CHAR(1))
,CAST('"' AS CHAR(1))

Internet Services

Web Development

Digital Marketing

Consumer Tech