Recently I was tasked with doing some investigations as to the best route to go. Now before you go getting all excited I am not going to be posting performance comparisons or declaring an outright winner. What I am going to point out is how to make the decision based on other factors.
As I was looking for feed back on the respective technologies it became very clear that this is a holy war that no one can win due to the emotional attachment to our egos and having to be right and the lack of really clear distinctions between the two.
First lets look at some basic best practise in writing maintainable software:
- Make code readable
- Use automated testing
- Use version control
- Ensure software is well designed
- Use less code
- Encapsulate
- DRY – do not repeat yourself
- Loose coupling
- Write unit tests
This is the essence of what I feel the articles in the reference section encapsulate. The primary reason for writing maintainable code (asides from having to maintain it) is to facilitate change. Businesses are becoming more dynamic and cannot afford to wait for months or years for the implementation of a vision they had. First to market is more important than ever with smaller businesses finding it easier to compete due to software and the internet.
Now the generally preferred structure of a software application is view layer, business logic layer and data layer. If designed properly one can very easily attach multiple views for different platforms to the solution without having to reengineer the business logic. The data stores can also be swapped out with relative ease or perhaps extended to include other data stores.
So what is a stored procedure? According to wikipedia: “A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.”
Now the benefits claimed with using stored procedures have always been related to performance. It is a common belief that stored procedures run quicker than generated SQL. While this might be the case with an experienced writer, I have had the distinct displeasure of seeing it go horribly wrong as well. This does not mean that I have not seen it happen in code but generally it is easier to fix the code than the stored procedure due to the unit tests. When changing a stored procedure inevitably you are going to have to change code. When changing DB structure you will have to change all the procedures that use that dataset and the code that maps to it.
Now let us get out of the emotional stuff and start comparing apples with apples.
If we have a look at the the description above on how to write maintainable code let see how stored procedures match up.
- Make code readable – Well no, it is a structured query language. While it looks like bad English sometimes it can be difficult to read.
- Use automated testing – I haven’t seen a way to automate the testing of stored procedures
- Use version control – I have not seen a way to handle versioning of stored procedures with ease
- Ensure software is well designed – Being procedural in nature there is very little design that can happen
- Use less code – There are certain things you can do in code you can’t do in SQL. So you might end up having to write far more SQL to facilitate it.
- Encapsulate – While some might argue that the procedure is encapsulated in the database I would argue that the logic is not encapsulated where it should be.
- DRY – do not repeat yourself – With having to name tables and operations continually there is a great deal of replication happening
- Loose coupling – Can stored procedures be interchanged between database vendors? Well yes, if you haven’t used vendor specific functions. It is also tightly coupled to the database unfortunately.
- Write unit tests – I would if I could! Haven’t seen this in Stored procedures.
I am not going to run the code through the same assessment as we all know that code supports all the above. Right lets get into the next point. While stored procedures might perform better, does the saving from the performance increase compliment the additional cost of maintenance attached by using stored procedures? The next question we need to ask is this. How safe is it to have business logic reside inside the database as opposed to the code base? What the you had specific rules for the same entities in a database? You would have to replicate the initial procedure and fine tune it for each entity. Now should the shared logic change you have multiple places you need to go and change. Not good!
I am not going to go into data parity mismatching either as that is a seperate article all toghether! Just know that it is there.
Lets look at it from the other side. Yes generating SQL to query a database has a certain amount of overhead. That is the only concern that people have. Let me say that again, the only con that using code over using stored procedures has is the performance aspect. So what do we do? Well lets have a look at another definition: “"Premature optimization" is a phrase used to describe a situation where a programmer lets performance considerations affect the design of a piece of code. This can result in a design that is not as clean as it could have been or code that is incorrect, because the code is complicated by the optimization and the programmer is distracted by optimizing.”
Is this not what we are doing when we allow the decision to use stored procedures affect our system designs? How about we try this from now on. Lets write the application first, get it working properly (even if it is a single feature
) and release it. Once we identify bottle necks we begin to optimise the bottle necks. This might very well include using stored procedures! Lets get out of the dark ages folks. There is no right or wrong in this realm. There is only deliver on time or don’t. Lets deliver on time 
Perfection is generally a refining process any way, expecting it on the initial iteration is absurd.
Just to clear up any ambiguity. This article does not serve to prove or disprove the use of SQL and stored procedures. The point of the article is to make you aware that using stored procedures needs to be handled the same as writing code, carefully, with a liberal serving of caution and most of all making sure that what you are gaining is worth what you spending to gain it. Store procedures have their place, as do ORM, procedural languages and hot dogs (in my tummy!)
References: