Need some suggestions on query execution plan

  • Hi All,

    Need some advise on the query plan analysis. There is stored proc within which a dynamic sql is formed and executed and it is taking more time when executed with concurrent user load testing. checking if any suggestions/guidelines/index recommendations or best practices around refactoring the code.

    Attaching the query plan and stored proc source code.

    Thanks,

    Sam

  • First, why are you executing the stored procedure using dynamic SQL?

  • Second, why are you using dynamic SQL inside the stored procedure?  I am not seeing ANY reason for dynamic SQL to be used.  I am seeing a lot a poor coding and difficult to read because of the formatting of the code.  SQL Prompt errors trying to format the code.

    A big chunk of this is a catch all query but you aren't using dynamic SQL to eliminate this aspect of the code.

  • This snippet doesn't even make sense:
    AND (NOT EXISTS (SELECT 1 FROM @SampleStatusId) OR samp.SampleCondition IN (SELECT id FROM @SampleStatusId))

    EDIT: Nevermind, it does now.

  • I stand corrected, I got to the end of code and found why it is dynamic SQL, your ORDER BY clause can use a different column on each instantiation of the procedure. 

    Even with this, you could probably put it all in one procedure.
    Still, the query will result in table scans on several tables where you are using LIKE '%' _ some variable + '%'.

  • Hi Lynn,

    Code has been written some 3rd party consultant. Is there a way to convert it into normal sql?

    Thanks ,
    Sam

  • vsamantha35 - Thursday, December 21, 2017 2:14 PM

    Hi Lynn,

    Code has been written some 3rd party consultant. Is there a way to convert it into normal sql?

    Thanks ,
    Sam

    First, due to the dynamic nature of the final code (i.e. the sort column can change), it still must be dynamic.  Second, yes, it could be rewritten but it will take effort to accomplish and not something I could work on until I am off work.  Even then, do I want to expend the effort for free?  Maybe, but it is the last weekend before Christmas and I have a little shopping to complete and that will take priority as will going to the movies this weekend (Payday and Bonus!).

  • You can do a dynamic sort without dynamic SQL. Not going to be fast, but that's the price you pay for such code.

    ORDER BY
    CASE @Ordering WHEN 'Column1' THEN Column1 .....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Thursday, December 21, 2017 2:24 PM

    You can do a dynamic sort without dynamic SQL. Not going to be fast, but that's the price you pay for such code.

    ORDER BY
    CASE @Ordering WHEN 'Column1' THEN Column1 .....

    Ah, yes, but I was definitely not thinking that way.  That could get messy depending on how many different columns might be used.  And then having to update the code when a new column or columns are needed.

  • This looks a lot like the thread you started here... https://www.sqlservercentral.com/Forums/1911062/Help-to-reduce-stored-procedure-execution
    I'm not sure why anyone would want to continue answering questions for you when you bail out on the threads you've already started and ignore the answers you've been given...

  • Jason A. Long - Thursday, December 21, 2017 3:25 PM

    This looks a lot like the thread you started here... https://www.sqlservercentral.com/Forums/1911062/Help-to-reduce-stored-procedure-execution
    I'm not sure why anyone would want to continue answering questions for you when you bail out on the threads you've already started and ignore the answers you've been given...

    Could it be he is looking for a free rewrite after paying way to much to a third party for the crap code he has to support?
    Yes, I just went there based on the new information.

  • Lynn Pettis - Thursday, December 21, 2017 3:43 PM

    Jason A. Long - Thursday, December 21, 2017 3:25 PM

    This looks a lot like the thread you started here... https://www.sqlservercentral.com/Forums/1911062/Help-to-reduce-stored-procedure-execution
    I'm not sure why anyone would want to continue answering questions for you when you bail out on the threads you've already started and ignore the answers you've been given...

    Could it be he is looking for a free rewrite after paying way to much to a third party for the crap code he has to support?
    Yes, I just went there based on the new information.

    HI Jason & Lynn,
    Sorry my sincere apologies. The code getting changed by many every now and then and I have no other option seeking help for cleaning up such crappy code.

  • vsamantha35 - Thursday, December 21, 2017 8:36 PM

    HI Jason & Lynn, 

    Sorry my sincere apologies. The code getting changed by many every now and then and I have no other option seeking help for cleaning up such crappy code.

    The point is that you (or someone else within your company), is going to have to rewrite this code if you want it to perform well.
    The good people of the forum will gladly help you but we can't do the heavy lifting for you. 
    My comment regarding the previous post wasn't intended to have you mark my comment as the answer, but rather to have you examine the methodology so that you can take it and apply it to the remainder of the code yourself. If you have questions about how works or why it can help, everyone here is willing to help fill in the blanks, myself included.
    The key thing to remember is that this isn't a free code writing service, it's a free learning resource. Giving you code that you don't understand may solve you immediate problem but it does nothing to help you the next time you're in a similar situation... Not to mention the fact that you should NEVER take a piece of code from the internet and apply it to your production databases w/o understanding exactly what it is, what its doing and how it works. 
    If neither you nor anyone you work with is inclined to do the necessary rewrites, you may need to consider hiring a consultant... Just make sure they are competent, preferably one who specializes in query performance tuning. There are several very good ones who participate here on this forum.

  • Jason A. Long - Friday, December 22, 2017 9:19 AM

    vsamantha35 - Thursday, December 21, 2017 8:36 PM

    HI Jason & Lynn, 

    Sorry my sincere apologies. The code getting changed by many every now and then and I have no other option seeking help for cleaning up such crappy code.

    The point is that you (or someone else within your company), is going to have to rewrite this code if you want it to perform well.
    The good people of the forum will gladly help you but we can't do the heavy lifting for you. 
    My comment regarding the previous post wasn't intended to have you mark my comment as the answer, but rather to have you examine the methodology so that you can take it and apply it to the remainder of the code yourself. If you have questions about how works or why it can help, everyone here is willing to help fill in the blanks, myself included.
    The key thing to remember is that this isn't a free code writing service, it's a free learning resource. Giving you code that you don't understand may solve you immediate problem but it does nothing to help you the next time you're in a similar situation... Not to mention the fact that you should NEVER take a piece of code from the internet and apply it to your production databases w/o understanding exactly what it is, what its doing and how it works. 
    If neither you nor anyone you work with is inclined to do the necessary rewrites, you may need to consider hiring a consultant... Just make sure they are competent, preferably one who specializes in query performance tuning. There are several very good ones who participate here on this forum.

    And some that may like to earn some extra money as well.

  • I have absolutely no reservations about recommending Lynn. I’ve seen enough of Lynn’s tsql over the years, to know he’s a top shelf coder...

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply