Breaking a SP into smaller modules

Question

Breaking a SP into smaller modules

anjali.vishwakarma

Ten Centuries

Points: 1112
More actions
September 22, 2008 at 2:59 am

#198274

I have a SP which has code somthing like this.
if (condition1)
begin
insert into #temp1
select * from table1
where clause1
end
if (condition2)
begin
insert into #temp1
select * from table1
where clause2
end
And so on..and there are approximately 20 conditions..
How can I break it smaller modules ?

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

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

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 1

Just call another SP in the condition... But would you really want to have 20 SP's each one being called by the other to do the one job? as it would get very messy. I would just have the 20 conditions in the SP (as you have) but just comment them clearly... I'm guessing the SP writes to the one table etc?

anjali.vishwakarma Ten Centuries Points: 1112 More actions · Answer 2

Yes...SP is writing to a single temporary table based on the condition and then that temporary table is returned at the end.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 3

we reaped huge benefits from breaking a stored procedure down like that. We ended up having a master stored procedure that had the most used condition in the first If and then it would send them to another stored procedure. Each additional condition was listed below that in order of usage.

We shaved things down from 30 secs to 2 secs in most cases.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

The only thing different in any of the original code is the IF and the WHERE... why on Earth would anyone break this into 20 separate stored procedures?

Anjali, if you want a better answer to this, we'll need some better information... like what the conditions are and what the Where clause contains. 😉

--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)

Jason Crider SSCertifiable Points: 6019 More actions · Answer 5

my response about our improvement wasn't relative to his specific example. I only stated that we had seen great improvement in our situation and it all involves testing.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Heh... and I wasn't responding to your particular post, either. In general, it seems that the direction of this thread is to split the original post into 20 stored procs... definitely NOT the way to do this. It would require duplication of code 20 times for no reason. 🙂

--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)

Jason Crider SSCertifiable Points: 6019 More actions · Answer 7

we had a variety of variables that could be there or not, so there would be conditions that determine the sp.

Simple example would be. There are more variables but that's where it was going.

if @a = 1

Begin

exec sp_1

End

If @b-2=1

Begin

exec sp_2

END

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

anjali.vishwakarma Ten Centuries Points: 1112 More actions · Answer 8

But that would mean creating 20 SP's more...that would be more difficult to manage. Any other possible solution ?

Jason Crider SSCertifiable Points: 6019 More actions · Answer 9

Well, look at the idea of a stored proc not needing to recompile everytime. If you are passing it different variables in each condition, we've had the best experiencing when breaking them up. It's not perfect but it really helped in our situation. Yes, it creates more stored procedures to manage, but each time that child stored proc is called it will have the same variables in it and it should have a plan cached.

There may be other options but that one fit us best.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Jack Corbett SSC Guru Points: 184393 More actions · Answer 10

anjali.vishwakarma (9/23/2008)
But that would mean creating 20 SP's more...that would be more difficult to manage. Any other possible solution ?

You asked about breaking it down into smaller modules and the only way to do that in SQL Server is to create either more stored procedures or functions.

If there are significant logic differences and/or where conditions for each criteria, I agree with Jason that, performance-wise, breaking them out into child procedures will likely perform better. You obviously had or anticipate some issue with maintenance or performance of the current procedure or you wouldn't have asked the question.

Another benefit I see in breaking down the procedures is that you could choose to move the conditional logic to the application and call the specific procedure directly.

I do also agree with Jeff, that without specifics we can't give a specific answer.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jerry Hung SSChampion Points: 12968 More actions · Answer 11

We just had this issue yesterday where the Query Plan is not what it should be using, and a sp_recompile took the query from 10 secs to 1~2 secs

This is what you may get if IF conditions are very different

Instead of 20 new SPs, I suppose you could find the optimized Query Plan Guide for each

and in each IF condition SELECT statements, call it with the Query Plan Guide hint so it'll use THAT particular plan each time

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Folks, please... go back and look at the original post... the queries are identical except for the IF and the WHERE... just move the IF to the WHERE and be done with it.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (9/24/2008)
Folks, please... go back and look at the original post... the queries are identical except for the IF and the WHERE... just move the IF to the WHERE and be done with it.

I agree that given the example, Jeff's method would likely be the method I implement.

If you're really hell-bent on trying to reduce the amount of duplication of actual SQL code, then you might consider dynamic code (store the base SQL in one string, dynamic smack on a where clause, and execute the result), but again - I'd do that only if the query I was copying over and over was complex.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

Hey MATT! Congrats on becoming a proud member of the over 5k crowd!

--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)