Results 1 to 13 of 13
  1. #1
    Join Date
    May 2010
    Posts
    6

    Unanswered: Query Executing Slow

    Hi. Hope everyone doing great.

    When i run SELECT 'X' FROM PS_INSTALLATION this it's taking no time, and when i run the inner query it's taking 3 sec. But

    when i run the compete query it's taking 2 mins. This is system generated query i can't change the query. Execution plans for the query and inner query are different.

    CODE:

    SELECT 'X' FROM PS_INSTALLATION WHERE EXISTS
    (SELECT 'X' FROM PS_KK_SOURCE_LN A, PS_KK_SOURCE_HDR B , PS_KK_ACTIVITY_LOG C
    WHERE B.BUSINESS_UNIT = 'BUS' AND B.PO_ID = 'PO_NUM' AND A.LINE_NBR = '1' AND A.SCHED_NBR = '1'
    AND A.DISTRIB_LINE_NUM = '1' AND A.KK_TRAN_ID = B.KK_TRAN_ID AND A.KK_TRAN_DT = B.KK_TRAN_DT
    AND A.KK_TRAN_ID = C.KK_TRAN_ID AND A.KK_TRAN_DT = C.KK_TRAN_DT AND A.KK_TRAN_LN = C.KK_TRAN_LN
    AND C.ENTRY_EVENT <> ' ' AND C.EE_PROC_STATUS IN ('N', ' '))\

    Any help would be greatly appreciated!

    Thanks,
    Best Regards,
    Kumar.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then the problem is in the system generating the query. That is a lazy, inefficient piece of code.

    Code like a pro. Relate your tables in the JOIN clause, not the WHERE clause. Drop the obfuscatory and unnecessary table aliases. Format and indent your code. And for efficiency sake, avoid using subqueries with EXISTS.

    Code:
    SELECT	PS_INSTALLATION.'X'
    FROM	PS_INSTALLATION
    	INNER JOIN PS_KK_SOURCE_LN on PS_INSTALLATION.'X' = PS_KK_SOURCE_LN.'X'
    	INNER JOIN PS_KK_SOURCE_HDR
    		ON PS_KK_SOURCE_LN.KK_TRAN_ID = PS_KK_SOURCE_HDR.KK_TRAN_ID
    		AND PS_KK_SOURCE_LN.KK_TRAN_DT = PS_KK_SOURCE_HDR.KK_TRAN_DT
    	INNER JOIN PS_KK_ACTIVITY_LOG
    		ON PS_KK_SOURCE_LN.KK_TRAN_ID = PS_KK_ACTIVITY_LOG.KK_TRAN_ID
    		AND PS_KK_SOURCE_LN.KK_TRAN_DT = PS_KK_ACTIVITY_LOG.KK_TRAN_DT
    		AND PS_KK_SOURCE_LN.KK_TRAN_LN = PS_KK_ACTIVITY_LOG.KK_TRAN_LN
    WHERE	PS_KK_SOURCE_HDR.BUSINESS_UNIT = 'BUS'
    	AND PS_KK_SOURCE_HDR.PO_ID = 'PO_NUM'
    	AND PS_KK_SOURCE_LN.LINE_NBR = '1'
    	AND PS_KK_SOURCE_LN.SCHED_NBR = '1'
    	AND PS_KK_SOURCE_LN.DISTRIB_LINE_NUM = '1'
    	AND PS_KK_ACTIVITY_LOG.ENTRY_EVENT <> ' '
    	AND PS_KK_ACTIVITY_LOG.EE_PROC_STATUS IN ('N', ' '))
    ...and don't even get me started on your table naming convention. Ugh.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    WTF is PS_INSTALLATION.'X'? This won't even compile will it?

    I think by removing the EXISTS (which is a perfectly wonderful clause adored by pros like yours truly) you've changed the (il)logical meaning of the query.

  4. #4
    Join Date
    May 2010
    Posts
    6
    Thanks for the replies...

    This is built-in SQL generated by software which cannot be changed.

    @blindman: I can't do anything with the system, I have to use it as it is. I was checking whether i can do any changes by which query will be faster(as the inner query and outer query separately are efficient).

    PS_INSTALLATION IS TABLE WITH JUST ONE ROW. I can use TOP 1 in the inner query itself removing the outer query altogether, but i can't change it.

    I was just checking with the pro's to know something I don't know.

    any suggestion would be helpful

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The only suggestion I have for you is to complain to the vendor of this product and have them fix it immediately or risk losing business.
    Dave

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There was a great session at OracleWorld about this very subject when running on Microsoft SQL. The problem is well known (if not infamous). There is an SQR that you can schedule that will minimize the performance issues, but beware that it will consume every resource your database server has the first time it runs, and it is quite possible that it may fail more than once before it is completely successful.

    As a side note, if you have MS-PSS (Microsoft Premier Support Services) your CSM can ask your TAM to allocate support hours for a SQL Server Health Check which will produce near optimum (sub-second) response times for this kind of query.

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

  7. #7
    Join Date
    May 2010
    Posts
    6
    Hi Pat Phelan

    Thanks for the reply

    Could you send me links or information regarding the solution.

    Thanks,
    Best Regards,
    Kumar.
    Last edited by akumar.ana06; 06-03-10 at 15:27.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are literally thousands of links that are appropriate to your question. Since you've asked the question in a Microsoft SQL forum, this link is probably the most appropriate place to start. You seem to be looking for a quick/simple solution for a problem with two large and complex software packages... Someone (probably you) is going to need to spend some time (several hours) working through just what your problem is in order to find the solution, and that solution might not be simple. You could be looking at more than a day's worth of work before you resolve the problem.

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

  9. #9
    Join Date
    May 2010
    Posts
    6
    Hi.

    I am asking links or info to get started.

    -Kumar.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump View Post
    I think by removing the EXISTS (which is a perfectly wonderful clause adored by pros like yours truly) you've changed the (il)logical meaning of the query.
    I think not. That EXISTS looks like a logical inner join to me, though depending upon the cardinality of the data it might be necessary to throw a DISTINCT at the result set as well.

    Kumar, have you ensured that all the tables are properly indexed? This means an index for each set of fields included in either side of the joins.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by akumar.ana06 View Post
    I am asking links or info to get started.
    The link that I provided ought to do just fine then.

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

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman View Post
    depending upon the cardinality of the data it might be necessary to throw a DISTINCT at the result set as well.
    Exactly .

  13. #13
    Join Date
    May 2010
    Posts
    6
    Hi

    Thanks Pat Phelan and Pootle Flump

Posting Permissions

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