Results 1 to 2 of 2

Thread: query tuning

  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: query tuning

    Can someone help me tune this query. I am kind of poor at query tuning. I can send db2exfmt output of it if required.

    This query is taking lot of time to comeback. If there are any basic changes we can do to make this query run faster, that would be great.


    -- Here is the SQL:

    WITH MF_TRADE AS (SELECT
    A.DSSSCA_NO, A.AT_ML_SC_NO,
    C.FC_OFC_NO, C.FC_NO, A.DSS_TD_PRS_DT, B.SC_SY, B.ML_SC_DSC1_TX,
    C.DSSAT_CD, C.TMA_PR_TY_CD, C.AC_GES_TY_CD, MIN(A.TD_CLI_ENT_CD) TD_CLI_ENT_CD, MIN(E.FC_SOL_TD_IN) FC_SOL_TD_IN
    FROM MIDS.TBTRD A ,MIDS.TBFIN_PROD B, MIDS.TBACCT_WE C
    ,MIDS.TBOFC D, MIDS.TBTRD_EXT E
    WHERE A.AT_ML_SC_NO = B.ML_SC_NO
    AND A.TD_STA_CD IN ('1', '5') AND B.AST_L2_PR_CD IN ('10', '12')
    AND A.DSSSCA_NO = C.DSSSCA_NO
    AND C.AC_BU_CHNL_CD <> 'M' AND C.DSSAT_CD NOT IN ('24', '25', '26') AND E.TD_UT_PX_AM <> 1.0000
    AND C.FC_OFC_NO = D.OFC_NO
    AND D.SLS_RGN_CD <> '093044' AND D.OFC_NO <> '899' AND D.EVP_CD='PCLORG'
    AND A.DSS_TD_PRS_DT = E.DSS_TD_PRS_DT
    AND A.TD_PRS_DT = E.TD_PRS_DT
    AND A.TD_RF_NO = E.TD_RF_NO
    AND A.TD_STL_DT = E.TD_STL_DT
    AND A.TD_DT = E.TD_DT
    AND A.DSSSCA_NO = E.DSSSCA_NO
    AND A.TD_STA_CD = E.TD_STA_CD
    GROUP BY
    A.DSSSCA_NO, A.AT_ML_SC_NO,
    C.FC_OFC_NO, C.FC_NO, A.DSS_TD_PRS_DT, B.SC_SY, B.ML_SC_DSC1_TX,
    C.DSSAT_CD,C.TMA_PR_TY_CD,C.AC_GES_TY_CD)

    , ACCT_TYPE AS
    (
    SELECT A.DSSSCA_NO,
    MIN(CASE WHEN C.SV_CD IN ('00185',
    '00102','00004','00026','00027','00028','00029','0 0034','00071','00072','00073','00106','00107','001 08','00200',
    '00184','00066','00067','00068','00069') AND C.AC_SV_END_DT ='9999-12-31'
    THEN 'EXCLUDE'
    WHEN SUBSTR(A.DSSSCA_NO,4,2)='08' AND A.DSSAT_CD IN ('05','07','42','53','54','55','56','57','58','59' ) AND
    G.AC_GES_TY_CD IS NULL THEN 'EXCLUDE'
    WHEN A.DSSAT_CD = '02' AND C.SV_CD = '00014' AND C.AC_SV_END_DT ='9999-12-31' THEN 'ALWAYS_INCLUDE'
    WHEN A.DSSAT_CD = '02' AND A.TMA_PR_TY_CD NOT IN ('11','13','15') THEN 'EXCLUDE'
    ELSE 'INCLUDE'
    END) CATEGORY
    FROM MF_TRADE A
    LEFT OUTER JOIN MIDS.CBRU_GES_CD_LIST G
    ON A.AC_GES_TY_CD = G.AC_GES_TY_CD
    LEFT OUTER JOIN MIDS.TBACCT_SERVICE_WH C
    ON A.DSSSCA_NO = C.DSSSCA_NO
    GROUP BY A.DSSSCA_NO
    )
    , ACCOUNTS AS(
    SELECT A.DSSSCA_NO, A.AT_ML_SC_NO, A.FC_OFC_NO, A.FC_NO, A.DSS_TD_PRS_DT,
    A.SC_SY, A.ML_SC_DSC1_TX
    FROM MF_TRADE A,
    ACCT_TYPE B
    WHERE A.DSSSCA_NO = B.DSSSCA_NO
    AND B.CATEGORY <> 'EXCLUDE'
    )
    , TRADE_SUMMARY AS(SELECT
    A.DSSSCA_NO, A.FC_OFC_NO, A.AT_ML_SC_NO, A.SC_SY, A.ML_SC_DSC1_TX,
    B.TD_BYSL_IN, SUM(CASE WHEN B.TD_STA_CD IN ('1', '5')
    THEN B.TD_PRN_AM * 1
    WHEN B.TD_STA_CD IN ('2', '3')
    THEN B.TD_PRN_AM * -1
    END) SUM_PRN,
    SUM(CASE WHEN B.TD_STA_CD IN ('1', '5')
    THEN B.TD_QY * 1
    WHEN B.TD_STA_CD IN ('2', '3')
    THEN B.TD_QY * -1
    END) SUM_QTY
    FROM ACCOUNTS A,
    MIDS.TBTRD_DH B
    LEFT OUTER JOIN MIDS.TBTRD_MF_EXT_DH C
    ON B.DSS_TD_PRS_DT = C.DSS_TD_PRS_DT
    AND B.TD_PRS_DT = C.TD_PRS_DT
    AND B.TD_RF_NO = C.TD_RF_NO
    AND B.TD_STL_DT = C.TD_STL_DT
    AND B.TD_DT = C.TD_DT
    AND B.DSSSCA_NO = C.DSSSCA_NO
    AND B.TD_BYSL_IN = C.TD_BYSL_IN
    AND B.AT_ML_SC_NO = C.ML_SC_NO
    WHERE A.DSSSCA_NO = B.DSSSCA_NO
    AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
    AND B.DSS_TD_PRS_DT >= A.DSS_TD_PRS_DT - 29 DAYS
    AND B.DSS_TD_PRS_DT <= A.DSS_TD_PRS_DT
    AND B.TD_STA_CD IN ('1', '5', '2', '3')
    AND COALESCE(C.MF_QT_NAV_PX,0) <> 1.00
    GROUP BY
    A.DSSSCA_NO, A.FC_OFC_NO,
    A.AT_ML_SC_NO, A.SC_SY, A.ML_SC_DSC1_TX,
    B.TD_BYSL_IN
    )
    , ALL_ACCOUNTS AS
    (SELECT DSSSCA_NO, AT_ML_SC_NO
    FROM TRADE_SUMMARY
    WHERE
    (TD_BYSL_IN = 'B' AND SUM_PRN >= 50000.00) OR
    (TD_BYSL_IN = 'S' AND SUM_PRN >= 50000.00)
    GROUP BY DSSSCA_NO, AT_ML_SC_NO
    )
    , BUY AS(SELECT T.DSSSCA_NO, T.AT_ML_SC_NO,T.SUM_PRN,T.SUM_QTY
    FROM TRADE_SUMMARY T, ALL_ACCOUNTS A
    WHERE T.TD_BYSL_IN = 'B'
    AND T.DSSSCA_NO = A.DSSSCA_NO
    AND T.AT_ML_SC_NO = A.AT_ML_SC_NO
    )
    , SELL AS(SELECT T.DSSSCA_NO, T.AT_ML_SC_NO,T.SUM_PRN,T.SUM_QTY
    FROM TRADE_SUMMARY T, ALL_ACCOUNTS A
    WHERE T.TD_BYSL_IN = 'S'
    AND T.DSSSCA_NO = A.DSSSCA_NO
    AND T.AT_ML_SC_NO = A.AT_ML_SC_NO
    )
    , FINAL_ACCOUNTS AS
    (
    SELECT
    C.DIVN_SMRY_NM, C.OFC_RGN_NM, C.OFC_PNT_NM,T.FC_OFC_NO, T.FC_NO, B.DSSSCA_NO,
    T.AT_ML_SC_NO, T.SC_SY, T.ML_SC_DSC1_TX,
    B.SUM_PRN PRN_BUY, B.SUM_QTY QTY_BUY,
    S.SUM_PRN PRN_SELL, S.SUM_QTY QTY_SELL,
    T.DSS_TD_PRS_DT
    FROM BUY B, SELL S,MF_TRADE T,MIDS.TBOFC C
    WHERE B.DSSSCA_NO = T.DSSSCA_NO AND B.AT_ML_SC_NO = T.AT_ML_SC_NO
    AND B.DSSSCA_NO = S.DSSSCA_NO AND B.AT_ML_SC_NO = S.AT_ML_SC_NO
    AND T.FC_OFC_NO = C.OFC_NO
    AND S.SUM_PRN >= B.SUM_PRN * .2 AND
    B.SUM_PRN >= S.SUM_PRN * .2
    )
    , LAST_TRADE AS
    (
    SELECT
    A.DSSSCA_NO,
    A.AT_ML_SC_NO,
    B.TD_BYSL_IN,
    MAX(B.TD_DT) LAST_TRADE
    FROM FINAL_ACCOUNTS A,
    MIDS.TBTRD_DH B
    LEFT OUTER JOIN MIDS.TBTRD_MF_EXT_DH C
    ON B.DSS_TD_PRS_DT = C.DSS_TD_PRS_DT
    AND B.TD_PRS_DT = C.TD_PRS_DT
    AND B.TD_RF_NO = C.TD_RF_NO
    AND B.TD_DT = C.TD_DT
    AND B.DSSSCA_NO = C.DSSSCA_NO
    AND B.TD_BYSL_IN = C.TD_BYSL_IN
    AND B.AT_ML_SC_NO = C.ML_SC_NO
    WHERE A.DSSSCA_NO = B.DSSSCA_NO
    AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
    AND B.DSS_TD_PRS_DT >= A.DSS_TD_PRS_DT - 29 DAYS
    AND B.DSS_TD_PRS_DT <= A.DSS_TD_PRS_DT
    AND B.TD_STA_CD IN ('1', '5')
    AND C.MF_QT_NAV_PX <> 1.00
    GROUP BY A.DSSSCA_NO,A.AT_ML_SC_NO,B.TD_BYSL_IN
    )
    SELECT
    A.DSSSCA_NO,A.DSS_TD_PRS_DT,A.SC_SY,A.FC_OFC_NO, A.FC_NO,A.ML_SC_DSC1_TX,A.QTY_BUY,A.PRN_BUY,
    A.QTY_SELL,A.PRN_SELL,B.LAST_TRADE LAST_BUY_DATE,
    C.LAST_TRADE LAST_SELL_DATE,A.OFC_RGN_NM, A.OFC_PNT_NM,
    A.AT_ML_SC_NO
    FROM FINAL_ACCOUNTS A
    LEFT OUTER JOIN LAST_TRADE B
    ON A.DSSSCA_NO = B.DSSSCA_NO
    AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
    AND B.TD_BYSL_IN = 'B'
    LEFT OUTER JOIN LAST_TRADE C
    ON A.DSSSCA_NO = C.DSSSCA_NO
    AND A.AT_ML_SC_NO = C.AT_ML_SC_NO
    AND C.TD_BYSL_IN = 'S'
    ORDER BY A.DSSSCA_NO,A.AT_ML_SC_NO
    ;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look for table scans on large tables in the explain. Work from there.

    Andy

Posting Permissions

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