December 23, 2017 at 9:46 am
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.
Totally Agree.
December 23, 2017 at 9:48 am
Lynn Pettis - Friday, December 22, 2017 5:09 PMJason 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.
Not for money though. Due to strict deadlines, I was seeking help as no one was available. But sure, before putting any code on prod, I would understand the functionality with complete testing done, only then I put the code on prod. otherwise no.
December 23, 2017 at 9:52 am
Lynn Pettis - Thursday, December 21, 2017 1:46 PMSecond, 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.
Hi Lynn,
Just wanted to know , what are the drawbacks of using dynamic sql/sp_executesql inside a stored proc? I am not sure why some developer has done the coding in such a way, but are the problems doing so?
December 23, 2017 at 10:50 am
vsamantha35 - Saturday, December 23, 2017 9:52 AMLynn Pettis - Thursday, December 21, 2017 1:46 PMSecond, 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.
Hi Lynn,
Just wanted to know , what are the drawbacks of using dynamic sql/sp_executesql inside a stored proc? I am not sure why some developer has done the coding in such a way, but are the problems doing so?
Nothing, I do it a lot at my current employer, the problem I have is when the engineers here write dynamic SQL even when it is NOT needed. It is a tool and should be used appropriately. When I first started looking at your code I saw no reason for the dynamic SQL until I finally got down to the ORDER BT clause.
The main issue I now have with your dynamic SQL is it doesn't eliminate the catch-all nature of the query itself. This would be one reason to use dynamic SQL in this case.
December 26, 2017 at 1:10 pm
Lynn Pettis - Friday, December 22, 2017 5:09 PMJason 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.
You can add me to that list. My current contract doing performance tuning ends on 12/29, and I'll be available starting 1/2.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply