In Defense of Stored Procedures

Based on the comments regarding the relative benefits of stored procedures versus so-called in-line SQL a these blogs: Rob Howard's and Frans Bouma's, I decided throw a little something together here in defense of stored procs (and Rob to a certain extent).  Some of this will be my own thoughts, and I'll share some comments made on an email list made up of mostly AspInsiders and MVPs (and all experienced developers).  First, I'll paraphrase the comments of others. 

Paul D. Murphy says:
The argument Frans presents against stored procedures based on portability is not valid, especially for enterprise databases. Once you start to deal with real data needs (millions of records, TPS rates of 300+, etc..), you always will need to take advantage of database-specific table/row locking keywords or database-specific usability features.

So if you can't use the same dynamically created SQL statements, what's the point in not putting the data logic in the database? Microsoft's abstractions found in the various data library interfaces are the most robust way to achieve portability across different database providers. It's very simple, code your DAL factory against the interfaces, code your DAL objects against a strong provider, and use reflection. Don't drop to the lowest common denominator (ANSI SQL) cause that will only give you a DAL that isn't scalable.

In enterprise scenarios, if Joe Programmer decides he wants to get some data based on a pattern match in the address line of a record under normal conditions (200 transactions per second, indexes tuned for zip code and phone number searches and NOLOCK for straight lookups) this simple statement will crash an enterprise database.

Sprocs are more robust; so you can do more with them naturally. But the same basic rules apply. You can't arbitrarily change queries on 'real' databases.  Normalization/Denormalization is a data function that physically needs to exist in the database product. Abstractions and indirections are the keys to successful scalable programming.

Short cutting the rules might work, but that doesn't make it the right way to do it if growth, code longevity and scalability are important factors.

Shared by Phil Winstanley (though not necessarily his own opinions):
The arguments for inline SQL do not consider if your data-consuming applications are not in the same environment.  Writing stored procedures allows any environment that can interact with the database to use your data access code.

Stored procedures can be used to form a contract between the database and the consuming applications, and storing data access code on the server gives you a centralized place to maintain that code, instead of searching through various components that could in turn be interfacing with others.

Whether or not you use stored procedures, if you change or add columns, for example, you will have to modify your code.  Using in-line only does not help much in this case.

From Steve Schofield:
Being from the admin side of the planet, I've have to support applications that both use stored procedures and don't. "In My experience" applications don't perform nearly as well and have had to purchase more equipment to scale vs applications that use stored procedures. Now that doesn't say applications that use dynamic sql don't scale as well. I just have to live the pain everyday of throwing more hardware at enterprise applications that don't use stored procedures.

And now for my own thoughts on the subject:
One thing that's not really addressed in the arguments that I've seen is the readability of DA code when you use in-line SQL.  You have to put it all in quotes and on multiple lines, storing in strings, etc., or you build it bit by bit on multiple lines separated by a lot of flow control.  It makes the resulting SQL damn near impossible to read w/o profiling, and sometimes you can't profile, so it's hard to debug as well. 

Stored procedures, on the other hand, can easily be opened up in Query Analyzer and debugged there, substiting values for params where necessary.  Then when you've got the query working like you need it to, you can pretty much just copy and paste back into your stored proc, and you're done.   There's no need to figure out how to divide it up into the various pieces or split across many lines or replace quotes here and there, etc.

Putting the code into a stored proc makes it very easy to read, generally, especially with some tools, like VS.NET, that block out queries and color code, and so on.

Further, making any tweaks to an in-line query in .NET (or Java) would require a recompile and redeploy of the related data access code.  Often you can make many tweaks to a query without it breaking the parameter interface.  You can do this without needing to recompile and redeploy your application.  This point is particularly poignant when you have disparate components consuming the same data access code.  In this sense, ad-hoc/in-line queries are definitely brittle as compared to stored procedures.

So I think the arguments that in-line SQL is more developer-friendly are invalid in many cases.  Add to the points above the fact that you can often off-load much of the data access code onto a DBA (who is likely better at T-SQL and performance tuning than you), and there really is no comparison, in terms of developer friendliness. 

And even if you write your own, using stored procs keeps you honest if you have a DBA who goes over them to make sure they're not going to adversely affect the databases, be it in terms of performance or security.  You can also still use the DBA for performance tuning, as he can easily review a stored proc to see where he can modify it or indices or add views, etc.  Many DBAs cannot or do not want to read through in-line queries for this purpose, so you'll be on your own.  This applies both to development and problem resolution.  If you do it all in-line, you'll get the phone call as well as the DBA; whereas if it's all in stored procs, the DBA can probably solve the issue. 

Stored procedures are clearly the way to go in these respects.

