SQL Server - Individual Procedures vs. Single Procedure

views:

119

answers:

+4 Q:

SQL Server - Individual Procedures vs. Single Procedure

Hi,

In a system I'm developing I have a choice of either using a single stored procedure that does three related jobs and returns either no result, or the same result set, but sourced from two different tables.

I read an article yesterday that suggested that a stored-procedure should only have one execution plan and that any procedure that changes it's execution plan depending on a difference in parameters should probably be written as multiple procedures.

Writing the procedure as three different procedures would change how the system that executes the procedures operates but not in any significant way.

What I'd like to know is whether the performance gained from having procedures that don't have different execution plans depending on inputs vs a single procedure is worth the effort i.e. is the overhead of calling the database three times greater than the overhead of having to recompile the performance plan depending on circumstances?

Thanks

Greg

+2 A:

You might consider writing three separate stored procedures but then calling these from a single stored proc.

Chris Simpson 2009-03-19 11:08:52

For extra bonus points, what is the NAME of this design pattern?

Peter Wone 2009-03-19 11:39:59

haha, I'm rubbish with remembering these names. I do understand factory patterns and SOLID principles but though the concept remains with me, the name normally has to be recalled by a google search

Chris Simpson 2009-03-19 12:24:46

for the purposes of a nice clean stack overflow, you should probably edit your own answer to reveal this elusive pattern name

Chris Simpson 2009-03-19 12:26:28

Don't know much, if anything about design patterns, so have no idea to which one you're referring. However, what about a single procedure with 3 parts. I've seen this done previously, but never done it myself as maintainability seemed to go out the window and it was somewhat confusing.

TheMouster 2009-03-19 13:51:40

The procedures in question are multiplied out by the number of objects in the system. 10 objects = 40 procedures, 100 objects, you get the picture. I think maintenance would become an overriding factor.

TheMouster 2009-03-19 13:54:46

If you could write a single stored procedure that calls through to three separate stored procs you get the benefit of one call to sql but the separate logical processes will be compiled separately. I'm afraid I'm not quite sure what you are saying about maintenance.

Chris Simpson 2009-03-19 14:30:48

I was shown an example where one procedure had three definitions in the same file and it seemed that you could call them individually by using some form of colon operator. Can't for the life of me find any examples of such a situation on the net though. Maybe I dreamed it.

TheMouster 2009-03-19 14:38:39

Maintenance wise, I've got a situation where I have numerous objects in my system that all utilise variations on this theme. Multiple procedures per object becomes a bit of a headache to maintain. Much easier to deal with 1 procedure per object than 4.

TheMouster 2009-03-19 14:41:05

You're right about the right three, and a caller, the plans would be stored individually and give me the performance I want with the simplicity of a single point of access, but I end up with 4 things to change if the definition of a related object changes. I can probably code-gen some of it.

TheMouster 2009-03-19 14:43:41

Now I understand. Whichever method you chose you intend to repeat this pattern many times. Code gen may be the way forward although if you intend to look down this road maybe an ORM could help?

Chris Simpson 2009-03-19 15:55:44

Unless some of the functional segments have the potential for reuse by other areas of your system, I would actually recommend sticking with 1 stored procedure.

As a rule of thumb, the more SPs that you have, the more there is to maintain (I feel there is a little overhead per SP, in addition to the contents contained within the SP).

pearcewg 2009-03-19 11:35:56

None of the functional segments are reusable, which helps answer my question. The procedures would however be repeated for various objects in my system, but there's no generalisation that can be applied to the routines.

TheMouster 2009-03-19 13:33:03

Therefore meaning that I'd end up with multiple procedures where I could have one. Maintainability would decrease as a result.

TheMouster 2009-03-19 13:34:08

+2 A:

Chris Simpson correctly recognises the applicability of a design pattern that I leave to him to name. The benefit accruing most directly from application of this pattern is simplicity, which confers ease of validation (testing) and ease of maintenance.

There may be performance gains but this is be unpredictable. Sometimes complexity confuses the query plan optimiser. Breaking your über method into several simpler case-specific strategies (hint there for Chris) may reduce errors that are due to aggressive pruning of a very large decision tree, and it may also permit per case optimisations. When a "special" case is actually very typical, this can be extremely beneficial.

Nevertheless, improved verifiability and maintainability are laudable goals in their own rights.

Peter Wone 2009-03-19 11:51:24

ansaurus

tags:

views:

answers:

SQL Server - Individual Procedures vs. Single Procedure

related questions