Are Stored Procedures Good or Bad? And when to use them?

There are a hell of a lot of articles / posts on “Stored Procedures are bad” or “Stored Procedures are good” all of which are written from one aspect or point of view. They never really offer much in terms of when or when not to use them for a developer (or someone at Uni or even for an application / system architect re-thinking things).  You can almost always tell the area of IT in which these people specialise in (or have their most experience) just based on their views on this subject….So what is the big problem? Why the discussion…..

Well, people like to have “golden rules” or “answers”. In this case, there isn’t a particular view that is right, nor wrong, rather only the correct view for a particular organisations requirement. Don’t leave the decision in a data architects hands, nor an application developers (both bring different skills and views to the table). So when embarking on a new project, or a database overhaul, you really need to bring together a number of key personell to ensure you use your database – and stored procedures wisely….There is no golden rule of “Stored procedures are evil” or “Stored Procedures are great”….

Understanding the real pro’s and con’s

Before you can really determine when best to use a stored procedure, you have to think about what are the benefits of using them and the negatives. Ofcourse – this is where troubles arise – some people have arguments that are valid – other ones not so valid. So lets break these down….

Pro’s

Well first off, lets think about learning this rather than going based on “what we have learnt in the past”. So, we can all agree that modularisation and de-coupling is a good thing. So de-coupling our data from our applications can only be a good thing. Great. Now how do we extract that data in the most modular fashion and having minimal reliance on anything else. The answer, a Stored Procedure. The stored procedure provides data architects (and other database roles) with the ability to maintain the database as a seperate entity (as much as can be possible). This provides great flexibility along with other benefits.

Now some will argue this could be done in a DAL (Data Access Layer), which is true, however this means that we have in effect “coupled” our database to another external layer. Now who will maintain this DAL? How accessible and easy is it to update especially if a number of LOBs are dependent on it? In addition, a DAL will be written in what language? Languages change far more frequent than our actual data – many companies will have data that is a hell of a lot older (maybe 40-50 years older) than reletively new languages. To illustrate my point, if you used a DAL written in COM+, how easy would it be for an organisation to find someone to write a web page that utilises this COM+ DAL layer in todays market place? Not great. However, can I find someone who can code that web page and have it interact with a stored procedure – oh, yes I can very easily…

Another pro – is that you can “tune” your stored procedure without really having an effect on calling applications. No code needs to be re-compiled, deployment made etc etc. All that is required is a competent data architect or administrator. This ability to “tune” is because the database doesnt have the overhead of being linked to a particular layer (or GUI if bound).

Performance is sometimes mentioned here, especially as stored procedures are compiled in some cases. Now, in recent years dynamic SQL performance has caught up – however, lets look at the bigger picture of performance. I have worked on a number of projects where a DAL layer was used, and yes, it executed the query almost at the same speed as our SP (most occasions the SP was still faster), however, for more and more complex queries and business rules, the DAL layer became slower. Why? Basically because it used data in-efficiently – bringing some data back, passing it to a business layer to implement rules before making another call to the DB. In such a case – this “technically” is the better solution – with business logic sitting outside the database, however, for performance, placing it inside the database provided far greater performance….

Finally, who actually writes the stored procedures? Often it is someone with extensive experience in this area, someone who can write the SP quite quickly and accurately. Ask yourself who will write it in a business application or DAL? More often than not the junior developer , (that could be a little harsh) or people who dont have as much experience of SQL as they could have – or experienced people who just dont have that much extensive knowledge of the data model / its architecture….In all of these cases – this can lead to poor performance queries and ultimately poor application performance….

So the supposed con’s

Many feel that you have a “vendor lock-in” which on the web – everyone hates (it seems on the web most people want the earth from their software for free and for it to be maintained by the most ethical people in the world who do it to help developers etc and not for a penny..hmmmm). In addition, how often does a company actually migrate to a different database, not often. In addition – if you write your stored procedures (sticking as closely as you can to more standard SQL) then migration may not be such a big issue. To be honest, if you migrate data – if you have a DAL or application that tie to a particular database, good luck re-coding and testing your dynamic SQL there…

Algorithm tuning is also sometimes seen as a negative, stating that millions of people (developers) tune SQL and only a handful can do that for stored procedures. This is really misguided. Developers who write SQL (even in their millions) will not bring to the table anyting more in terms of tuning than a handful of good database architects…

Security…Oh well, this is a classic issue. Many think because it runs in compiled code it is more secure. True to an extent. But how many people (developers) has access to this? In addition, database security is not as bad as it once was, with complex user/role security in place you can really lock down a database and it will only be accessed (admin rights etc) by far fewer people – with less knowing the full database schema. This last point, is probably a negative one – especially if your organisation looses a few good DB men…but its key in such cases to ensure you get proper handovers completed (be responsible as an organisation).

A big, and valid negative, is that you have far greater “power” with dynamic SQL and in a proper language. You can perform greater calculations, applie business rules etc etc. SQL is a little, well restrictive. But thats a good thing – see the next section…

Stored procedures can be highly restrictive – especially when you have a very dynamic database schema. By this I mean one which may well update itself or an application updates it due to business requirements. These are more common than you might think – though by no means are they the norm. In such cases, stored procedures and SQL are far too rigid and lacking in functionality. In such cases you really will need your own DAL…

So when to use a stored procedure and when not to…

As you may have guessed, I am more in favour of the Stored Procedure than not. However, there are those occasions when they just arent a great idea…Its identifiy when to use them, how to use them, and when not to use them for your applications that is key to wheather they are good, or bad for you…

So when / how to use them:

• Use them for basic and moderate complex typical functions (insert, delete, update etc)
• Keep your SPs as standard as possible and not over complex
• Dont be scared to have stored procedures that do contain “business logic” (do not get this confused with application logic – they are different)
• Use SPs, triggers etc to enforce data integrity (your data will last longer than your applications and their chosen language)

When not to use them:

• When your DB schema is more “dynamic” and therefore requires a more powerful language with features to understand how to use it (insert, delete, update)
• When your application requires a lot of application logic from the extracted data
• When you need to utilise a greater / more unique security model on the data (though in this case you may well use a mixture of dynamic SQL and stored procedures to get the job done)

With all of this in mind, to answer are Stored Procedures good or bad? They are great – when you use them correctly…..They are poor when your schema is too dynamic or complex and they are evil when you use them incorrectly….