Why I don’t like database stored proceedures

There’s a number of compelling reasons not to use them. Here’s just a few:

1. They can’t be stepped through and debugged by your standard IDE. Admittedly, neither can the other dirty SQL you shove in your code however because the majority of SQL lacks logic beyond IF() this is less of a problem.
2. The errors created by the DBMS on the failure of a stored procedure are often very cryptic and relate to an underlying database error caused by a single line in the stored procedure.
3. You’re passing off the processing load to your database server. Databases, for example MySQL, are a lot harder to scale than hosting environments such as PHP-FPM. Research shows a very marginal performance boost in using stored procedures but I don’t think that this is enough to hide the fact that your database server will process less transactions per second.
4. You lose portability. A good database abstraction layer in your code should make it portable between databases. Using stored procedures negates this.
5. In the words of @altreus… they’re not in the code base, they’re not in the code base, they’re not in the code base. As such, they don’t track via version control systems. Of course, your database migrations should contain the stored procedures but each change is a new file. This isn’t how version control works.
6. Further to the above, they’re anti-version control. Not only must a developer have his own version of a codebase, he must also have his own version of the database. When working in a team, this adds further complexities and makes it a fruitless task for individual developers to unit test builds prior to committing.
7. Stored procedures aren’t reusable in the same way that code is. This is because they lack library support.

Some, or indeed all, of the above may well be entirely wrong. I still don’t like Stored Procedures. Sorry.

Was this Post Helpful?

Then buy me a beer (please)!