Now I would like to point out that Frans was not exactly being fair in his blog.  He took out a couple points Rob made and presented them as if they were the entire argument, which is not true.  Apart from general flaming and blustering, which in my opinion show a lack of confidence in one's position, there are the following to consider.

For performance, Frans, IMO, did not address the first two (and more important) paragraphs that Rob wrote, which deal with filtering, sorting, and multiple-query/batch queries.  These are very important and valid points that have not really been addressed by the 'opposing party'.  Even though it's true that you can put multiple queries in one command batch, you multiply the difficulties I addressed above in terms of readability, maintainability, and performance tuning.  And I'm not sure how SqlCommand would handle parameters with multiple queries or if it'd work at all.

Instead he focused on the issue of caching, which is a minimal point.  I am not a SQL Server expert, but most of the SQL Server experts I know (and I'm talking about very experienced developers, MVPs, and DBAs, such as those who work at huge corporations like Verizon that deal with millions and millions of records and transactions) advocate stored procedures over in-line T-SQL.  As others have commented, though dynamic queries are cached, they are still handled differently.  And in my own experience, I've noticed that they tend to cache for a short period of time.  But if we put our code into stored procedures, performance on the whole becomes less of an issue because of the reasons given above (easier tuning and availability of DBA expertise).

For security, we'll ignore the stupid comments made towards Rob.  Obviously we all know about roles.  The points are still valid.  Using stored procedures and views give you better protection and finer control over how your data is accessed and manipulated.  Even if you use a view instead of granting a login permission to a table, you still are limited in terms of how fine you can control access, and I've heard conflicting opinions on updating through views, so often folks grant modification permissions to the table itself.  Using stored procedures gives you a very fine grain control over what users and roles can do, and you can quickly and easily revoke specific permissions if needed without affecting the rest of the applications' usage of the data. 

Furthermore, arguing that you should use only views for fine-grained control throws you back into a management nightmare because then instead of having many stored procedures to maintain, you then have many views to maintain.  The simple truth is that using stored procedures, with or without views, will give you finer control (better security) than the opposing position.  At a bare minimum, you can see that you can use SQL roles (that Frans advocates) with or without stored procs, so using them as an argument in favor of in-line queries or against stored procs is faulty.

And as for recommending using stored procedures as a way to prevent injection attacks, one must understand the audience that Rob must deal with.  These are typically the folks who do not know about or remember to use ADO.NET parameters, so recommending stored procedures is a guard against lazy developers (most of us) who would simply use concatenated T-SQL otherwise.  Recommending stored procedures for this reason is entirely valid, though it is good to also point out that using parameters with in-line SQL is also a valid way to prevent such attacks.

All-in-all, I would say whether or not you use stored procedures depends on your situation.  There are some cases where it just might not make sense, such as tiny (in terms of amount of code and time to develop), single-developer projects, dynamic query-builders, and the like.  But I would say for the vast majority of long-lived, “real-world” applications, especially on multi-developer teams with DBAs, you should use stored procedures for all the reasons given.  In the short-run, it might make things go a bit slower, but in the long-run, you will have greater returns on your time invested.

10 Comments

  • The problem is coming with Yukon. The next generation databse make thinks more complicated, because Microsoft integrated CLR and T-SQL.



    So the choice between dynamic procedures and sPs will be though.



    You will have to decide if you want a non proprietary database.



    I use both approach in my projects, but I'd rather prefer to mody an SP than playing with my code to modify a statement.

  • You have a talent for taking things out of context, Frans.



    I am an expert on most of the points that I brought up, concerning development and maintenance aspects. That was and is what the bulk of the blog is about. What I said I'm not an expert on is the internal workings of SQL Server, and in that case, I deferred to those whom I consider to be experts. Please do try not to take things out of context.



    As far as I can tell, the only valid points you've made are that dynamic T-SQL statements are cached and that Yukon could change the situation in regards to DBAs.



    I don't think anyone is debating that dynamic T-SQL is cached or that while cached it can be as performing as stored procedures, but that's not the whole story, and it's certainly not speaking to the ancillary points that have been made as to an overall recommendation regarding data access procedures.



    Bringing up Yukon is a valid question, but it hardly speaks to the issues at hand. Let's not forget that Yukon is not doing away with T-SQL--it is *adding* support for the .NET CLR. So it doesn't necessarily change the situation at all because we can still use the exact same setup as we have now with T-SQL stored procs, but it does offer other options, such as writing procedures in, say, C# or VB.NET.



    Naturally, this means that some developers will do so, and this may limit their DBA's ability to help them. On the other hand, I would expect a good DBA would learn enough of the syntax to be able to understand, optimize, and possibly write their own stored procedures.



    But that is neither here nor there since we are speculating about the future. This point remains the same, which is that using stored procedures will empower developers with good DBAs to rely on their expertise so that we can focus on other aspects of application architecture.



    And please don't pretend that I'm guilty of ad hominems when you throw them about like candy. My points are based on reason, experience, and facts. If you cannot see that, I apologize for not communicating more effectively. I can only hope that other, perhaps less-personally-involved people will better understand my blog.



    Note that I fixed the link w/ your name and removed the comment about your blog replies. Both were honest mistakes.

  • Thanks Paul, couldn't have said it better.



    J. Ambrose: thanks for fixing the name and removing the comment comment :)



    I've said enough about this topic I think. I've seen both sides of the story for a long time and I know that what I've said is true, so are others, so I'll leave it at that. My arguments are on my blog, and I don't think I have to recycle them time after time after time ... :)

  • Paul,



    If you are right about the context of Frans' post, and it does seem likely that you are, then Frans was very out of line in criticizing Rob, as it is my impression that Rob was contrasting stored procedures primarily to ad-hoc, in-line SQL, which I think can be inferred from Rob's comments. In any case, Rob did not address OR mapping specifically, as far as I can tell, and Frans failed to make it clear, in my opinion, that he was only advocating ad-hoc T-SQL stashed away in an OR mapper. If that is the case, then it does mitigate some of the concern about ad-hoc T-SQL.



    It does, OTOH, open up a new problem, which is the possible dependence on a black-box, third-party solution. There is some validity to general concerns about any third-party software relating to extensibility, learning curve, and on-going support. I would be very wary, myself, of committing important production data access code to a third-party product that has no inherent knowledge of either my application or my database and that could be the cause of problem that I can't fix because I can't get the support I need and that is more difficult, if not impossible, for me or my DBAs to tweak.



    O/R mapping is certainly a better solution than inline T-SQL, but the choice of whether or not to use it would depend greatly on the project, the team, the third-party product, and the company offering the product.



    One thing is clear, using stored procedures is *always* a good idea. That is not the case for O/R mappers. So I think Rob is justified in recommending them for general use, and particularly when contrasted to in-line, which is what, I think, he was addressing.



  • I can mostly agree with that. The problem of third party support is a big one, as it is with other controls and tools, but it is becoming less of an issue. There are now already several very excellent O/R mappers out there, and at least some that I'm familiar with have excellent support and typically provide you the support. It does very much however depend on the project and the team too -- some businesses can benefit greatly from automatically supporting multiple databases with a good O/R mapper. Its always best to consider all your options, but most of the Microsoft world doesn't even know about this option (at least not in a fair light), unlike a large part of the Java world that takes this option for granted. I cannot agree that stored procedures is "always" the best idea, although its hard to argue that they are almost always an "acceptable" solution.



    I get headaches thinking of all the broken builds, let alone bugs, I've seen when stored procedures and their wrappers get out of synch, let alone all of the time that is spent making sure that doesn't happen. I also get headaches thinking of all the times that I've had to deal with performance and scalability problems, on many different projects at many different employers and clients, due to developers not closing connections or datareaders, or otherwise writing bad data-access code. I also get headaches thinking of all the potentially lost business I've seen over the years due to silly arguments over MS Sql vs. Oracle vs. Sybase, or even wanting Access in some cases. I'm very much excited about O/R mappers because they have the potential to solve all of these issues, although I'd be a fool if I didn't admit that there are other issues that they raise in the process. By the way, I think its shameful that MS will release ObjectSpaces only supporting MS Sql initially.

  • You misquoted me, Paul. I said always a "good" idea. I can agree they're not always the best idea.

  • "One thing is clear, using stored procedures is *always* a good idea."

    No. I have a client with a database with a LOT of tables and thousands of stored procedures and 20+ applications running on top of that stored proc set. It's completely unmaintainable. If something has to change for app X, you HAVE TO create a new proc, because otherwise it will break other apps. It's a documentation nightmare.



    This would not be present if they had formulated the queries in the programs themselves for example using an O/R mapper. Then, if a query has to change for program X, you change it in program X, and everything is fine.



    As Paul said, I was a long time Stored proc advocate, but I realized they're definitely not the answer to modern database questions. Though I still use them once in a while. The last one I wrote was a sequence emulator proc, which was simpler written as a proc than a lot of code in C#. However the situations I use them are rare nowadays, because normal DML is easier formulated by C# constructs than with a stored proc.

  • One thing that I think is particularly telling about a vendor's level of support and commitment is if the vendor has public forums on their website to discuss any problems, features, tips, etc. with the products. If I don't see this level of support -- then I usually move on to someone else.



    So, Frans, where are the public forums on your website? I notice that the Thomas has them for EntityBroker.

  • I have forums for customers, if you want to take a look, mail me.

  • Forums don't seem publicly accessible - we need link directly to your forums without registering. Thanks.

Comments have been disabled for this content.