Jeremy D. Miller -- The Shade Tree Developer

Sponsors

The Lounge

Wicked Cool Jobs

Syndication

Recent Posts

Tags

View more

News

Blogs I Read

Archives

Advertisement

Jeremy D. Miller -- The Shade Tree Developer » Why I do not use Stored Procedures
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Why I do not use Stored Procedures

I promised myself that I wouldn't ever make another post about stored procedures, but Eric's post on sproc's hit a few of my hot buttons on the subject.  Four years ago the pre-Agile, VB6/ASP coding me would have fervently agreed with Eric's pro-sproc stance and I wrote PL/SQL by the bushel full, but today my answer to the sproc question is a firm "no thank you" or at least a "guilty until proven innocent."  Besides, I'm an Oracle guy in a Sql Server shop and I despise T-SQL.

First, some common ground.  I think we can all agree that adhoc SQL in code ala ASP circa 1998 is an abomination.  I don't have that much trouble with using sproc's for CRUD, but then again I think a domain model approach backed by O/R mapping is more efficient in terms of the all important developer time and the domain model approach leads to better code.  The newer parts of our applications can accommodate database changes by simply changing the NHibernate mappings.  No sproc's necessary.

So to Eric's post, first this:

So when I see people in the TDD crowd slamming stored procedures and views it greatly confuses me.

That's an easy answer Eric, stored procedures make TDD a slower, less productive process.  Business logic in stored procedures is more work to test than the corresponding logic in a domain model class.  Referential integrity will often force you to setup a lot of other data just to be able to insert the data you need for a test (unless you're working in a legacy database like ours without any foreign keys;)).  Stored procedures are inherently procedural in nature, and hence harder to create isolated tests and prone to code duplication (a duplicated "where" clause is duplicated code and duplication is bad).  Another consideration, and this matters a great deal in a sizable application, is that any automated test that hits the database is slower than a test that runs inside of the AppDomain.  Slow tests lead to longer feedback cycles.  Trust me on this one, a team will be much slower with a 20 minute CI build versus a 5 minute build time.

I'll make an admission.  I bet I haven't written a full 100 lines of direct ADO.Net manipulation code in the last six months, yet we've rolled out a multitude of new functionality in that time frame.  I also write very little non-trivial SQL these days.  Between a little OO query engine we've developed and NHibernate, there's simply no need to spend time in the muck of data access.  Like Jeff Atwood said, stored procedures are the assembly language of the database.  I'll use sproc's when I have to have a performance gain, but until then they're nothing but premature optimization.

I think the gap in thinking about sproc's is about how you see the role of the database in an application -- do you talk about the database in passing as a persistence mechanism or is the .Net code merely a way to get data back and forth from the UI and database?.  The applications I build are primarily about business rules, not reporting.  The database to me is simply a persistence mechanism for our domain objects and messages en route.  Maintainability, which is almost synonymous with testability, is king.  A large, comprehensive body of automated tests at both the acceptance and unit test level are, in my opinion, the best way to accommodate changes to the code.

Again from Eric's post, and this is where I got hot under the collar (it's not directed at you Eric) -

Your DBA team can work completely independently of your programming team and do pretty much whatever they want and your application doesn't care!

Expletive.  NO, NO, NO!!!!!  The DBA should most certainly NOT work independently of the programming team.  Stored procedures are code, and potentially destructive code at that.  If you change a stored procedure you *MUST* integrate and test the stored procedure against the application before it gets anywhere near production.  I've been burned by this one too many times.  The stored procedure code absolutely has to be built and versioned within your Continuous Integration build.  I'm sick of bugs caused by a sproc getting out of synch with the code.  The idea of a DBA, or a maintenance developer, putting a different version of a sproc directly into the database is a bad, bad, dangerous practice. 

