There is this debate raging among developers: “Is it better to use stored procedures or not use stored procedures”. From first glance, this seems like a simple question, but there are some complicated implications around this question. Here’s the basic pros and cons of using stored procedures in your system:
Pros
- You can isolate table changes from your front-end.
- Updating business code is easy, just update the stored procedure while your system is running (assuming you already tested the changes off-line and everything is good).
- Many queries can be reduced to one call to the database making your system faster.
Cons
- Your business processing is performed by your database which is expensive (license fees compared to web servers).
- Unit testing is all but impossible.
- Version control is difficult.
- You are married to your database server technology (MS SQL cannot be easily ported to Oracle).
- Stored Procedure languages are not well structured. This leads to spaghetti code.
Now, let’s expand a bit and discuss each of these issues:
Isolate Table Changes
It may seem like overkill to setup all of your select, insert, update and delete queries to call a stored procedure, then have the stored procedure call the tables. This can be a boon for situations where you might change your table structure in the future. An example would be a situation where a table becomes too wide and you want to break the table into two tables. Your stored procedures can have the same interface to your program and the code inside could handle all the details of where the data is stored and read from. Removing a field from a table can also be safely done, since you can just remove it inside the stored procedure but leave the parameter called from your software until you can root out all calls to the database and change those (or leave the dead parameter).
Updating Business Code
Business code is the logic that computes and performs work on your database. Your stored procedure might update one table, add an entry to a log table and remove corresponding records from another table. Another use for a stored procedure is to pass filter and ordering information into a query for a list of data. A dynamic query can be formed in a stored procedure and executed with all the parameters entered. This relieves your front-end from the task of sorting and filtering data. It can also reduce the amount of raw data returned from the database.
The point here is that your business code might need a change or an enhancement. If there is a bug, then it can be fixed and deployed on the fly. A .Net application must be compiled and carefully deployed on servers that are not accessed during the time of deployment. A stored procedure can be changed on the fly. If your website is a large monolithic application, this capability becomes a larger “pro”.
One Call to the Database
If a record edit requires a log entry, then you’ll be forced to call your database twice. If you use a stored procedure, the stored procedure code can make those calls, which would be performed from the database itself and not over the wire. This can reduce the latency time to perform such an operation.
Now for the list of cons…
Business Processing is Being Performed by the Database
MS SQL server and Oracle licensing is expensive. Both licenses are based on number of CPUs and the price can get steep. If you have a choice between performing your processing on a web server or a database server, it’s a no-brainer. Do it on the web server. Web servers, even an MS Server license is cheaper than a SQL Server license. Initially your business will not feel the cost difference because your system is small. However, once your system grows you’ll see your costs increase on an exponential scale.
Unit Testing
Unit testing is not available for stored procedures. You could probably get creative and setup a testing environment in Visual Studio, but it would be custom. Without unit tests, you are not able to regression test. If your stored procedures contain logic that allows different modes to occur, it can be difficult to properly test each mode. An example is a stored procedure that performs filter and sorting operations. If you are building a dynamic query using “if” statements, then you’ll have a combination of different possible inputs. How do you ensure that your dynamic query doesn’t have a missing “and” or a missing comma, or a union query with non-matching fields? It’s difficult. If this logic is in your website code, you can wrap each combination of inputs in unit tests to provide regression testing when you add a filter or sorting option or change an option.
Version Control
No matter how you divide your code, you’ll need to make sure you version control your database so you can keep a history of your changes and match those changes with your code. Visual Studio allows you to define all your database objects in a project (see SQL Server Database Project type). There are tools available to allow you to create change scripts from two Team Foundation Server versions. This can be used to update multiple databases. Versioning a database is not a common practice and that is why I’ve put this under the “con” list instead of the “pro”. For companies that keep their database definitions in a version control system, they can take this off the “con” list.
Married to Your Database Server
Till death do you part! If you ever decide to switch from one database server technology to another, you’ll discover how steep the hill is that you’ll need to climb. Each stored procedure will need to be converted by hand one-by-one. If your system doesn’t have stored procedures, then you’ll have an easier time converting. Minor differences between triggers and indexes might be an issue between Oracle and SQL, and there’s the recursive query in Oracle that is different. You might even have issues with left and right outer joins if you used the “(+)” symbol in Oracle. Stored procedures will be your Achilles heel.
Spaghetti Code
Writing stored procedures is a lot like writing in Classic ASP. It’s messy. I see a lot of sloppy coding practices. There is no standard for formatting queries or TSQL code. Everybody has their own short-cuts. Once a system grows to a point where it contains tens of thousands of stored procedures, you’re faced with a mess that has no hope. C# code has the luxury of being able to be refactored. This is a powerful capability that can be used to reduce entangled code. Being able to break code into small and management chunks is also helpful. If your database code is contained in a Visual Studio project, you can perform some of the same refactoring, but you can’t test on the fly. So programmers prefer to change stored procedures on their test database where refactoring is not available.
Conclusion
Are there more pros and cons? Sure. Every company has special needs for their database. Some companies have a lot of bulk table to table processing that must be performed. That screams stored procedures, and I would recommend sticking with that technique. Other companies have a website front-end with large quantities of small transactions. I would recommend those companies keep their business logic in their website code where they can unit test their logic. In the end, you’ll need to take this list of pros and cons and decide which item to give more weight. Your scale may tip one way or the other depending on which compromise you want to make.