Menu
What is the argument for and against putting code in stored procedures with the intention of making the code more maintainable (i.e. Easier to make changes to the business rules without recompiling code)?
All else being equal what makes a stored procedure better/worse for maintenance?
935
A stored procedure is cached in the server memory, making the code execution much faster than dynamic SQL.
To help you build powerful database applications, stored procedures provide several advantages including better performance, higher productivity, ease of use, and increased scalability.
Stored procedures handle large quantities of data much better; in fact EF has some limitations on how much data can be handled.
As seen from the process above, stored procedures are a secure and safe way to give access to your database. That means someone can only be able to do what is defined in stored procedures that you have given him permission to call. And that makes stored procedures great for securing data in a database.
Stored procedures are a bad practice for a number of reasons.
One of the most significant ones is separation of concerns. With stored procedures, you have business logic in the data layer, instead of in the service layer where it belongs. One consequence of this is that you now have one language and one language only in which to implement your business logic. As new technologies come along, you have no good migration path.
Another solid reason to avoid stored procedures is that stored procedures create a strong bond between you and the DB vendor. It will be very difficult to ever change to a different DB vendor, whereas having your business logic in the service layer will allow you to very easily swap out DB. So, stored procedures provide great benefit to the DB vendor, and not so much to you.
Next, scalability. It's pretty straightforward to create services in a middle tier, and distribute them across a cluster. How are you going to do that with a stored procedure? I think it would be egregiously difficult to cluster a stored procedure across, say, even eight machines.
Next, integration with other systems. If your system is pulling data from your database, relying on stored procedures, and other data from other systems, that logic will almost certainly have to be in a services layer. If some of your business logic is in the service layer, and some in the business layer, you have something of a maintenance hassle.
105
It probably depends on your system. For us, Stored Procs are used almost exclusively. We just have one website, so having the SQL statements in a stored proc makes it so much easier for our DBA to tune queries and recreate performance causing issues.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
