Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: Query Optimization

    Hi

    I have a query which take around 4 hours to fetch results in Production environment..
    I want to know is there any other way of writing this query for better performance.

    select to_char(T5.ORGID) as ORID,T2.NAME,SUM(T4.QUANTITY) ORDERQUANTITY
    ,SUM(ABS(T4.QUANTITY - CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY IS NULL THEN T3.INVOICEDQTY ELSE T3.PACKMANIFESTQTY END))AS Formula
    FROM ORDERFACT AS TROOT
    INNER JOIN FOLDER AS T1
    ON TROOT.ORDERID = T1.FOLDERID AND T1.STATUS != 'Deleted'
    INNER JOIN PARTYDIM AS T2
    INNER JOIN ORGANIZATION AS T5
    ON T2.TRADECARDORGID = T5.ORGID
    ON TROOT.SELLERPARTYID=T2.PARTYID
    INNER JOIN ORDERITEMFACT AS T3
    INNER JOIN LINEITEMFLATVIEW AS T4
    ON T3.lineItemKey = T4.lineItemKey
    ON TROOT.ORDERID = T3.ORDERID
    where T5.CREATETIMESTAMP between '2013-04-01' and '2013-05-31'
    group by T5.ORGID,T2.NAME,T4.QUANTITY

    Im using DB2 10.5 Fixpack 2

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have indexes built on:
    • ORGANIZATION.CREATETIMESTAMP
    • PARTYDIM.PARTYID
    • ORDERITEMFACT.ORDERID
    • ORGANIZATION.TRADECARDORGID
    • FOLDER.FOLDERID
    • LINEITEMFLATVIEW.lineItemKey
    I've reformatted your SQL, but based on what I expect from the optimizer I think that it should read like:
    Code:
    SELECT to_char(T5.ORGID) as ORID
    ,  T2.NAME
    ,  SUM(T4.QUANTITY) ORDERQUANTITY
    ,  SUM(ABS(T4.QUANTITY
    -     CASE
             WHEN  T3.PACKMANIFESTQTY = 0
                OR T3.PACKMANIFESTQTY IS NULL THEN T3.INVOICEDQTY 
             ELSE  T3.PACKMANIFESTQTY
          END))AS Formula
       FROM ORDERFACT AS TROOT
       INNER JOIN FOLDER AS T1
          ON  T1.FOLDERID = TROOT.ORDERID 
    	  AND T1.STATUS != 'Deleted'
       INNER JOIN PARTYDIM AS T2
          ON T2.PARTYID = TROOT.SELLERPARTYID
       INNER JOIN ORGANIZATION AS T5
          ON  T5.ORGID = T2.TRADECARDORGID
       INNER JOIN ORDERITEMFACT AS T3
          ON  T3.ORDERID = TROOT.ORDERID
       INNER JOIN LINEITEMFLATVIEW AS T4
          ON  T4.lineItemKey = T3.lineItemKey
       WHERE T5.CREATETIMESTAMP BETWEEN '2013-04-01' AND '2013-05-31'
       GROUP BY T5.ORGID,T2.NAME,T4.QUANTITY
    If my assumptions are correct, the indices that I suggested above should give the best performance for this query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to take explain.
    It (and base table's DDLs including indexes, runstats for the tables) must be a first step to analyze performance isuue.

    DB2 version/release/fixpack and base platform OS might be (sometimes) essential.

  4. #4
    Join Date
    Sep 2011
    Posts
    85
    Hay

    Thak you Pat Phelan and tonkuma for your reply

    Pat Phelan - We have the indexes you have mentioned with your comment. So is this the best we can do to improve the performance of this query

    tonkuma - Im using DB2 v10.5.0.2 and my OS is Linux 2.6.32-279.el6.x86_64 x86_64

    I will try to post the explain here

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    [assume folderid is PK on folder]
    Maybe include 'status' as include column in your unique index definition you've defined on 'folder' just before the "alter table folder .. primary key"
    [/assume folderid is PK on folder]

    This query would certainly benefit because there no more references to 'T1.folder ' columns so index-only access is sufficient.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    db2advis -s "statement"
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, is the grouping correct??
    GROUP BY T5.ORGID,T2.NAME,T4.QUANTITY
    Aren't you performing a sum on the quantity?
    In your CASE, maybe you could help performance with the following, put the most frequently occurring case as your first WHEN statement:
    Code:
    CASE
             WHEN  T3.PACKMANIFESTQTY IS NULL THEN T3.INVOICEDQTY 
             WHEN  T3.PACKMANIFESTQTY = 0 THEN T3.INVOICEDQTY 
                  ELSE  T3.PACKMANIFESTQTY
    Dave

  8. #8
    Join Date
    Sep 2011
    Posts
    85
    I'm attaching the access plan

    One nested loop join cost is much higher
    If i can change that query will perform more faster
    Any ideas guys..??

    Here is the query. I remove one group by thanx to dav1mo

    select to_char(T5.ORGID) as ORID,T2.NAME,SUM(T4.QUANTITY) ORDERQUANTITY
    ,SUM(ABS(T4.QUANTITY - CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY IS NULL THEN T3.INVOICEDQTY ELSE T3.PACKMANIFESTQTY END))AS Formula
    FROM ORDERFACT AS TROOT
    INNER JOIN FOLDER AS T1
    ON TROOT.ORDERID = T1.FOLDERID AND T1.STATUS != 'Deleted'
    INNER JOIN PARTYDIM AS T2
    INNER JOIN ORGANIZATION AS T5
    ON T2.TRADECARDORGID = T5.ORGID
    ON TROOT.SELLERPARTYID=T2.PARTYID
    INNER JOIN ORDERITEMFACT AS T3
    INNER JOIN LINEITEMFLATVIEW AS T4
    ON T3.lineItemKey = T4.lineItemKey
    ON TROOT.ORDERID = T3.ORDERID
    where T5.CREATETIMESTAMP between '2013-04-01' and '2013-05-31'
    group by T5.ORGID,T2.NAME
    Attached Thumbnails Attached Thumbnails 1.png  

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Looks like you do not have an index as Pat suggested:

    Do you have indexes built on:
    •ORGANIZATION.CREATETIMESTAMP
    A good index on that table would be:
    ORGANIZATION.CREATETIMESTAMP
    ORGANIZATION.ORGID

    Also, why is lineitemattribute showing up in the explain twice, but is not in the query? Are you perhaps hitting some view?

    Lastly, I can see why you all have written the query as you have, but just for readability/maintenance sake, you might want to have a look at how Pat rewrote it.
    Dave

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    hi,ai_zaviour
    The exection plan you attached here does not provide enough information to identified the problem.We need more infomation about the plan ( eg ,estimated card, filter factor, join predicate, local predicate type, stream order ).According to the plan you posted here, I guess maybe some temporaray tables have been created by optimizer, and db2 have to scan these tables many times to accomplish the nested nljoin.
    So ,plz use the db2exfmt command to generate the plan and post the plan with ddl of these tables which are referenced by the query 。
    and another thing is the size of the db parameter sortheap? Maybe increase the sortheap size will drive optimzer tend to choose hash join or merge join。
    thx。

Posting Permissions

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