Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: Bottleneck SQL Statement

    Hi Everyone,
    Just joined dbforums family. Thanks for all the sharing.

    Here is the scenario that I encounter for the bottleneck sql statement,

    Code:
      SELECT A.CIF_NO, A.COLL_NO, A.ACCT_NO, A.CR_LINE, A.PROPORTION,
              A.DT_CREATED, A.FILE_RUN_ID, A.ROW_NO, A.DT_FILE, min(
              B.AR_ID)as AR_ID, B.SRC_UNQ_ID_TP AS SRC_STM_ID, min(C.IP_ID)as IP_ID, D.RI_SRC_KEY
      FROM DSSSTG.SG1_COL_DSKLM01 A LEFT OUTER JOIN
         (SELECT SRC_UNQ_ID, IP_ID
         FROM DSSSTG.SG_SRC_X_IP
         WHERE SRC_STM_ID='BTR' )C ON A.ACCT_NO=C.SRC_UNQ_ID LEFT OUTER
              JOIN
         (SELECT SUBSTR(ACCT_NO, LENGTH(ACCT_NO)-2, 1)AS RI_SRC_KEY,
                 ROW_NO
         FROM DSSSTG.SG1_COL_DSKLM01)D ON A.ROW_NO=D.ROW_NO LEFT OUTER
              JOIN
         (SELECT A.ALTEL_AC_NO, D.AR_ID, D.SRC_UNQ_ID_TP, A.ROW_NO
         FROM
            (SELECT (CASE
            WHEN SUBSTR(A.ACCT_NO, LENGTH(A.ACCT_NO)-2, 1)IN ('5' , '6' ,'9' )
            THEN '35001' ||RTRIM(D.OU_CODE)||'0000' ||RTRIM(A.ACCT_NO)
            ELSE '35001' ||RTRIM(D.OU_CODE)||RTRIM(A.ACCT_NO)END)AS
                    ALTEL_AC_NO, A.ROW_NO
            FROM DSSSTG.SG1_COL_DSKLM01 A, DSSBDW.OU D
            WHERE D.BR_NO=LEFT(A.ACCT_NO, 4))A, DSSSTG.SG_SRC_X_AR D
         WHERE A.ALTEL_AC_NO=D.SRC_UNQ_ID)B ON A.ROW_NO=B.ROW_NO
      group by A.CIF_NO, A.COLL_NO, A.ACCT_NO, A.CR_LINE, A.PROPORTION,
              A.DT_CREATED, A.FILE_RUN_ID, A.ROW_NO, A.DT_FILE,
              B.SRC_UNQ_ID_TP, D.RI_SRC_KEY;

    The section that I highlighted in red and bold is the most resource hungry query statement. as you can see it require different key column from different tables to become single key search. I tested to separate this out to use load cursor into a table with the index key column - altel_ac_no but the response still not ideal. the total query run end records are estimated 117mil records and it took 22mins just to load into table.

    Anyone has better idea to resolve this issue ? All suggestions are welcome. thank you!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    From the naming, the column ROW_NO in a table DSSSTG.SG1_COL_DSKLM01 might be unique.
    Is it true?

  3. #3
    Join Date
    Aug 2013
    Posts
    80
    Hi tonkuma,
    I just did a count(distinct row_no) vs count(1) from the table, yes it is unique.

    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If column ROW_NO in a table DSSSTG.SG1_COL_DSKLM01 was unique,
    then how about the following query?
    with indexes
    BR_NO on DSSBDW.OU
    and
    (SRC_STM_ID , SRC_UNQ_ID , IP_ID) on DSSSTG.SG_SRC_X_IP

    Code:
    /*
    If A.ROW_NO was unique
    Then try...
    */
    SELECT A.CIF_NO
         , A.COLL_NO
         , A.ACCT_NO
         , A.CR_LINE
         , A.PROPORTION
         , A.DT_CREATED
         , A.FILE_RUN_ID
         , A.ROW_NO
         , A.DT_FILE
         , B.AR_ID
         , B.SRC_UNQ_ID_TP AS SRC_STM_ID
         , (SELECT min(C.IP_ID)
             FROM  DSSSTG.SG_SRC_X_IP AS c
             WHERE C.SRC_STM_ID = 'BTR'
               AND C.SRC_UNQ_ID = A.ACCT_NO
           ) as IP_ID
         , SUBSTR(ACCT_NO , LENGTH(ACCT_NO) - 2 , 1) AS RI_SRC_KEY
     FROM  DSSSTG.SG1_COL_DSKLM01 A
     LEFT  OUTER JOIN
           LATERAL
           (SELECT min(D2.AR_ID) AS AR_ID
                 , D2.SRC_UNQ_ID_TP
             FROM  DSSBDW.OU          D1
             INNER JOIN
                   DSSSTG.SG_SRC_X_AR D2
              ON   D2.SRC_UNQ_ID
                   = '35001'
                     || RTRIM(D1.OU_CODE)
                     || CASE
                        WHEN SUBSTR(A.ACCT_NO , LENGTH(A.ACCT_NO) - 2 , 1)
                             IN ('5' , '6' , '9')
                        THEN '0000'
                        ELSE ''
                        END
                     || A.ACCT_NO
             WHERE D1.BR_NO = LEFT(A.ACCT_NO , 4)
             GROUP BY
                   D2.SRC_UNQ_ID_TP
           ) B
     ON    0=0
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or,
    these indexes might be better...
    (SRC_STM_ID , SRC_UNQ_ID , IP_ID) on DSSSTG.SG_SRC_X_IP
    and
    (BR_NO , OU_CODE) on DSSBDW.OU
    and
    (SRC_UNQ_ID , SRC_UNQ_ID_TP , AR_ID) on DSSSTG.SG_SRC_X_AR

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    I agree with Mr. tonkuma。
    I think the condition d.br_No = LEFT(a.acct_No,4) will prevent optimizer from using hashjoin, So the nljoin maybe the only way. So index on (br_no,ou_code) will be very helpfull。
    anyway paste the full execution plan here ( db2exfmt output) will be more helpful。
    and i have another rewrritten SQL for you ( it might be easier to understand than tonkuma's sql )。

    Code:
    SELECT  a.cIf_No,
            a.coll_No,
            a.acct_No,
            a.cr_Line,
            a.Proportion,
            a.dt_Created,
            a.File_Run_Id,
            a.Row_No,
            a.dt_File,
            a.ri_src_Key
            a.src_unq_Id_tp,
            MIN(a.ar_Id)  AS ar_Id,
            MIN(b.ip_Id)  AS ip_Id
            FROM (  SELECT  a.cIf_No,
                            a.coll_No,
                            a.acct_No,
                            a.cr_Line,
                            a.Proportion,
                            a.dt_Created,
                            a.File_Run_Id,
                            a.Row_No,
                            a.dt_File,
                            a.ri_src_Key
                            d.src_unq_Id_tp,
                            d.ar_Id
                    FROM   (  SELECT (CASE 
                                      WHEN Substr(a.acct_No,Length(a.acct_No) - 2,1) IN ('5','6','9') 
                                      THEN '35001'
                                           ||Rtrim(d.ou_Code)
                                           ||'0000'
                                           ||Rtrim(a.acct_No)
                                      ELSE '35001'
                                           ||Rtrim(d.ou_Code)
                                           ||Rtrim(a.acct_No)
                                          END) AS altel_ac_No,
                                      a.cIf_No,
                                      a.coll_No,
                                      a.acct_No,
                                      a.cr_Line,
                                      a.Proportion,
                                      a.dt_Created,
                                      a.File_Run_Id,
                                      a.Row_No,
                                      a.dt_File,
                                      Substr(a.acct_No,Length(a.acct_No) - 2,1) AS ri_src_Key
                             FROM   dssstg.sg1_col_dsklm01 a,  dssbdw.ou d
                             WHERE  d.br_No = LEFT(a.acct_No,4) ) a, dssstg.sg_src_x_ar d
                   WHERE  a.altel_ac_No = d.src_unq_Id )  a
                   LEFT OUTER JOIN  dssstg.sg_src_x_ip b
                                ON  a.acct_No = b.src_unq_Id
     group by  a.cIf_No,
            a.coll_No,
            a.acct_No,
            a.cr_Line,
            a.Proportion,
            a.dt_Created,
            a.File_Run_Id,
            a.Row_No,
            a.dt_File,
            a.ri_src_Key
            a.src_unq_Id_tp;

  7. #7
    Join Date
    Aug 2013
    Posts
    80
    OK. Thanks for your time and effort, Tonkuma. I will try the statement in our environment. Any outcome I will post here.

Posting Permissions

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