Stored Procedure or not?
In these days I’m having a discussion with some DBAs about the pros&cons on using stored procedure to access the database.
I don’t like stored procedure for various reasons. The main one it tight to code maintainability. If you begin with one SP you end up you application with thousand of SPs that extract data from database in multiple forms (the customers ordered by name, the customers filtered by country, filtered by company, filtered by whatever).
What happen when I need to add a new field to the customer table? A mess!
Another reason is about business logic location. In my opinion the user request "I want to view all the customers filtered by Country" is a business rule, and as a business rule I want to code that in the business layer of my application. So I don’t want to code that in T-SQL but I want to code that in a high level, object oriented, strongly typed, debuggable, testable, manageable, readable language: C#, Java,…
One of the arguments that DBA take in favor of SP is performance. Apart that I’m not so sure that the SP give more performance (read here), are performance always the most important thing?
I agree if we talk of reporting applications, or application that have a HUGE database that has to be responsive in milliseconds (automation industry for example), but most of the application are used by humans that count in seconds, not milliseconds. So performance in not a real issue, you can get the same with ORM (if it’s rightly tuned).
Maybe the solution will came when the database vendors fill the gap between application world and database world making database more abstract from the physical representation of data.
I want to think in objects not in bytes. Instead the DBA still today has to think in bytes!
I talk some months ago about T-SQL like an assembly language for database, you can read it here.
Posted in Emanuele DelBono | 2 comments
2 Comments so far
Leave a reply
RSS
I have to agree with you 100%. Stored procedures yielding better performance is the oldest, most tiring argument. T-SQL is not suited for describing the business domain. And don’t forget DRY. Reuse (if you can call it that) is almost non-existant. If you change one thing, you usually have to update it in a million places.IMO, performance is important, but the most important thing is really maintainability.
I believe stored procedures are usefull if you need to reduce the contact surface between data and application (mainly for security purpose).
Sometimes stored procedures can also save you with perfomance issue. If you need to process a recordset of one milion records just to extract a single value that rappresent the ratio of a column, SP can save you to extract a big recordset from database. But in this case you are putting application logic inside database and you should be very carefull when you do it!