The extra configuration management burden is one of the reasons most Agile teams stay away from stored procedures.  If I'm relying on NHibernate mappings or parameterized SQL in the code that SQL is going to be built and versioned with the C# code automatically.  My risk of having mismatched sproc and C# code is greatly minimized.  Our C# assemblies are signed with the CruiseControl.Net build number and it's simple to spot mismatches  The stored procedures are, err, well, uh, I don't know if that's the same version that the code was tested against to be quite honest :(

The absolute worst thing you can do, and it's horrifyingly common in the Microsoft development world, is to split related functionality between sproc's and middle tier code.  Grrrrrrrr.  You just make the code brittle and you increase the intellectual overhead of understanding a system.

 

 

Posted Thu, May 25 2006 2:45 PM by Jeremy D. Miller
Filed under: ,

[Advertisement]

Comments

David Scheidt wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 4:19 PM
Hi Jeremy,
I like the article / thoughts.  Quick question, though... How does it scale?  Are there any example projects that use this approach?  I'd love to see a working web site or code to understand how this can be implemented.

Thanks!
- David Scheidt
Sachin Rao wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 4:28 PM
Jeremy

One of the things I have heard in favour of stored procs (as compared to parameterized sql) is performance.

Another point usually made is reporting (where you might have to use stored procs in spite of having an o/r mapper).

What's your take on that?

Sachin
Jaosn wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 5:02 PM
A very interesting post. I have been a highly vocal proponent of SPs (I too have a PL/SQL backgorund) but as I embrace TDD I am starting to change my mind.

Your observations about DBAs are all true true but I think the essence of that problem is inproper utilization of that resource. If the DBA (or any other person) is not an inegrated member of the team then the chances whatever they produce doing what it's supposed to is slim (at best). In my experience most developers are better at SQL cause they have more experience (we don't spend all of our time worrying about backups etc.!) Where DBA cvan be invaluable is when you have to do something new and leveraging a feture of the RDBMS (I don't really buy into database independence as a concept) can be a huge time saver or performance boon.

I do have questions about performance and scalability (especially with SQL Server) but most applications do not need to be perfect in either respect. So next timme perhaps I will try to use SPs only for the slow bits and see where this gets me.
Nick Parker wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 5:10 PM
Jeremy,

Care to share more about your OO query engine and how it integrates with NHibernate?

Nick Parker
Jay R. Wren wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 5:12 PM
What about security?  With the stored proc approach you can handle security with different DB user accounts for different queries and allow your users to access the database with the various accounts through your application.

With NHibernate you have to implement this entire security model yourself in your application.  Maybe that is OK, but it does seem like a lot of work.  How do you handle it?
Eric Wise wrote In Response To No Stored Procedures
on Thu, May 25 2006 6:33 PM
You'll have to catch up on my original post and Jeremy's informative post before delving into this one....
Sam Smoot wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 8:26 PM
What about security? Giving UPDATE/INSERT Access doesn't mean you give the user the SA account with no password and call it a day.

If you have a robust fault-tolerance solution (including backups), then what's the danger really anyways? Or phrased another way, when was the last time you heard about a system intrusion whose goal was to INSERT/UPDATE a bunch of random records in a few tables. I've _never_ heard of it frankly. And as hard as I try, I'm not naive enough to believe the only security fault (or the most dangerous, or the simplest) in my application would be an Object Relational Mapper vetted by (at least) thousands of developers in tens of thousands of applications.

The "sprocs are an interface" argument just doesn't jive with the real world IME, but to each his/her own I suppose. Mostly I see sproc proponents claim "sprocs are great, I can change my tables!", but how often does it actually happen? During development sproc maintenance is just another layer to slow you down. During production operation, most developers would much rather not touch existing sprocs IME, preferring a little CYA and copy&paste action instead. Which only leads to an even bigger maintenance problem.
Eric Wise wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 8:48 PM
I've witnessed insert/update attacks to put vulgarities into forum sites.  I've seen them used to hack a record into an order system so that the hacker could get access to legitimate product keys.

What about security?  How about not using an ORM with access to a database table with billing information?  Now anyone that hijacks your .dll can select * from your credit card table.  I tend to keep tables with billing and other sensitive personal information on lock-down.
John Wood wrote Another Stored Procedures Argument
on Thu, May 25 2006 9:00 PM
It's easy to think there are two types of programmers in this world - those who are for using stored...
Sam Smoot wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 11:18 PM
Funny... I would attribute defacing attacks to application flaws. I'm not sure I've ever seen the claim that sprocs would have prevented one before... but your experience is your experience.

The billing argument is more than a bit silly. Sprocs are obviously not the only solution. A view does just as well, but we're still not out of options yet. Put your cards in a different table (preferrably behind a different service altogether, on a different system altogether if you _must_ keep them at all).

Even if you decided to go with sprocs in this (oft-repeated as the last bastion of sproc supporters everywhere) scenario, nobody's going to make fun of your application. It's _one simple case_. Don't get so tied up in the purity of anything that some edge case keeps you from delivering...

WRT: "hijacks your .dll": If someone is able to start executing arbitrary code in-process on your application server, I wouldn't take much solace in having sprocs as a line of defense.
karl wrote re: Why I do not use Stored Procedures
on Thu, May 25 2006 11:52 PM
Relational databases are too good at what they do to be treated as nothing more than persistence mechanism.  I've seen catastrophic failures as a result of this belief, and not a single success. Don't get me wrong, I'm 100% sure that's only because of my limited exposure, but it's enough to tell me that there's a learning curve and the stakes are offly high.

I can't be the only one who likes to map relational data to objects manually (codesmith helps)?  Sure it's a lot more grunt work, but it just seems simpler.
Jeff Gonzalez wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 12:54 AM
Sorry, I forgot 1 more thing I wanted to say.

I think that Eric is right when he says that calling a stored procedure is a contract or an interface.  It is like consuming a third party library, which has an established API.   A stored procedure has a stored execution plan and a very specific list of parameters it needs to execute that plan.

How is that not a contract or interface?  

Any talk favoring stored procedures for performance or security at this point is FUD.  To that I say QFT.  Parameterized queries defend just as much injection as a stored procedure.   While it does allow you to operate without priveledges for a table, views would allow you to accomplish the same thing in most cases.
David Kemp wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 4:03 AM
"The database to me is simply a persistence mechanism for our domain objects and messages."

That's where I'm at too. I think that until someone has used a tool like NHibernate, it's very difficult to see the database as somewhere where you store <b>data</b>. Your code turns the data into something meaningful. Of course, I still use all the tools in the database to make sure that the data isn't corrupt, but unless I'm automatically generating sprocs and code to call them, they seem like a bit hassle.

Eric: If this is a real concern, and something that you can't enforce otherwise, then you can always use process impersonation and restrict access to methods/classes using security policy.
william dieterich wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 8:33 AM
Speed wise...

This is about CRUD based sprocs, if you have to do a bunch of looping or multiple selects to get a single piece of data then sproc is the way to go.

dynamic code is as fast or faster then sprocs for CRUD.

1) people claim that sprocs are compiled and stored in the database differently then dynamic SQL; this is completly false but still found in alot of places.  The use of parameterized dynamicA SQL removes any speed boost that sprocs may have from query reuse.

