My Love Hate Relationship With Stored Procedures

Over the 16 years I have been using SQL Server, I go hot and cold about the usefulness of stored procedures.  For a while I hated them and didn’t have anything to do with them, then I got in the habit of using them everywhere.  At this point in my personnel best practices, I have turned against them again.   Here is why:

Source Control

It is harder to check in stored procedures into source control than checking in C# source.  Instead of stored procedures, I’m using inline parameterized queries in my C# code.  Either in the model (MVC) or in the business object layer. I can check the code in to source control and that checks in the Transact-SQL statements that are in the code – all in one revision of the source control.

Deploying

Having the Transact-SQL inline  also makes deploying the code easier.  Instead of deploying stored procedures and matching web site code, I just need to deploy the ASP.NET code and the complied assemblies (for the object library).

Performance

There is no difference between the performance of stored procedures and parameterized queries.  However, it is easier for a junior developer to introduce performance issues with stored procedures.  Here is an example:

1. Senior Developer writes a C# method called A() and a matching stored procedure with one column returned.
2. During testing, it is determined that a non-clustered covered index is needed for the stored procedure to give it optimal performance.
3. Junior developer needs to extend the class with a new method. He notices that the method A() does almost what the new method needs to do.  So the Junior developer creates a new method B() that calls the same stored procedure.  Because he needs an additional column he adds another column to be returned from the stored procedure.

The performance issue is that the covered index doesn’t cover the columns that the stored procedure now returns.  This makes A() method run slow again – a bug that might not be found without regression or performance testing  testing. 

 

It is too tempting to reuse stored procedures, instead of creating new ones.  However, at the same time if you create new stored procedures every time, you end up with a lot of stored procedures to wade through, updated on schema changes, deploy to production SQL Servers, and clean up.

Clean Up

It seems like the list of stored procedures always has lots of unused procedures just hanging out.  Here is a scenario that I have seen before:

1. Developer needs to modify a stored procedure called spFetchLocation, however doesn’t want to break anything that might be calling that stored procedure. 
2. He creates a copy calling it spFetchLocation2 and starts calling it instead of spFetchLocation.
3. spFetchLocation never gets cleaned up – and there is no way to easily audit if the stored procedure is still in use in a SQL job, C# code, or nested stored procedure.

This type of renaming happens more often if stored procedures are nested in other stored procedures, since it is harder to track the flow of the code path.

 

With inline parameterized queries in the C# code, this is less of an issue.  Since changing the method involves changing the Transact-SQL in the code, there is no worry that code is being called from somewhere else.

Summary

Right now I have a love affair with inline parameterized queries, and stored procedures are my ex – however once in a while I find a good use for them.  Ask me again in a couple of years weather I like stored procedures.  The answer might be different than today.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}