Results 1 to 12 of 12

Thread: SQL tuning

  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Question Unanswered: SQL tuning

    Hi All,

    This table EDW_SOR_TX N has totally 30 million rows and the below mentioned query runs so very slow at the rate of 300 rows/second.
    Kindly let me know how this can be fine tuned.

    SELECT
    POSN_DT,
    AR_ID,
    OU_ID,
    TXN_CODE,
    CNL_ID,
    sum(abs(NET_CASH_FLOW_AMT)) as TOT_TXN_AMT,
    SRC_STM_ID ,
    'EDW_SOR_TXN' as SRC_TBL,
    count(*) as TOT_NBR_OF_TXN
    FROM
    schema.
    where
    POSN_DT='2006-09-30'EDW_SOR_TXN
    and CNL_ID<>'2'
    group by
    POSN_DT,
    AR_ID,
    OU_ID,
    TXN_CODE,
    CNL_ID,
    SRC_STM_ID

    Thanks in advance.

    Regards,
    Priya

  2. #2
    Join Date
    May 2006
    Posts
    82
    where
    POSN_DT='2006-09-30'EDW_SOR_TXN .....?
    and CNL_ID<>'2'
    Give the details about the indexes built on that table.

  3. #3
    Join Date
    Jan 2007
    Posts
    11
    Only one index exists on this table,that is the primary key index with (TXN_CODE,POSN_DT)

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by shanmugapriya
    ... primary key index with (TXN_CODE,POSN_DT)
    If the combination of TXN_CODE and POSN_DT is unique,
    and the WHERE-clause says ... where POSN_DT='2006-09-30' (one single value ! )
    and TXN_CODE is part of the SELECT-clause,
    then there is no need to do a GROUP BY !


    each value for TXN_CODE can only occur once.
    --> GROUP BY TXN_CODE will group each column in a separate group
    --> COUNT(*) will always be 1
    --> sum(abs(NET_CASH_FLOW_AMT)) will always be abs(NET_CASH_FLOW_AMT)

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Use the DB2 Design Advisor with your above query as workload to get a suggestion in indexes from which this query would benefit.

    Other than that, I don't think that your query is syntactically correct. Things like "FROM schema." are not valid FROM clauses. Likewise, what kind of predicate is "POSN_DT='2006-09-30'EDW_SOR_TXN"??
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by umayer
    If the combination of TXN_CODE and POSN_DT is unique,
    and the WHERE-clause says ... where POSN_DT='2006-09-30' (one single value ! )
    and TXN_CODE is part of the SELECT-clause,
    then there is no need to do a GROUP BY !
    I guess you meant that TXN_CODE and POSN_DT are both in the select-list so that each group consists of a single row only
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2007
    Posts
    11
    Sorry for the typo.

    the query is given below.

    SELECT
    POSN_DT,
    AR_ID,
    OU_ID,
    TXN_CODE,
    CNL_ID,
    sum(abs(NET_CASH_FLOW_AMT)) as TOT_TXN_AMT,
    SRC_STM_ID ,
    'EDW_SOR_TXN' as SRC_TBL,
    count(*) as TOT_NBR_OF_TXN
    FROM
    <schema>.<tablename>
    where
    POSN_DT='2006-09-30'
    and CNL_ID<>'2'
    group by
    POSN_DT,
    AR_ID,
    OU_ID,
    TXN_CODE,
    CNL_ID,
    SRC_STM_ID


    Umayer ,you are absolutely right.But by mistake I had given the primary key cols as (TXN_CODE,POSN_DT) but it is actually (TXN_ID,POSN_DT)

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Either:

    1. Reverse the order of the primary key from (TXN_ID,POSN_DT) to (POSN_DT,TXN_ID). This will change the order of the index (assuming you let DB2 create the PK index for you).

    or

    2. Add a new index on (POSN_DT)

    If TXN_ID has less than 20 unique values in the table, I would choose option 1.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    @ stolze:

    Quote Originally Posted by stolze
    I guess you meant that TXN_CODE and POSN_DT are both in the select-list so that each group consists of a single row only

    POSN_DT is a constant value due to the WHERE-clause. So there is no need for that column to occur in the SELECT-list. TXN_CODE is unique if POSN_DT is constant

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by umayer
    POSN_DT is a constant value due to the WHERE-clause. So there is no need for that column to occur in the SELECT-list. TXN_CODE is unique if POSN_DT is constant
    Yes, you are right.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jan 2007
    Posts
    11
    when TXN_CODE is not a part of the key ,how can it be unique just bcoz posn_dt is constant.
    Please elaborate.

  12. #12
    Join Date
    Dec 2005
    Posts
    273
    my statement was a reply to stolzes posting. That ( stolzes post ) was before you corrected the information regarding the primary key.

    So if the primary key is on column (TXN_CODE,POSN_DT) and POSN_DT is constant, TXN_CODE must be unique

Posting Permissions

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