December 21, 2017 at 1:24 pm
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
December 21, 2017 at 1:32 pm
First, why are you executing the stored procedure using dynamic SQL?
December 21, 2017 at 1:46 pm
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.
December 21, 2017 at 1:51 pm
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.
December 21, 2017 at 1:59 pm
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 + '%'.
December 21, 2017 at 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
December 21, 2017 at 2:20 pm
vsamantha35 - Thursday, December 21, 2017 2:14 PMHi 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!).
December 21, 2017 at 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 .....
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
December 21, 2017 at 2:27 pm
GilaMonster - Thursday, December 21, 2017 2:24 PMYou 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.
December 21, 2017 at 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...
December 21, 2017 at 3:43 pm
Jason A. Long - Thursday, December 21, 2017 3:25 PMThis 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.
December 21, 2017 at 8:36 pm
Lynn Pettis - Thursday, December 21, 2017 3:43 PMJason A. Long - Thursday, December 21, 2017 3:25 PMThis 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.
December 22, 2017 at 9:19 am
vsamantha35 - Thursday, December 21, 2017 8:36 PMHI 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.
December 22, 2017 at 5:09 pm
Jason A. Long - Friday, December 22, 2017 9:19 AMvsamantha35 - Thursday, December 21, 2017 8:36 PMHI 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.
December 22, 2017 at 6:01 pm
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