Please delete this post

Question

Please delete this post

Feivel

SSCertifiable

Points: 7524
More actions
February 13, 2018 at 1:59 pm

#403112

Steve please have this post deleted.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSCertifiable Points: 7524 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSCertifiable Points: 7524 More actions · Answer 4

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 SSC Guru Points: 182882 More actions · Answer 5

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.
😎
Looking through the code, everything there can be optimized with this one exception
ALTER PROCEDURE [dbo].[prEdPlanbyState]

Feivel SSCertifiable Points: 7524 More actions · Answer 6

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.
😎
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 SSC Guru Points: 182882 More actions · Answer 7

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.
😎
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?
😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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 SSC Guru Points: 182882 More actions · Answer 9

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.
😎
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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Eirikur Eiriksson - Wednesday, February 14, 2018 2:17 AM
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.
😎

Very likely. I don't really have time to do more than glance over it though. Got paying clients stuff to work on, and a conference next week.

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

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 11

ffarouqi - Tuesday, February 13, 2018 3:37 PM
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.

Just curious then... what is the job description for your current position?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

If you want to have any chance of being helped. I suggest that you start with the best practices to post in forums.
How to Post Performance Problems - SQLServerCentral
How to post a T-SQL question on a public forum | spaghettidba

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125521 More actions · Answer 13

ffarouqi - Tuesday, February 13, 2018 1:59 PM
Steve please have this post deleted.

I couldn't help but click when I saw the title "Please delete this post".

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho