Stored Procedures – Another look

In Choosing a free embedded database I wrote that I searched for an embedded database with Stored Procedure support. At that time, I was all new in SQL and databases in general and viewed Stored Procedures as the best way to work with databases. My opinion has changed over the (almost) two years, however. This post describes my current view on Stored Procedures and their usage in application programming.

About Stored Procedures

Stored Procedures are pieces of SQL that reside in the RDMS itself. They have input and output parameters and can return data tables. There are numerous advantages of using SPs, such as:

• keeping all the SQL out of your code
• a little bit faster execution
• it’s not necessary to send intermediate result sets through wire, data can be processed right where it is – on the RDMS server
• possibility to limit access to some tables and allow modifying their data only through SPs

But as with any technology, there’s also disadvantages. I'll present my view on some of them, that I’ve encountered in a few projects that used SPs.

Business Logic

The ability to encapsulate business logic into Stored Procedures is probably my least favorite thing about them. Instead of having a rich Domain Model in your application’s layer, all the business rules are pushed down into Stored Procedure code. We can find everything, from authorization to complex conditional logic, there. The application itself remains only as a mean to represent the results of the SPs and invoke other SPs on them. The portion of SQL in the SP grows with every new requirement and some of the functionality begins to duplicate. Even if we find a way to refactor that piece of logic into a database function, or another SP, we get a slight performance penalty for doing this most of the time. And when there are so many calculations going on at the RDMS server already – we cannot afford that. Thus, keeping the SPs DRY becomes really hard.

The result is the same as with application code duplication – having changed one occurrence of some piece of duplicated code, we introduce a bug when we forget about the other ones.

Also, I’m concerned about testing Stored Procedures. There are ways to test them, even frameworks that do that. But when we write unit tests against some business logic in our domain models, we strive to isolate database for it being slow and now we have no choice – we must execute the whole thing on database. I haven't tried it yet, but I imagine that preparing test data for a procedure (inserting data into tables the SP is using) should be slower than doing it the traditional way in application code.

Bigger Load on Database Server

Another issue is having many long running procedures. The more such procedures are run simultaneously, the slower they will perform due to lack of resources. So the more logic you put into SPs, the quicker you hit the scaling issues of the database server. We should build our logic into our applications instead because that enables us to move a large part of the calculations from database server to application servers or client computers. The database is left with operating the data only – storing, updating and returning records. That’s what it’s meant for, isn’t it?

Procedural Application Code

I’m not talking about Stored Procedures here – they are written in procedural languages, that’s nothing new. The thing is that it also affects your application code. When you realize that all you need is to execute a Stored Procedure when some button is clicked and show the result returned in a grid, you put the code in button’s clicked event handler. Do it again and again and after a while there’s more and more code in every handler, leading us to the so called Smart UI anti-pattern. Why is it an anti-pattern? Because most of the code you write is in the UI. It usually belongs to the class of the form you’re showing data on. This way, the GUI becomes not only responsible for showing data and receiving command from user, but also retrieving that data from database, enforcing the part of rules that were not pushed into Stored Procedures and so on. A Single Responsibility Principle violation and a pain to test.

Integration through Database Only

Almost all applications must integrate with other systems at some point. But when most of the logic sits in Stored Procedures, the only logical place for integration seems to be the database itself. This leads to creating more SPs, that can have duplicated behaviour, but are meant for an external system to use. Or we can extend existing SPs, add new parameters, add some conditional logic to it, which makes your SPs even more messy.

Portability

There’s a lot of talk that ideally applications should be immune to changing RDMS, although it doesn’t happen too often. Anyways, if you are using Stored Procedures for all your business logic, you’ve got one more problem – portability. Every different RDMS has its own flavor of procedural language that is used to write Stored Procedures and functions. The more you have written, the more you’d have to port.

Conclusion

While Stored Procedures are not that bad for data processing, having business logic implemented in them is. It introduces repetition, complexity and scalability issues into your system, which we usually try to avoid. Database should be considered a part of the infrastructure, not core of the application.

CodeProject