I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values to input parameter.

Will this not be a performance issue? If multiple clients are calling the same stored procedure by passing different values to the input parameter then the stored procedure execution plan will have to be recompiled each time it is called and degrade the performance?

The only reason I can comprehend is to save from writing new service methods to call different stored procedures and reuse existing service methods by just passing different parameters.

Does anyone see any other advantage of writing the stored procedure this way?

What could be the other disadvantages of writing it this way besides performance?

  • It is not uncommon to do this. Having a separate Stored Proc for every variation on a Select will cause a huge amount of stored procs and you will even find it very hard to manage their names and there is a good chance to even write the wrong conditions. A variation would be to generate the code dynamically based on the passed parameters. – NoChance Jul 8 '15 at 13:32

Yes, I have seen significant performance problems result from this sort of pattern. If you have branches in your sproc that result in making significantly different queries, under certain circumstances this can cause huge performance problems.

SQL server will build an execution plan for a sproc when it needs to based on the values of the parameters passed into that sproc at the time when it is called. Then that execution plan will be cached and reused in subsequent calls. Depending on how the sproc is written and which branch will be executed with those parameters, it is possible for the execution plan that gets cached to be one that performs very poorly when the sproc is called with different parameters.

The worst cases I have seen of this have been when there is a bit parameter that is tested in an if statement to return different results - for example returning one record vs. returning a page of records.

This problem can behave very intermittently - if a particular branch of the sproc generates an execution plan that works well enough for the other branches then you won't notice a problem. That is, until the execution plan is rebuilt using another branch.

Performance problems with this are known and well documented. There are various workarounds, like using OPTIMIZE FOR UNKNOWN and OPTIMIZE RECOMPILE but a better option IMHO is to write sprocs to avoid these issues by limiting sprocs to perform specific tasks and avoiding branching clauses.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.