2) And what most people ignore is all thoses COALESCE statements and OR statements in sprocs are slow.  As has been shown in multiple cases, and is easy to duplicate, even a sproc with just 3-5 optional parameters you will get a 15% to 20% slowdown using COALESCE or 30% to 40% using OR statements compared to dynamic SQL.
Snoop Blogg - The Bloggfather wrote The Dynamic SQL vs. Stored Procedures is the most common GEEK battle of the century...
on Fri, May 26 2006 9:37 AM
Frans Bouma summarizes a war that has been brewing since the popularity of ORMapping and Code Generation....
jace wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 11:22 AM
@ William Dieterich:

Sorry, but your point number 1 is dead wrong, simply look into the documentation:

"When executing statements, calling a stored procedure on the data source (instead of directly executing or preparing a statement in the client application) can provide:

Higher performance
SQL statements are parsed and compiled when procedures are created. This overhead is then saved when the procedures are executed.

Reduced network overhead
Executing a procedure instead of sending complex queries across the network can reduce network traffic. If an ODBC application uses the ODBC { CALL } syntax to execute a stored procedure, the ODBC driver makes additional optimizations that eliminate the need to convert parameter data.

Greater consistency
If an organization's rules are implemented in a central resource, such as a stored procedure, they can be coded, tested, and debugged once. Individual programmers can then use the tested stored procedures instead of developing their own implementations.

Greater accuracy
Because stored procedures are usually developed by experienced programmers, they tend to be more efficient and have fewer errors than code developed multiple times by programmers of varying skill levels.

Added functionality
Extended stored procedures can use C and C++ features not available in Transact-SQL statements."

from:

http://msdn2.microsoft.com/de-de/library/ms131440.aspx

Pretty clear isn't it?
johnwood wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 11:32 AM
>> Because stored procedures are usually developed by experienced programmers... << That's quite funny actually... how the heck do they know that? Stored procs are actually usually developed by inexperienced programmers - experienced DBAs perhaps, but not programmers. At least not that I've seen.
Jacob Eggleston wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 2:38 PM
@jace

