For years now, I’ve been a fan and advocate of stored
procedures. I’ve used them in almost every system I’ve worked on. Recently I
even used them almost exclusively for a large system with a MySQL Cluster
back-end (which was an interest project in many respects). At some point, like
many others, I woke up and found out that it wasn’t cool to use stored
procedures anymore. So I became a closet-sproc-lover.
The last couple weeks, I’ve started doing some preliminary
work on two small projects. In both cases, I’ve found myself completely abandoning
stored procedures.
One of the systems is a reporting application, and like it
or not, I’m stuck doing very ugly dynamic SQL (like dynamic table/column names
and such). For such a case, C# and VB.NET are infinitely better. I mean string
formatting, concatenation and manipulation is just easier and far cleaner
(hence maintainable). Security is equally problematic in both cases. As for
performance, well, it’s a highly indexed and de-normalized system….I don’t have
enough data to test yet, but I’m positive I wouldn’t be able to measure any difference.
The other system though is a more typical web-app, and I
surprised myself when I started to write SELECT statements in-code. I was
annoyed because I had multiple stored procedures to pull one or more rows from
a Users table….say:
User_GetFromCredentials(@userName, @password)
User_GetFromId(@userId)
User_GetAllForCompany(@companyId)
And whenever I added data to my User class/Table definition
I’d need to hunt down all the places where I had the same select (but different
wheres) and update them. This was a problem I had faced before, and more or
less gotten around by doing some whacky thing with an XML data type, but it was
really frustrating. So for fun, I ended up trying something like:
private string _userSelect = “SELECT
UserId, UserName, FirstName, LastName, Email FROM Users”:
And I haven’t really looked back.
I found an old post by Jeff Atwood that actually sold me on
this entire inline-SQL (here for anyone interested). He’s right on all three points,
but most importantly that using a stored procedure only for performance IS a
micro-optimization. I’ve never EVER registered a significant performance gain
by moving to a stored procedure (you get significant performance gains by
properly indexing and modeling and not using datasets.)
Who knows how it’ll go. We’ll see. And who knows, maybe my
next step is O/R Mapper land.