Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2013
    Posts
    23

    Unanswered: Qyery tuning help appreciated

    Hi Dba Gods,

    Pls help me in tuning the following query which seems to be running slowly:

    SELECT COUNT (*) FROM ORDER_FIRST.W_KINGS, ORDER_FIRST.W_QUEEN, ORDER_FIRST.W_JACKS, ORDER_FIRST.W_ROOKS WHERE (((W.ROOK_ID IN ( select w.rooks_id=b.rooks_id left join ORDER_FIRST.w_masti on b.jasti_id=s.kusti_id join ORDER_FIRST.w.chasti on a.chasti_id=b.chasti_id WHERE ( a.chasti_alias = PAM_ID and w_masti_AVG<259))))) AND(W_QUEEN_ID=M_QUEEN_ID AND ORDER_FIRST.S.JACKS_ID=T.JACKS_ID AND G_ KINGS_ID=M_ KINGS_ID AND M_ KINGS_IN_PA IS NULL AND W_ROOKS VALIDITY=0)

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    can't help you with the info provided. There is no info about table size/indexes that exist/what the current access path is/syntax errors in the provided SQL/etc.

    I can tell you that an exists will normally perform better than an IN. Other than that can't off any other advice

    Dave

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Here is the query with all those useless parenthesis remove and some (assumed typos) corrected.
    Code:
    SELECT COUNT (*) 
    FROM ORDER_FIRST.W_KINGS
       , ORDER_FIRST.W_QUEEN
       , ORDER_FIRST.W_JACKS
       , ORDER_FIRST.W_ROOKS 
    WHERE W.ROOK_ID IN
               ( select w.rooks_id = b.rooks_id 
                   left join 
                   ORDER_FIRST.w_masti 
                     on b.jasti_id = s.kusti_id 
                     join 
                   ORDER_FIRST.w.chasti 
                     on a.chasti_id = b.chasti_id 
                 WHERE a.chasti_alias = PAM_ID 
                   and   w_masti_AVG    < 259 
               )
      AND             W_QUEEN_ID       = M_QUEEN_ID 
      AND ORDER_FIRST.S_JACKS_ID       = T.JACKS_ID
      AND             G_KINGS_ID       = M_KINGS_ID 
      AND             M_KINGS_IN_PA   IS NULL 
      AND             W_ROOKS_VALIDITY = 0
    However, I don't see how this could have run before (with the typos) and I really don't see how this could run now.

    The subquery of the IN has SELECT column=column without table being Left joined and there are references that don't exist (t., a., b., s.) unless some of the _ should be . in a three part name (maybe).

  4. #4
    Join Date
    Nov 2013
    Posts
    23

    Thanks

    Hi thanks a bunch!!! can you please help me rewrite the query with Exists...

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sure show us a valid query

  6. #6
    Join Date
    Nov 2013
    Posts
    23

    Help needed...

    1. SELECT COUNT (*) FROM ORDER_FIRST.W_KINGS, ORDER_FIRST.W_QUEEN, ORDER_FIRST.W_JACKS, ORDER_FIRST.W_ROOKS WHERE (((W.ROOK_ID IN ( select w.rooks_id=b.rooks_id left join ORDER_FIRST.w_masti on b.jasti_id=s.kusti_id join ORDER_FIRST.w.chasti on a.chasti_id=b.chasti_id WHERE ( a.chasti_alias = PAM_ID and w_masti_AVG<29))))) AND(W_QUEEN_ID=M_QUEEN_ID AND ORDER_FIRST.S.JACKS_ID=T.JACKS_ID AND G_ KINGS_ID=M_ KINGS_ID AND M_ KINGS_IN_PA IS NULL AND W_ROOKS VALIDITY=0) with UR

    How can we rewrite this query to make it run faster...

    2. When I use db2batch -d db_name -f file_name -r result_txt -o p 1 e 2
    iam getting the error "unable to open file_name...pls suggest reasons for the same and also what are other options for BENCHMARKING available for performance.

  7. #7
    Join Date
    Nov 2013
    Posts
    23

    DBA GODS pls help...

    1. SELECT COUNT (*) FROM ORDER_FIRST.W_KINGS, ORDER_FIRST.W_QUEEN, ORDER_FIRST.W_JACKS, ORDER_FIRST.W_ROOKS WHERE (((W.ROOK_ID IN ( select w.rooks_id=b.rooks_id left join ORDER_FIRST.w_masti on b.jasti_id=s.kusti_id join ORDER_FIRST.w.chasti on a.chasti_id=b.chasti_id WHERE ( a.chasti_alias = PAM_ID and w_masti_AVG<29))))) AND(W_QUEEN_ID=M_QUEEN_ID AND ORDER_FIRST.S.JACKS_ID=T.JACKS_ID AND G_ KINGS_ID=M_ KINGS_ID AND M_ KINGS_IN_PA IS NULL AND W_ROOKS VALIDITY=0) with UR

    How can we rewrite this query to make it run faster...

    2. When I use db2batch -d db_name -f file_name -r result_txt -o p 1 e 2
    iam getting the error "unable to open file_name...pls suggest reasons for the same and also what are other options for BENCHMARKING available for performance.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Thayums,
    You still haven't provided any of the info I asked about the first time you posted this. You still give us an SQL statement riddled with errors(as an example, you give b.jasti_id=s.kusti_id, but there aren't any tables with a correlation id, so what is b and what is s???)
    If you can't properly ask a question we cannot properly help you. You are still stuck with my first suggestion change your IN to an EXISTS. We can't help you anymore than that.

    Dave

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Posting in separate thread does not help when you do not provide info...


    Thayums,
    You still haven't provided any of the info I asked about the first time you posted this. You still give us an SQL statement riddled with errors(as an example, you give b.jasti_id=s.kusti_id, but there aren't any tables with a correlation id, so what is b and what is s???)
    If you can't properly ask a question we cannot properly help you. You are still stuck with my first suggestion change your IN to an EXISTS. We can't help you anymore than that.

    Dave

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've reformatted the last snippet of code that you submitted, based on my best guess at what you thought you were writing. Your code snippet isn't complete, or syntactically correct so this is only a guess:
    Code:
    SELECT COUNT (*) 
       FROM ORDER_FIRST.W_KINGS
    ,     ORDER_FIRST.W_QUEEN
    ,     ORDER_FIRST.W_JACKS
    ,     ORDER_FIRST.W_ROOKS
       WHERE (((W.ROOK_ID IN ( select w.rooks_id = b.rooks_id
          left join ORDER_FIRST.w_masti
             on b.jasti_id = s.kusti_id
          join ORDER_FIRST.w.chasti
             on a.chasti_id = b.chasti_id
          WHERE ( a.chasti_alias = PAM_ID and w_masti_AVG<29)
                             )
             )))
             AND (W_QUEEN_ID = M_QUEEN_ID 
             AND ORDER_FIRST.S.JACKS_ID = T.JACKS_ID 
             AND G_ KINGS_ID=M_ KINGS_ID 
             AND M_ KINGS_IN_PA IS NULL 
             AND W_ROOKS VALIDITY=0)
    with UR
    Please re-post at least an executable snippet of the code that you'd like us to help you optimize. Then we can analyze that, and maybe we can help without needing more than that.

    Dave: I'm pretty sure that this is an excerpt from a CTE, but we have no idea how much we might be missing so I don't know of any way to even get started trying to help.

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

Posting Permissions

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