The MSDN help item you linked to is a leftover from the days of SQL Server 6.5. A dead giveaway is the reference to ODBC - when was the last time you used that? Every single point made on that page is either out of date or, as in the case of "Greater consistency/accuracy" were at best questionable when that was originally written. The performance argument was a valid one on 6.5, but is not any more. Starting with version 7 MS made huge changes to the way sprocs and queries were compiled and optimized. It has been proven by many people since then that, when used correctly, there is rarely any significant difference in performance between stored procs and the parameterized queries that OR mappers like NHibernate use. I've done my own tests and come to the same conclusions. Besides even if there _IS_ a slight difference in performance, using sprocs for this reason is committing one of the most common (and most bone-headed) mistakes that developers can make: premature optimization. Few things get me more worked up than developers who will gladly waste thousands of dollars in development and maintenance of sprocs to milk that extra 6ms out of a round trip to the server that takes 1600ms before they've even done any testing to identify where the bottleneck really is!

The security argument that sproc advocates trumpet is also bogus in all but a handfull of edge cases. Anyone making this claim doesn't fully understand SQL Server's security model or application security in general - or maybe they've just never really tried to do it any other way. Any security hole that allows a user (malicious or not) to manipulate your data willy-nilly could be exploited just as easilly with sprocs as without them.

I've rarely heard an OR mapping proponent argue that sprocs don't have their uses, only that you should use the right tool for every task (and in my book anyone arguing that you should never use sprocs is no smarter than one who argues you should always use them). Sometimes sprocs are the best tool for a task. Reporting is often one of those cases, while CRUD functionality rarely is.

One interesting observation is that I frequently see developers convert from a pro-sprocs-for-everything position to pro-OR-mapping, but seldom, if ever, do I see the reverse. Why is that?
Jeremy D. Miller -- The Shade Tree Developer wrote Grab bag of follow up's for data access, persistence, query engines, and o/r mapping
on Fri, May 26 2006 3:05 PM
Just catching up on questions and comments from the Why I do not use Stored Procedures post yesterday....
Chad Myers wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 5:51 PM
Everyone: Please read Frans Bouma (of LLBLGen and flaming-every-blogs-comments-section fame) post on this subject:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

He pretty much establishes that all the performance and security arguments for sprocs are bunk.

Also, there's a historical argument here: SPROCS were not *MEANT* to be your DAL. It's a trick in SQL 6.5 for performance that they grew into that role. Now that the main arguments have been yanked out from under them, can anyone seriously defend their use? The extra complexity, the code-in-many-places problem, the two-or-multiple language maintenance problem, the DBA's-doing-application-development problem, etc, etc.

It just seems silly to be using Sprocs today when you have things like NHibernate.

Sprocs have their place and they are very useful *WHEN YOU NEED TO USE THEM FOR THEIR INTENDED PURPOSES*.
Keith Gaughan wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 6:13 PM
There's another argument against them that people have been omitting, that they, like the database schema itself, are a pain to keep under version control.
Jeremy D. Miller wrote re: Why I do not use Stored Procedures
on Fri, May 26 2006 6:13 PM
All, that's enough for now.  Comments closed, and let's move on to something more interesting.
Craig's Blog wrote Separation of Concerns…in our arguments
on Sat, May 27 2006 1:13 PM
I recently have been following two arguments on Codebetter.com.&amp;nbsp; One is the classic stored procedure...
Craig's Blog wrote Separation of Concerns…in our arguments
on Sat, May 27 2006 1:16 PM
I recently have been following two arguments on Codebetter.com.&amp;nbsp; One is the classic stored procedure...
RodMan's Corner wrote The Reality of Stored Procedures
on Wed, Jun 21 2006 12:55 PM
&amp;nbsp;
OK I’ve decided to jump into this discussion of using stored procedures for data access and manipulation....
Gary Farris - Thoughts On Technology and Everything Else wrote Business Logic In Your Stored Procedure
on Wed, Jul 19 2006 9:58 PM
This argument/problem has popped up in my world again.&amp;nbsp; I find it is easy for someone familiar with...
Scott’s Blog » Stored Procedures vs. Ad-hoc SQL Redux wrote Scott&#8217;s Blog &raquo; Stored Procedures vs. Ad-hoc SQL Redux
on Tue, Aug 15 2006 3:07 PM
Kevin Jensen » The Dynamic SQL vs. Stored Procedures is the most common GEEK battle of the century… wrote Kevin Jensen &raquo; The Dynamic SQL vs. Stored Procedures is the most common GEEK battle of the century&#8230;
on Tue, Sep 5 2006 7:32 PM
Ayende @ Blog wrote Advanced Uses For OR/M
on Sat, Jan 13 2007 2:22 PM

Advanced Uses For OR/M

Devlicio.us