Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-01-21 : 17:15:32
|
| Hi All -I have noticed that we have quite a large number of stored procedures that have multiple operations in them: - SELECT a bunch of data from various tables into a Temp or Work table. - Perform some DML on this data - JOIN the Temp/Work table with another table and then UPDATE based on some condition - JOIN the Temp/Work table with a table and then INSERT for any not in the Temp/Work table - etc.What I am wondering is whether it is better to separate many of these operations into their own stored procs...so as to possibly gain some performance benefit if the server can better cache the execution plans for these.thanks for any info. - will |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-21 : 17:19:04
|
There usually are no bigger problems having large stored procedure, as long the control flow is the same.If there are IF's to control the flow, you usually have problems.If the control flow is the same, the stored procedure re-uses the execution plan.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-01-21 : 17:32:16
|
| Thank you for the reply and information. Some of these large stored procs do have IF conditions. I'll take a closer look at those.Thanks, - will |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 20:13:44
|
| Read this:http://support.microsoft.com/kb/243586 |
|
|
|
|
|
|
One big Stored Proc vs Multiple Stored Procs