I’ve written on the never-ending stored procs vs. ad-hoc SQL debate before, but there are quite a few more perspectives on both sides of it than I referred to in my previous post. Here are a few of them:
- Frans Bouma (ad-hoc SQL camp) compiles a summary post with links to various arguments here.
- Jeremy D. Miller (ad-hoc SQL camp) rejects stored procedures because they make test-driven development (TDD) slower. This is an argument I hadn’t thought of. The apps I build tend to be database-driven websites. Testing logic that makes database calls at any point does slow things down quite a bit. Miller especially dislikes the idea of putting business logic into a stored procedure (defined as anything other than a CRUD operation).
- Jeff Atwood (ad-hoc SQL camp) argues against stored procedures because he prefers parameterized SQL and a single development environment.
- Microsoft’s official guidance is to use stored procedures instead of direct SQL.
- Rob Howard writes in favor of stored procedures. Community Server 2.1 makes significant use of them.
These arguments make me a lot more curious about object-relational mapping and code generation.
The Gentle framework does NOT use Stored Procedures, but NetTiers does. (Genereated by the code generation framework.)
I haven’t used Gentle in a high load environment, but the primary author is taking great pains (especially with version 2) to do the right amount of caching of data at the object layer for performance. (Every call it makes to the database uses SP_Execute too.) From a coder perspective I MUCH prefer NOT having stored procedures to worry about, and it makes refactoring and testing a LOT easier. Gentle includes ways to call SPs if you need a release valve, but I’ve used it on three different projects now and haven’t needed to touch it once.
Coming from an “everything must be a SP” attitude, it took me a while to give in and try out the new way. But once the Gentle pros convinced me I can do without them, I haven’t looked back.
Unless you are doing really heavy data queries ALL the time, my suggestion would be to use a framework, like Gentle, that doesn’t use SPs. Then, if certain areas of your application would benefit from some specialized SPs for performance reasons, use them where needed. But for your day to day CRUD procedures, they’re really overrated, imho.