Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Help me on Query fine tune.Explain report details provided.

    Hello,

    Thankyou for helping me.

    Here we have two quires on same table , but FROM classs differs.
    But both quiries uses the same INDEX.I have provided the index details below.Is there any chances to improve performance.
    Please help me. Thank you.

    STATEMENET :1
    ---------------------------
    SELECT DRMT_DET_PAR_NBR
    ,DRMT_SYS_ID
    ,DRMT_DET_PAR_DTE
    ,DRMT_DR_CR_IND
    ,DRMT_DET_AMT
    ,DRMT_ACH_TRAN_CD
    ,DRMT_DIST_ACCT_NBR
    ,DRMT_ORIG_ACCT_NBR
    INTO :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR, :DCLCH-DET-RTN-MTCH
    .DRMT-SYS-ID, :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE, :
    DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND, :DCLCH-DET-RTN-MTCH.
    DRMT-DET-AMT, :DCLCH-DET-RTN-MTCH.DRMT-ACH-TRAN-CD, :
    DCLCH-DET-RTN-MTCH.DRMT-DIST-ACCT-NBR, :
    DCLCH-DET-RTN-MTCH.DRMT-ORIG-ACCT-NBR
    FROM CH_DET_RTN_MTCH
    WHERE DRMT_DET_PAR_NBR = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR
    AND DRMT_SYS_ID = :DCLCH-DET-RTN-MTCH.DRMT-SYS-ID
    AND DRMT_DET_PAR_DTE = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE
    AND DRMT_DR_CR_IND = :DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND
    AND DRMT_DET_AMT = :DCLCH-DET-RTN-MTCH.DRMT-DET-AMT
    AND DRMT_DIST_SETT_DTE >= :DCLCH-DET-RTN-MTCH.

    EXPLAIN REPORT :
    ----------------------------

    QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
    1 1 0 SELECT 0 I 3 N CH_DET_RTN_MTCH X1CHDRMT

    NU J O G CU J O G LCK
    N N N N N N N N IS


    STATEMENET :2
    ---------------------------


    SELECT DRMT_DET_PAR_NBR
    ,DRMT_SYS_ID
    ,DRMT_DET_PAR_DTE
    ,DRMT_DR_CR_IND
    ,DRMT_ACH_TRAN_CD
    ,DRMT_DIST_ACCT_NBR
    ,DRMT_ORIG_ACCT_NBR
    INTO :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR, :DCLCH-DET-RTN-MTCH
    .DRMT-SYS-ID, :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE, :
    DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND, :DCLCH-DET-RTN-MTCH.
    DRMT-ACH-TRAN-CD, :DCLCH-DET-RTN-MTCH.DRMT-DIST-ACCT-NBR,
    :DCLCH-DET-RTN-MTCH.DRMT-ORIG-ACCT-NBR
    FROM CH_DET_RTN_MTCH
    WHERE DRMT_DET_PAR_NBR = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR
    AND DRMT_SYS_ID = :DCLCH-DET-RTN-MTCH.DRMT-SYS-ID
    AND DRMT_DET_PAR_DTE = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE
    AND DRMT_DR_CR_IND = :DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND
    AND DRMT_DIST_SETT_DTE >= :DCLCH-DET-RTN-MTCH.

    EXPLAIN REPORT :
    ----------------------------
    QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
    1 1 0 SELECT 0 I 3 N CH_DET_RTN_MTCH X1CHDRMT

    NU J O G CU J O G LCK
    N N N N N N N N IS

    Index Key Information for: X1CHDRMT
    -------------------------------------------------------
    COLUMN NAME COLNO COLSEQ ORDERING
    DRMT_SYS_ID 1 1 ASCENDING
    DRMT_DET_PAR_NBR 2 2 ASCENDING
    DRMT_DIST_SETT_DTE 3 3 ASCENDING
    DRMT_DET_AMT 4 4 ASCENDING
    DRMT_DR_CR_IND 5 5 ASCENDING
    DRMT_CBAT_CO_ID 6 6 ASCENDING
    DRMT_DET_PAR_DTE 7 7 ASCENDING
    Last edited by chandra131; 08-10-07 at 23:23.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the FROM clause is the same -- the only difference between the two queries is that the second query retrieves one less field

    and both queries use the same index, yes?

    but since that field is the 4th column in the index, and you're providing a value for it in the first query but not the second, i would expect the first query to run faster than the second

    to make it run even faster, provide a value for DRMT_CBAT_CO_ID as well

    is that what you're after?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    Thanks for your advice.
    Both queires different in WHERE clause but uses the same TABLE and same INDEX.

    Here my question is Can i improve performance by

    1.create another index ?
    2.by changing WHERE clause field sequance, based on index key field sequance ? will it improve performance ?

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by chandra131
    Can i improve performance by
    2. changing WHERE clause field sequence, based on index key field sequence ? will it improve performance ?
    No, definitely not. The order of WHERE predicates does not influence the optimizer's decisions.

    Quote Originally Posted by chandra131
    Can i improve performance by
    1. create another index ?
    Of course.
    E.g., an index on (DRMT_DET_PAR_NBR, DRMT_SYS_ID, DRMT_DET_PAR_DTE, DRMT_DR_CR_IND, DRMT_DIST_SETT_DTE, DRMT_DET_AMT) would help both queries.
    Also an index on (DRMT_DET_PAR_NBR, DRMT_SYS_ID, DRMT_DET_PAR_DTE, DRMT_DR_CR_IND, DRMT_DET_AMT, DRMT_ACH_TRAN_CD, DRMT_DIST_ACCT_NBR, DRMT_ORIG_ACCT_NBR) could help, since its use would make the query use just index-only access.
    Finally, adding column DRMT_DIST_SETT_DTE to that last index would be useful for both purposes, so it would "certainly" be picked by the optimizer.

    On the other hand, I don't expect creating any of these three indexes would be a good idea since "best overall performance" is more complex than just "tune the system for this particular query only".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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