Monolithic SP or Separate SP's? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Monolithic SP or Separate SP's?

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-10-06 : 14:42:46

I have 3 recordsets that I'd like returned. I was considering whether to put the code to return them all in one SP or make an SP that calls 3 separate SP's and I came up with the following guidelines:

Use single monolithic SP if
- passing large parameters
- duplicating operations inside each one (like splitting a CSV string that is needed in each)
- passing data between them through temp tables (causes recompiles)

Use three SP's called by one otherwise
- more maintainable
- each SP has its own execution plan so if it needs to recompile it won’t recompile your whole big procedure.

Anything else to consider? Locking issues?

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 15:15:27

You forgot regression testing when you need to make a change. That means, you should test the functionality of all three instead of one.

Make 3

What's so hard to clone anyway?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 02:23:30

Do you mean you need to do something like:

SELECT * FROM Table1 WHERE COL1 = 'FOO'
SELECT * FROM Table2 WHERE COL2 = 'BAR'
SELECT * FROM Table3 WHERE COL3 = 'FOOBAR'

(obviously more complicated!)

if so isn't there a case for putting them in one Sproc so that the query plan covers the whole job?

If you are going to do:


IF @PARAM1 = 1
   SELECT * FROM Table1 WHERE COL1 = 'FOO'
ELSE
IF @PARAM1 = 2
   SELECT * FROM Table2 WHERE COL2 = 'BAR'
ELSE
IF @PARAM1 = 3
   SELECT * FROM Table3 WHERE COL3 = 'FOOBAR'
END

then I would probably replace the SELECTs with EXEC Sproc calls instead.

Kristen

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-10-07 : 08:36:31

Something like

CREATE mod1SP AS
SELECT * FROM Table1 WHERE COL1 = 'FOO'
GO
CREATE mod2SP AS
SELECT * FROM Table2 WHERE COL2 = 'BAR'
GO
CREATE mod3SP AS
SELECT * FROM Table3 WHERE COL3 = 'FOOBAR'
GO

CREATE modularSP AS
EXEC mod1SP
EXEC mod2SP
EXEC mod3SP
GO

CREATE monoSP AS
SELECT * FROM Table1 WHERE COL1 = 'FOO'
SELECT * FROM Table2 WHERE COL2 = 'BAR'
SELECT * FROM Table3 WHERE COL3 = 'FOOBAR'
GO

I'm trying to decide between
EXEC modularSP and EXEC monoSP
to get 3 recordsets.

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 08:50:17

I would use monoSP (so that the query plan covers the lot) than I would use modularSP (ratio about 90:10 I would guess - I would use the modularSP if its components were going to get reuse elsewhere and it was important to centralise the logic

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 09:48:26

http://www.metrolyrics.com/lyrics/115340/Ray_Charles/Baby_What'd_I_Say

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources