Single sp vs multiple sp - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Single sp vs multiple sp

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-10-06 : 15:13:45

I've got a table with multiple columns that will need to be updated at least once a day.

I'm wondering if it's OK to just write a single stored procedure that contains multiple updates or if it's best to write an sp for each update and call them from a "master" sp.

Thanks in advance,

Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 15:35:28

It depends. You have to be careful with multiple code paths due to differing execution plans. Could you show us a sample of the updates?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-10-06 : 15:48:18

quote:
Originally posted by tkizer

It depends. You have to be careful with multiple code paths due to differing execution plans. Could you show us a sample of the updates?

Tara Kizer

I'm curious what you mean? Since each query will have its own execution plan regardless of which Stored Procedure it belongs too.

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-10-06 : 15:48:53

Here you go -

--Update job status
UPDATE p
SET JobComplete = 'Y'
,ActualCompleteDate = j.ActCompleteDate
,PercentComplete = 1
FROM dbo.MfgProgress p
INNER JOIN dbo.JobMaster j
ON p.Job = j.Job
WHERE j.Complete = 'Y'
AND p.JobComplete <> 'Y'

--Update wire cut status
UPDATE dbo.MfgProgress
SET WCComplete = 'Y'
WHERE Job NOT IN
(SELECT Job
FROM dbo.JobOperations
WHERE Job IN
(SELECT Job
FROM dbo.MfgProgress)
AND LEFT(WorkCentre, 2) = 'WC'
AND OperCompleted = 'N')
AND WCComplete <> 'Y'

--Update shortage status
UPDATE dbo.MfgProgress
SET Shortage = 'Y'
WHERE Job IN
(SELECT Job FROM dbo.OperationAllocations
WHERE Job IN
(SELECT Job FROM dbo.MfgProgress)
AND JobShortageStatus = 'S')
AND Shortage <> 'Y'

--Update run time issued
UPDATE p
SET RunTimeIssued = o.SUMRunTimeIssued
FROM dbo.MfgProgress p
INNER JOIN
(SELECT Job, SUM(RunTimeIssued) AS SUMRunTimeIssued
FROM dbo.JobOperations GROUP BY Job) o
ON p.Job = o.Job
WHERE RunTimeIssued <> o.SUMRunTimeIssued

--Update expected run time
UPDATE p
SET ExpectedRunTime = o.SUMICapcityReqd
FROM dbo.MfgProgress p
INNER JOIN
(SELECT Job, SUM(ICapacityReqd) AS SUMICapcityReqd
FROM dbo.JobOperations GROUP BY Job) o
ON p.Job = o.Job
WHERE ExpectedRunTime <> o.SUMICapcityReqd

--Update adjusted expected run time
UPDATE p
SET AdjExpectedRunTime = o.SUMAdjExpRunTime
FROM dbo.MfgProgress p
INNER JOIN
(SELECT Job, SUM( CASE WHEN RunTimeIssued > ICapacityReqd THEN RunTimeIssued ELSE ICapacityReqd END ) AS SUMAdjExpRunTime
FROM dbo.JobOperations GROUP BY Job) o
ON p.Job = o.Job
WHERE AdjExpectedRunTime <> o.SUMAdjExpRunTime

--Update percent complete
UPDATE dbo.MfgProgress
SET PercentComplete = RunTimeIssued/AdjExpectedRunTime
WHERE PercentComplete <> RunTimeIssued/AdjExpectedRunTime
AND PercentComplete <> 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 15:52:06

quote:
Originally posted by denis_the_thief

quote:
Originally posted by tkizer

It depends. You have to be careful with multiple code paths due to differing execution plans. Could you show us a sample of the updates?

Tara Kizer

I'm curious what you mean? Since each query will have its own execution plan regardless of which Stored Procedure it belongs too.

A stored procedure can only have one execution plan for a given set of connection settings. Within that one plan can be multiple plans from each query. The multiple code paths will lead to problems when that one stored procedure plan doesn't cover the various combinations for the code paths. This type of thing is one example of a bad execution plan. Sure, it can recompile, but that can be costly if it runs frequently. If it doesn't recompile and the plan in cache doesn't cover your execution, well that's a bad plan. As a result, you should break them into their own stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 15:54:06

MorrisK, yeah I would break that up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 15:59:21

quote:
Originally posted by denis_the_thief

quote:
Originally posted by tkizer

It depends. You have to be careful with multiple code paths due to differing execution plans. Could you show us a sample of the updates?

Tara Kizer

I'm curious what you mean? Since each query will have its own execution plan regardless of which Stored Procedure it belongs too.

Execution plans are generated per-batch, not per statement. So if you have multiple execution paths in a proc, all of them get optimised at the same time and the plan for the procedure gets reused and reused and reused.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 16:02:08

quote:
Originally posted by tkizer

The multiple code paths will lead to problems when that one stored procedure plan doesn't cover the various combinations for the code paths. This type of thing is one example of a bad execution plan. Sure, it can recompile, but that can be costly if it runs frequently.

The generated plan will always cover all of the possible execution paths because the optimiser will optimise every query within the plan regardless or whether it can be executed or not. The optimiser doesn't evaluate conditionals.

The procedure won't recompile when executed with different parameters and that reuse of the plan can cause poor performance if a query was optimised with a parameter value it can't be executed with.

--
Gail Shaw
SQL Server MVP

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-10-06 : 16:02:23

OK. Thanks for the help.

Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 16:04:00

Alright, I bow down in shame.

My explanation is wrong, but I think we're both saying the same end result: break them up. Right?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2011-10-06 : 16:09:48

quote:

The procedure won't recompile when executed with different parameters and that reuse of the plan can cause poor performance if a query was optimised with a parameter value it can't be executed with.

--
Gail Shaw
SQL Server MVP

In this case there are no input parameters so does that make a difference?

Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 16:13:13

No need to break it up in that case.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-08 : 10:21:25

Whatever way you choose but dont forget to implement transaction in you code.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources