State Street Gang

This isn't my first programming blog.  I've had a couple others; one on that horrible Blogspot POS and another that was essentially beta testing some blog software for Pixy over at mu.nu.  Both sucked; but other than that they had only one thing in common:  The Dreaded Stored Procedure Post.

A programming blog just isn't complete without a post about stored procedures.  Either you hate them, love them, or are indifferent about them, it doesn't matter.  You're obliged by law to comment upon them.  So here I weigh in with my official opinion on SP's.

I prefer not to use them.

Strong, controversial words, I know.  I thought about saying something outrageous like "get butthurt, SP lovers!!1" but I don't care enough about them.  In fact, I think as little about them as humanly possible.

So what, I'm lazy.  Being lazy means that I want to do things right the first time and do them as efficiently as possible so that I can be done with it.  Spending time thinking about SP's is just a waste.  There was a time when they ruled the database world, but they're just not that important anymore.  And, in my opinion, they are harder to create, version, maintain, are more prone to bugs, and generally all-around suck.  Okay, that ended up being controversial after all.

SP lovers (or, as I like to refer to them, SP Nazis or SPazzes), have a yellowed, moldy-around-the-edges list of reasons for their SP love that they keep in their wallets for times like this.  I've peeked at this list and haven't seen anything that changes my mind. 

Stored Procedures are faster than ad-hoc queries

SP's have little to no speed advantage over ad-hoc queries anymore.  An ad hoc query that uses indexes properly, prefers sargeable where clauses, and generally doesn't suck will beat your average SP speed-wise every day.  SPazzes claim that SP's are "compiled" and their execution plans are cached, whereas ad-hoc queries must have their execution plans generated upon each request.  Which is true.  If you haven't upgraded to SQL Server 2000 or later.  Unless your ad-hoc queries are always significantly different from each other, their execution plans are cached right along side those of the SP's.

Yes, if you need to squeeze every last millisecond out of your execution time you will probably have to use an SP.  But remember what Knuth said about premature optimization?  Are you smarter than Knuth?  No?  Then don't immediately choose SP's based on their supposed speed benefits.

Stored Procedures are safe against SQL injection attacks; ad-hoc queries are not

Apart from this being entirely false to begin with, you can (and of course should always) use parameters in ad-hoc queries.  In fact, ad-hoc queries use the exact same SQL parameter pattern that SP's use.  You include @parameters in your ad-hoc queries, and add the matching SqlParameter objects to your SqlCommand object, just as if you were calling a stored procedure.  Yes, you can screw up ad-hoc queries by not using parameters and simply + concatenate + in + your user input, but you can do that with stored procedure calls as well.

Editing SP's is a breeze with Query Analyzer

So what?  I can't edit ad-hoc queries in QA?  Are you high?  Ctrl-C, Ctrl-V, my pothead friend.

Ad-hoc queries are a nightmare to maintain, as they are spread all over your code

As for having a single location for maintaining queries, ad-hoc queries actually beat SP's here hands down.  No doubt you can have an ignorant programmer who places queries in the UI, in this or that object, and everywhere in between.  Or you might have someone who has a hint of a clue that places all queries within a single object, which is then referenced by the database layer.  The fact is that if you (properly) keep a single object that "generates" all your queries, you have your single location where you go to maintain them.  Whenever your code changes, your queries are right there and can be easily changed to match.  You don't have to use some separate mechanism to examine installed stored procedures and upgrade them if necessary (no built in way to do this in SQL server, anyhow, other than overwriting them blindly).  Versioning is easy for code; its very hard (or expensive) for SP's in a database.  Upgrading ad-hoc queries is a simple task of dropping in a new DLL.  Updating SP's requires you to connect to the database, check your versions (how would you even do that?), drop and create, yadda yadda. 

Also, with ad-hoc queries, you can break queries into parts that are assembled on demand, removing the need to copy-paste the same SQL into different SP's.  For example, if you have a security check that runs prior to each and every query, you can write it once and, on startup, concatenate it with all other queries.  Edit once, change everywhere.  You can also use this trick to easily combine several database calls into a single ad-hoc query that returns multiple record sets. 

You can do other tricks as well using preprocessor directives like #if DEBUG.  For instance, you can keep your queries nice and formatted in your source code and use a regex replace to strip it out when in a release build (the regex "(/\*[^\*/]*\*/)|(\r\n\s*)|\t|\s{2,}" is a good starting point for this).  That keeps your release transmissions slim and quick but your debug queries fat and juicy.  Just copy the query right out of Profiler and drop it into Query Analyzer to debug.  Using this trick you can also include additional queries that help debug more complex database calls.

That's why I use ad-hoc's.  I'm not saying you're bad for being a SPaz, nor am I judging your worth as a programmer or as a human being.  I'm just saying I don't want to maintain your crappy SP code is all.  Can't we all just live separately behind 10ft brick walls?

This entry was posted on Wednesday, April 2nd, 2008 at 12:28 pm and is filed under Nuts, SQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.