Stored Procedures vs. ???

Commenting on my blog entry about generating data layers Jesse wrote, "I don't think entity broker supports stored procs, but they suck anyway" and Frans agreed, "In the next version of LLBLGen I will not generate stored procedures either, just code which at runtime generates an optimized query especially taylored for the situation." This is the first I've read about the move away from stored procedures. I recognize the problems with handcoded SQL statements in the code, but I thought there was agreement that stored procs solved many of these problems. Can someone explain the reasoning behind these statements?

This is my first blog and I'm not sure when I should be asking in the blog and when I should be posting on a forum. Opinions appreciated.

[What I'm reading: Applied Microsoft .NET Framework Programming I read this for the first time last summer and it remains the best .NET book I've read. This is the definitive look into the .NET framework. You'll need other books on Web Forms, Windows Forms and Web Services, but understanding this one will change the way you program. Wow!]

Published 13 May 2003 01:56 PM by Ted_Graham
Filed under: ,

Comments

# TrackBack said on 13 May, 2003 03:56 PM
Andres Aguiar's Weblog
# TrackBack said on 13 May, 2003 03:56 PM
Frans Bouma's blog
# Robert McLaws said on 13 May, 2003 06:54 PM
That, in my opinion, is an unintelligent response from Jesse, because the point of SPROCS is that they are precompiled, and they allow you to lock down your database by allowing access only to the sprocs and not to direct selects on the tables. Just think if you opened up DELETE permissions on your tables just cause you don't like SPROCs. REAL smart. Not only will your website run faster because the data will be returned faster, but you'll stop SQL Injection Attacks and you won't be leaving the key to your database under the mat.
# Paul Wilson said on 13 May, 2003 07:57 PM
The following thread has some discussion on these topics, although its long and twisted:
http://asp.net/Forums/ShowPost.aspx?PostID=35928

I think the main point about performance was that all modern databases compile and cache sql queries as well as sprocs, so as long as the same sql queries are being ran . . .

I'm not qualified to comment on the rest, as I am still a little "scared" due to my background using only sprocs also. But the O/R mapper concept is starting to make me think, especially given that MS is also getting in this space now and since its quite common in the non-MS world already. I've been trying out EntityBroker and it really is delivering on its promise so far -- which to me is less code to generate/maintain, working with real objects and typed collections, excellent query/search/sort capabilities, and automatic cross database support! I'm still learning, but I'm definitely getting very intrigued by all the non-MS concepts, including NUnit and NAnt, that are now coming to MS due to the .NET platform.
# HumanCompiler said on 13 May, 2003 11:11 PM
Just a guess here, but my guess is that since Yukon is the answer to why SP's will be used less ;)
# HumanCompiler said on 13 May, 2003 11:14 PM
ack, sorry, i really need to go to sleep...that last comment was horrible...what i meant is that I think the new features of Yukon might be why everyone's talking about using SP's less
# Frans Bouma said on 14 May, 2003 03:27 AM
If you read the asp.net forum thread, you'll notice I defend the usage of Stored Procedures. I wrote a testlayer with stored procedures and one with dynamic generated queries. When tailored to their task (i.e.: the stored procedure didn't have optional parameters (see my tip in a recent blog) and contained the same query as the dynamic generated query) the stored procedures and the dynamic query are both as fast, perhaps the stored procedure one is a little faster, but that's very very minor. This surprised me as well, because I really thought stored procs would have the edge over dynamic parameterised queries. I'll write a little bloggy about this today. :)
# Frans Bouma said on 14 May, 2003 03:32 AM
Robert: if you use parameterised dynamic queries, you are not open for sql-injection attacks, since you just pass on parameters like you do with a stored proc call. The argument you have about security in the database is indeed the only reason why stored procedures can be a favorite, however if I can execute such a stored procedure you've locked down via tool the security is moot anyway, which means: security should be enrolled everywhere in the call-chain if you allow a small set of users access a particular piece of code.

On the DOTNET list there was a debate the other day about windows-integrated security vs sqlserver security and it turned out that windows-integrated security wasn't even possible with ASP.NET and 2 boxes, without doing multiple administration of users (or storing the userid/password in plain text somewhere), which results in sqlserver security anyway, which results in probably 1 connection string used for the complete application, which means you have to implement security yourself IN the application, which results in that stored proc security is very nice, but will not be used since there is only one user connecting to the database anyway.
# Jesse Ezell said on 14 May, 2003 03:33 PM
Take a look at it this way. If you needed to serialize your data as XML, which would you prefer: a hard coded, precompiled method that contained all your xml serialization code like this:

void Serialize(Customer c, StreamWriter output)
{
output.Write("<customer id='"+id+"'>");
output.Write("<name>"+name+"</name>");
output.Write("<email>"+email+"</email>");
output.Write("<phone>"+phone+"</phone>");
output.Write("<fax>"+fax+"</fax>");
output.Write("</customer>");
}

(This, of course, is actually a very simlified version, because you would really want to use an Xml based writer or construct an Xml DOM tree instead, so that the InnerXml values where properly encoded).

or would a nice method that did all the work for you (a la .NET's XmlSerializer)?

As most developers will agree, the MS approach here with attributes and reflection is vastly superior and saves a hell of a lot of time. I would argue that the dynamic SQL approach is superior for all the same reasons.

Leave a Comment

(required) 
(required) 
(optional)
(required)