SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please delete this post


Please delete this post

Author
Message
Feivel
Feivel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6549 Visits: 2055
Steve please have this post deleted.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)

Group: General Forum Members
Points: 951504 Visits: 48920
You could start by getting rid of the unnecessary cursor.

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


Feivel
Feivel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6549 Visits: 2055
GilaMonster - Tuesday, February 13, 2018 2:25 PM
You could start by getting rid of the unnecessary cursor.

Thank you for your response but could you please explain what all pieces I should get rid of to eliminate the cursor. I apologize for this but I am not that good in programming and unfortunately this has been handed over to me for review.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)

Group: General Forum Members
Points: 951504 Visits: 48920
It loops over one table and inserts the values, one by one, into another table. That's not hard to replace with a single insert.
You get better at programming by doing it, not by someone doing it for you, so give it a try.

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


Feivel
Feivel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6549 Visits: 2055
GilaMonster - Tuesday, February 13, 2018 3:30 PM
It loops over one table and inserts the values, one by one, into another table. That's not hard to replace with a single insert.
You get better at programming by doing it, not by someone doing it for you, so give it a try.

I agree with you and that is my pain point even if I keep doing it a hundred time I won't be able to get rid of it. I am too bad at this and that is the reason I hate programming the most. Honestly, I don't really like to ask such questions and make myself even look more stupid but unfortunately I am not that skilled either. I can definitely give it a shot but I am sure I won't end up landing anywhere.

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172514 Visits: 24489
ffarouqi - Tuesday, February 13, 2018 1:59 PM
Is there a better way or a more efficient way to write the attached stored proc that leans on to following best practice solution for writing a stored proc. For e.g can I add a try/catch block in this existing code or should I instead of declaring table variable should use temp tables etc. Any input would be highly appreciated.

Yes there are better ways of doing this.
Cool
Looking through the code, everything there can be optimized with this one exception

ALTER PROCEDURE [dbo].[prEdPlanbyState]

Feivel
Feivel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6549 Visits: 2055
Eirikur Eiriksson - Wednesday, February 14, 2018 12:18 AM
ffarouqi - Tuesday, February 13, 2018 1:59 PM
Is there a better way or a more efficient way to write the attached stored proc that leans on to following best practice solution for writing a stored proc. For e.g can I add a try/catch block in this existing code or should I instead of declaring table variable should use temp tables etc. Any input would be highly appreciated.

Yes there are better ways of doing this.
Cool
Looking through the code, everything there can be optimized with this one exception

ALTER PROCEDURE [dbo].[prEdPlanbyState]

Wow...really I never thought about it. Okay so actions speak louder than words so could you please optimize everything except for the "Alter Proc".

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172514 Visits: 24489
ffarouqi - Wednesday, February 14, 2018 12:29 AM
Eirikur Eiriksson - Wednesday, February 14, 2018 12:18 AM
ffarouqi - Tuesday, February 13, 2018 1:59 PM
Is there a better way or a more efficient way to write the attached stored proc that leans on to following best practice solution for writing a stored proc. For e.g can I add a try/catch block in this existing code or should I instead of declaring table variable should use temp tables etc. Any input would be highly appreciated.

Yes there are better ways of doing this.
Cool
Looking through the code, everything there can be optimized with this one exception

ALTER PROCEDURE [dbo].[prEdPlanbyState]

Wow...really I never thought about it. Okay so actions speak louder than words so could you please optimize everything except for the "Alter Proc".

Are you asking me to do your work for free?
Cool

GilaMonster
GilaMonster
SSC Guru
SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)SSC Guru (951K reputation)

Group: General Forum Members
Points: 951504 Visits: 48920
ffarouqi - Wednesday, February 14, 2018 12:29 AM

Wow...really I never thought about it. Okay so actions speak louder than words so could you please optimize everything except for the "Alter Proc".

Table variables are slow (and you can probably manage with only one or even none).
Cursors are slow
Your string split is probably slow (can't tell, it wasn't included)
The variable assignments can be done in a single statement.
Can't tell how the final insert can be optimised. The multiple DISTINCTS are probably unnecessary though.

ffarouqi - Tuesday, February 13, 2018 3:37 PM
I can definitely give it a shot but I am sure I won't end up landing anywhere.

So give it a shot. You'll learn a lot more by trying and getting suggestions than by someone doing it for you.

If you want your code (not just this) optimising by someone else, suggest to your boss that you get a consultant in to look at it.


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


Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172514 Visits: 24489
GilaMonster - Wednesday, February 14, 2018 1:35 AM
ffarouqi - Wednesday, February 14, 2018 12:29 AM

Wow...really I never thought about it. Okay so actions speak louder than words so could you please optimize everything except for the "Alter Proc".

Table variables are slow (and you can probably manage with only one or even none).
Cursors are slow
Your string split is probably slow (can't tell, it wasn't included)
The variable assignments can be done in a single statement.
Can't tell how the final insert can be optimised. The multiple DISTINCTS are probably unnecessary though.

ffarouqi - Tuesday, February 13, 2018 3:37 PM
I can definitely give it a shot but I am sure I won't end up landing anywhere.

So give it a shot. You'll learn a lot more by trying and getting suggestions than by someone doing it for you.

If you want your code (not just this) optimising by someone else, suggest to your boss that you get a consultant in to look at it.

One thing would be the table variables, could be replaced by CTEs.
Cool
Wealth of antipatterns in this code; multiple table variables, cursor using table valued function, multi-valued input parameter, distinct selection from a table valued function etc.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search