Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: SQL Tuning - Deduce Big Table Joining More than 1

    Hi Everyone,
    I have a question on how to simplify or fine tune the following statement.

    Code:
    SELECT CF_CUST_NUM AS UNQ_ID, ROW_NO AS ROW_NO 
    FROM DSSSTG.SG1_ALS_DSAMM08) C
             LEFT OUTER JOIN (SELECT SRC_UNQ_ID, IP_ID
                                       FROM DSSSTG.SRC_X_IP    -- 80 Mil
                                       WHERE (SRC_UNQ_ID, SRC_STM_ID, PPN_TM) in
                                                    (SELECT SRC_UNQ_ID, SRC_STM_ID, max(PPN_TM)
                                                    FROM DSSSTG.SRC_X_IP  -- 80Mil Records
                                                    WHERE SRC_STM_ID = 'ABC'
                                                    AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6
                                                    AND LEFT(REF_NO,7) = 'XXXXXX'
                                                    GROUP BY SRC_UNQ_ID, SRC_STM_ID) 
                                       GROUP BY SRC_UNQ_ID, IP_ID)
    Thanks.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    with only this information NOBODY can help you
    you should at least specify platform/level
    ddl - explain - problem description....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2013
    Posts
    80
    Sorry for too brief info given, yes the database is using 9.7.6 running in AIX Server.
    Basically this query involve 2 large similar table (I commented 80 mil records),
    there are few challenges in this sql stmt
    1. how to simplify it to use only 1 table statement (to avoid redundant large table query)
    2. there are scalar function use in the where clause - causing full table scan of query.

    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by ckwan123 View Post
    Hi Everyone,
    I have a question on how to simplify or fine tune the following statement.

    Code:
    SELECT CF_CUST_NUM AS UNQ_ID, ROW_NO AS ROW_NO 
    FROM DSSSTG.SG1_ALS_DSAMM08) C
             LEFT OUTER JOIN (SELECT SRC_UNQ_ID, IP_ID
                                       FROM DSSSTG.SRC_X_IP    -- 80 Mil
                                       WHERE (SRC_UNQ_ID, SRC_STM_ID, PPN_TM) in
                                                    (SELECT SRC_UNQ_ID, SRC_STM_ID, max(PPN_TM)
                                                    FROM DSSSTG.SRC_X_IP  -- 80Mil Records
                                                    WHERE SRC_STM_ID = 'ABC'
                                                    AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6
                                                    AND LEFT(REF_NO,7) = 'XXXXXX'
                                                    GROUP BY SRC_UNQ_ID, SRC_STM_ID) 
                                       GROUP BY SRC_UNQ_ID, IP_ID)
    I think that the code must have syntax errors, like
    (1) Un-paired right parenthesis.
    FROM DSSSTG.SG1_ALS_DSAMM08) C

    (2) No ON clause for LEFT OUTER JOIN.
    Last edited by tonkuma; 09-12-13 at 08:16.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, depending on number of rows that match on an IN clause, most times you are better off using an EXISTS.

    Dave

  6. #6
    Join Date
    Aug 2013
    Posts
    80
    Here is the correct code.
    SELECT SRC_UNQ_ID, IP_ID
    FROM DSSSTG.SRC_X_IP WHERE (SRC_UNQ_ID, SRC_STM_ID, PPN_TM) in
    (SELECT SRC_UNQ_ID, SRC_STM_ID, max(PPN_TM)
    FROM DSSSTG.SRC_X_IP
    WHERE SRC_STM_ID = 'ABC'
    AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6
    AND LEFT(REF_NO,7) = 'XXXXX'
    GROUP BY SRC_UNQ_ID, SRC_STM_ID)
    GROUP BY SRC_UNQ_ID, IP_ID
    I tested to segregate inner sql statement to load into temp table to make the select statement simpler, but the response time is still not ideal.

    Thanks.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please post all sql statement.

    It is not enough to see segregated inner sql statement(though you might got some useful information from it).

  8. #8
    Join Date
    Aug 2013
    Posts
    80
    Hi Tonkuma,
    Ok Here the full SQL statement requested.

    SELECT CF_CUST_NUM,
    RT_ML_NUM,
    RT_ML_STATUS,
    RT_PURPOSE_CD,
    RT_LEND_IND,
    RT_PROD_CD,
    RT_LIMIT_AMT,
    RT_AVAIL_AMT,
    RT_DRAWN_AMT,
    RT_UNDRAWN_AMT,
    RT_CP_MET _DATE,
    RT_FRST_DISB_DATE,
    RT_EXPIRY_DATE,
    RT_TENOR,
    AC_TYPE,
    AC_RATE_SIGN,
    AC_RATE,
    RT_INT_MODE,
    RT_PMT_FREQ,
    FILE_RUN_ID,
    A.ROW_NO,
    DT_FILE,
    IP_ID
    FROM DSSSTG. ALS_DSAMM08 A,
    (SELECT CF_CUST_NUM AS UNQ_ID, ROW_NO AS ROW_NO FROM DSSSTG.ALS_DSAMM08) C
    LEFT OUTER JOIN (SELECT SRC_UNQ_ID, IP_ID FROM DSSSTG.SRC_X_IP WHERE (SRC_UNQ_ID, SRC_STM_ID, PPN_TM) in (SELECT SRC_UNQ_ID, SRC_STM_ID,max(PPN_TM) FROM DSSSTG.SG_SRC_X_IP WHERE SRC_STM_ID = 'AAA' AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6 AND LEFT(REF_NO,7) = 'XXXXX' GROUP BY SRC_UNQ_ID, SRC_STM_ID) GROUP BY SRC_UNQ_ID, IP_ID) B ON C.UNQ_ID=B.SRC_UNQ_ID
    WHERE A.ROW_NO=C.ROW_NO

    Thnaks

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ckwan123 View Post
    Hi Tonkuma,
    Ok Here the full SQL statement requested.



    Thnaks
    Not sure why your code does not show up in my quote. Anyhow, I would first try to unnest as much as possible. It will atleast be easier to read and understand what the query does. I probably missed something here, but you could try something like:

    Code:
    ...
    FROM DSSSTG.ALS_DSAMM08 A
    JOIN DSSSTG.ALS_DSAMM08 C
        ON A.ROW_NO=C.ROW_NO
    LEFT OUTER JOIN (
        SELECT SRC_UNQ_ID, IP_ID
        FROM (
            SELECT SRC_UNQ_ID, IP_ID, row_number() over (partition by SRC_UNQ_ID, IP_ID
                                                         order by PPN_TM desc) rn
    
            FROM DSSSTG.SRC_X_IP
            WHERE SRC_STM_ID = 'AAA'
              AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6
              AND LEFT(REF_NO,7) = 'XXXXX'
        ) WHERE rn = 1
    ) B
        ON C.UNQ_ID=B.SRC_UNQ_ID
    --
    Lennart

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another point I doubted was that JOIN A and C was neccesary?
    Both were aliases for same table DSSSTG.ALS_DSAMM08.

    If ROW_NO was unique,
    the JOIN must be unnecessary.

    If ROW_NO was not unique,
    A JOIN C ON A.ROW_NO=C.ROW_NO
    would duplicate rows n*n times for each duplicated ROW_NO value (where n is number of duplicated rows having same value of ROW_NO).

  11. #11
    Join Date
    Aug 2013
    Posts
    80
    I just tested to remove redundant query suggested by tonkuma, but result seems took longer as compare to original with ROW_NO comparison (18 min). The modified SQL become 22min.

    SELECT COUNT(1) FROM (
    SELECT CF_CUST_NUM,
    RT_ML_NUM,
    RT_ML_STATUS,
    RT_PURPOSE_CD,
    RT_LEND_IND,
    RT_PROD_CD,
    RT_LIMIT_AMT,
    RT_AVAIL_AMT,
    RT_DRAWN_AMT,
    RT_UNDRAWN_AMT,
    RT_CP_MET_DATE,
    RT_FRST_DISB_DATE,
    RT_EXPIRY_DATE,
    RT_TENOR,
    AC_TYPE,
    AC_RATE_SIGN,
    AC_RATE,
    RT_INT_MODE,
    RT_PMT_FREQ,
    FILE_RUN_ID,
    A.ROW_NO,
    DT_FILE,
    IP_ID
    FROM DSSSTG.ALS_DSAMM08 A
    LEFT OUTER JOIN (SELECT SRC_UNQ_ID, IP_ID FROM DSSSTG.SG_SRC_X_IP WHERE (SRC_UNQ_ID, SRC_STM_ID, PPN_TM) in (SELECT SRC_UNQ_ID, SRC_S
    TM_ID, max(PPN_TM) FROM DSSSTG.SG_SRC_X_IP WHERE SRC_STM_ID = 'AAA' AND LENGTH(TRIM(SRC_UNQ_ID_TP)) = 6 AND LEFT(REF_NO,7) = 'XXXXXX' GROUP B
    Y SRC_UNQ_ID,
    SRC_STM_ID) GROUP BY SRC_UNQ_ID, IP_ID) B ON A.CF_CUST_NUM=B.SRC_UNQ_ID


  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You compared queries which will return different results, because
    SELECT COUNT(1) FROM (
    was extra process from your original query.

    And, again your code must be incomplete, because of no corresponding right parenthesis for a left parenthesis(i.e. "(").
    Last edited by tonkuma; 09-17-13 at 10:59.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ckwan123,

    two ways of rewriting were proposed now
    (i.e. using ROW_NUMBER by Lennart, removing JOIN A and C by me).

    You may want to test these ideas one by one, and combination of them if each idea was effective.


    Another idea of me is use of correlation subquery, like...

    Example 1:
    Code:
    SELECT CF_CUST_NUM
         , RT_ML_NUM
         , RT_ML_STATUS
         , RT_PURPOSE_CD
         , RT_LEND_IND
         , RT_PROD_CD
         , RT_LIMIT_AMT
         , RT_AVAIL_AMT
         , RT_DRAWN_AMT
         , RT_UNDRAWN_AMT
         , RT_CP_MET _DATE
         , RT_FRST_DISB_DATE
         , RT_EXPIRY_DATE
         , RT_TENOR
         , AC_TYPE
         , AC_RATE_SIGN
         , AC_RATE
         , RT_INT_MODE
         , RT_PMT_FREQ
         , FILE_RUN_ID
         , A.ROW_NO
         , DT_FILE
         , IP_ID
     FROM  DSSSTG. ALS_DSAMM08 A
     LEFT  OUTER JOIN
           LATERAL
           (SELECT DISTINCT
                   IP_ID
             FROM  DSSSTG.SRC_X_IP AS x1
             WHERE SRC_UNQ_ID = A.CF_CUST_NUM
               AND PPN_TM
                   = (SELECT max(PPN_TM)
                        FROM  DSSSTG.SG_SRC_X_IP AS x2
                        WHERE x2.SRC_UNQ_ID = x1.SRC_UNQ_ID
                       /* AND x2.SRC_STM_ID = x1.SRC_STM_ID */
                          AND x2.SRC_STM_ID = 'AAA'
                          AND LENGTH( TRIM(SRC_UNQ_ID_TP) ) = 6
                          AND LEFT(REF_NO , 7) = 'XXXXX'
                      )
               AND x1.SRC_STM_ID = 'AAA'
           ) B
      ON   0=0
    Example 2:
    Code:
    SELECT CF_CUST_NUM
         , RT_ML_NUM
         , RT_ML_STATUS
         , RT_PURPOSE_CD
         , RT_LEND_IND
         , RT_PROD_CD
         , RT_LIMIT_AMT
         , RT_AVAIL_AMT
         , RT_DRAWN_AMT
         , RT_UNDRAWN_AMT
         , RT_CP_MET _DATE
         , RT_FRST_DISB_DATE
         , RT_EXPIRY_DATE
         , RT_TENOR
         , AC_TYPE
         , AC_RATE_SIGN
         , AC_RATE
         , RT_INT_MODE
         , RT_PMT_FREQ
         , FILE_RUN_ID
         , A.ROW_NO
         , DT_FILE
         , (SELECT IP_ID
             FROM  LATERAL
                   (SELECT IP_ID
                         , ROW_NUMBER()
                              OVER( ORDER BY PPN_TM DESC ) AS r_num
                     FROM  DSSSTG.SRC_X_IP
                     WHERE SRC_UNQ_ID = A.CF_CUST_NUM
                       AND SRC_STM_ID = 'AAA'
                       AND LENGTH( TRIM(SRC_UNQ_ID_TP) ) = 6
                       AND LEFT(REF_NO , 7) = 'XXXXX'
                   )
             WHERE r_num = 1
           ) AS IP_ID
     FROM  DSSSTG. ALS_DSAMM08 A
    Last edited by tonkuma; 09-18-13 at 19:00. Reason: Remove DISTINCT from scalar-subquery in Example 2.

  14. #14
    Join Date
    Aug 2013
    Posts
    80
    Thanks Tonkuma on endlessly support me on this particular problem.These two statements are indeed improve a lot but the result I still pending to verify. About this "Lateral" it seems only use when there is a correlation table reference happen. Any idea this Lateral been around since which version ? Thanks again Tonkuma!
    Last edited by ckwan123; 09-19-13 at 01:41.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In earlier DB2 version, TABLE was used in place of LATERAL.

    The use of LATERAL(or TABLE) keyword are documented
    in Correlated references in table-references
    in subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

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