SQL Server - Individual Procedures Versus Single Procedure

Question

In the system I am developing, I have a choice either using one stored procedure that performs three related tasks and does not return either the result or the same set of results, but is obtained from two different tables.

I read an article yesterday suggesting that a stored procedure should have only one execution plan and that any procedure that changes its execution plan depending on the difference in parameters should probably be written as several procedures.

Writing a procedure in the form of three different procedures would change how the system performing the procedures works, but not to a large extent.

What I would like to know is whether the performance obtained from procedures that do not have different execution plans, depending on the input data compared to one procedure, is the overhead of calling the database three times as much, than because of the need to recompile the implementation plan, depending on the circumstances?

thanks

Greg

Answer 1

Perhaps you should write three separate stored procedures, but then call them from one stored proc.

Answer 2

Chris Simpson correctly recognizes the applicability of the design template, which I leave to him to name him. The advantage obtained directly from the application of this template is simplicity, which provides ease of verification (testing) and ease of maintenance.

There may be a performance gain, but this is unpredictable. Sometimes complexity confuses the query plan optimizer. Breaking down your über method into a few simpler strategies for specific cases (a hint for Chris) can reduce the errors caused by aggressive pruning of a very large decision tree, and it can also help optimize each case. When a “special” case is actually very typical, it can be very helpful.

However, improved verifiability and maintainability are meritorious goals in their own rights.

Answer 3

If some functional segments cannot be reused by other areas of your system, I would recommend sticking to 1 stored procedure.

Generally, the more SPs you have, the more you need to maintain (I feel that there is a bit of overhead in SP, in addition to the content contained in SP).