CODEWITHSUNDEEP
sql-serverperformancestored-procedures
TheMouster
TheMouster

Reputation: 43

SQL Server - Individual Procedures vs. Single Procedure

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

Upvotes: 4

Views: 221

Answers (3)

Peter Wone
Peter Wone

Reputation: 18765

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.

Upvotes: 2

pearcewg
pearcewg

Reputation: 9613

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

Upvotes: 0

Chris Simpson
Chris Simpson

Reputation: 7990

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

Upvotes: 2

Related Questions