One of the most ignored features of the modern RDBMSs today is the stored procedures. In fact most of the web applications go to extreme lengths to increase performance but avoid the stored procedures. They are also one of the favorites in theory, but hated in practice. Are they so unnecessary? Are they so useless? Many say so.
Stored procedures give pre-compiled code which can execute requests in batches. This itself can help boost the performance. Of course the improvement will be dependent on the context, but other advantages like more granular access control and more mechanisms for security only promote them. A layer of stored procedures can also act as a single interface for multiple applications or clients.
Then why are they treated as ghosts? I wonder if there are other factors that drive the choice, factors which are more dependent on the programmers than on the merit of stored procedures itself. I have talked to programmers, database people (programmers, administrators), managers, testers about stored procedures and there have been extreme responses regarding stored procedures. Maybe this extremity is a problem itself. But let us dive deeper.
As Greg Jorgensen says, programmers hate RDBMSs and they hate SQL. Most of the programmers are comfortable to imperative programming, and the declarative SQL syntax only creates more friction. While I have met programmers who like the challenge of SQL, I have also found that they are not particularly fond of it. So what do they do? They google for SQL code and more often than not find a ready-made piece of code to solve their problems. This is just too easy.
As Martin Fowler says, we are moving away from using the database to share data across applications, and that nullifies one of the benefits of the stored procedures. But it is more than just liking, the programmability of the database might be against best practices of software programming. Programmers do not want the database to do anything more than manage the data. Database is not part of programming, so using stored procedures does not help much.
I think the problem is that the stored procedures are not yet programmable enough to guarantee inclusion of the entire business logic. And placing only a part of it in there can cause nightmares in maintenance. That leaves the benefit of granular access and security, but does that tilt the cost-benefit balance against the stored procedures?
This affects other decisions like composition of the development or the maintenance team, which tend to be stronger on non-DB programming side. But I believe that stored procedures can be useful in some cases, like in cases when it is possible to clearly separate the data rules and the business rules. In stead of not considering them at all, thoughtfully accepting or rejecting them is a better approach.
I have been part of desktop and network applications that used them. I haven’t seen them in any of the Web applications yet, for various reasons mentioned above. But I find it difficult to accept that stored procedures are useless. Do you use stored procedures?