KISS: Avoid Stored Procedures and save money!

I'm not a fan of stored procedures (SPs) for anything except complex data processing generally.

Having just read Writing and Calling Stored Procedures, I felt like explaining my point of view on this stuff. It's sunday after all.

Firstly, I'm developer who considers sql to be mandatory for the developer, rather than having dba's doing all the sql stuff.

That's just an approach which creates an unnecessary bottleneck in the development cycle. I have often seen bad results when developers don't understand the database and it's concepts, or maybe the DBA's didn't understand (or have time to) the application either.

DBA's are there to help with the complex stuff. Use them wisely (and cost effectively!)

This example of using SP's to ends up adding yet another layer to your app and is more complex & longer that the code it intends to replace. Suddenly it's also more work to say add a column to the insert as you have yet another chunk of code to update.

Using stored procedures means you can't easily work with multiple versions of code against the same database (as your logic in SP's is only once per db, not back in the app layer). You lose the ability to easily version your data access layer and hence things tend to stagnate.

If you database is 10 GB, do you really want to have to be running a different instance for each build with changes to the db layer. Doesn't that sounds like a rather expensive approach? It's a recession after all.

Tuning SQL for Coldfusion app with stored procedures, is also more complex in many ways! Personally I love how easy it is to tweak the sql in normal bound CFQUERIES. Copy from debug, tweak in something like TOAD and then update the CF code, easy!

What I love the most about Coldfusion is how clean looking (aside from the verbose cfqueryparam) the database support is when compared to other programming langauges.

Alas the ugly embedded sql in strings syntax of some other languages, almost necessitates SP's

Plus you can't port the app to another (perhaps free database) without rewriting the entire sql layer! Expensive huh?

SQL Server people seem to love (and needs) SP's more than with say Oracle. With Oracle you get the same result and performance just by using simple bound sql. I think the horrible muck that was classic ASP database access really started this trend.

A lot of the common uses of SP's I have seen is what I consider Premature Optimisation

Most of the performance myths about SP's date back to the old days of 100Mhz servers. Servers are cheaper than developers right? Not that I'm advocating something which is going to make your app run slower either (generally speaking).

My Advice, avoid SP's (until there's a clear issue which they can resolve) and enjoy the nice flexibility of bound sql and faster development cycles.