Frans Bouma's blog

Generator.CreateCoolTool();

Syndication

News



    Visit LLBLGen Pro's website

    Follow me on Twitter

    Add to Technorati Favorites

About me

Fun stuff I created

My work

Yay! A new Stored Proc vs. Dyn. Sql battle!

You'd think that by now everyone would have said everything that you can say about Stored Procedures vs. Dynamic SQL, but apparently Eric Wise and Jeremy Miller disagree with that and have started another iteration of this Never Ending StoryTM.

As it's Friday and you're probably looking forward to the weekend and wonder what to do till it's time to go home, sit back, get a soda or two and read yourself through the afternoon with the following Stored Procedure vs. Dynamic SQL masterpieces of all time :

If you still have some time left before the end of the day salutes you, google is your friend, as this fine topic has been discussed for decades by now.

(Update: added link to Rob Howard's old post, I lost the link but it was easy to find. Thanks Rob for pointing that out )

Published Friday, May 26, 2006 10:03 AM by FransBouma

Filed under: , , , ,

Comments

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 4:22 AM

Hurrah, another pointless trolling war!

Plip

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 5:18 AM

It’s a conspiracy Frans, and you’ve dragged yourself into it, again!

p.gielens

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 5:33 AM

Heh :). Noo, I'm not going to reply with new text, trust me, I've had enough hate-mail related to this topic. ;). Though it's fun to see how others seem to find it necessary to re-hash this battle from the stone-age over and over again :D

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 6:45 AM

You must be tempted though, Frans...

I know I am.

Wim Hollebrandse

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 7:17 AM

Nah, I've said everything that's to be said from my part. I always point to the article on TheServerSide.NET I wrote (link in the post). It took me a day to write that lengthy text, and it includes what I think about the subject.

But as you can see in that discussion as well, it's a stupid discussion. People argue about a subject they don't want to understand another POV about, mostly because of reasons not explained in their reasoning.

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 7:42 AM

Don't worry Frans, by the end of the week I'm going to put a spam filter on for "stored procedure" and never let the term it into my blog ever again.

Jeremy Miller

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 9:08 AM

Hi Frans,

Did you try searching for the posts - if you had you would have found that I apparently have not removed those posts :)

http://weblogs.asp.net/rhoward/search.aspx?q=stored+procedures&p=1

I believe it's actually the first result on the search results.

I've backed off my original statement of dynamic SQL is all bad (as long as it's parameterized is ok), but I still will always still use and recommend SPROCS first.

Rob Howard

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 9:16 AM

I say pick the right tool for the right job. I think the discussion is too broad. If everyone wants to argue, narrow it down. Are you talking about simple crud operations or complex reporting type queries? Is this a web based application or desktop app or a web service? is it a prototype or mega production system? Is your priority code maintence or minimizing development time? or maybe its performance?  Are you using an ORM? What type of database server are you running?

A couple of notes:

On performance alot of times another server is a hell of alot cheaper than ten architects debating how to get an extra ounce of performance out of an application.

triggers seem like an easy way to implement a changelog pattern.

.net has declaritive security at the application level.

There are probably situations were running a query on the database would possibly return a fater result. sum(x) comes to mind, although you could put that in dynamic sql. But, compared to return all objects and summming on a field the database solution is faster. Again, this all comes down to what are you trying to do. What runs faster at one time does always scale well.  

Those are my 2 cents.

Bob

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 9:17 AM

Rob, no I didn't try a search, I clicked on the link in my original post. I'll update the post, thanks! :)

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 10:05 AM

And all those links are just from .NET + SQL Server users. Just think of all the flamewars on the Java ERB + Oracle side of things.

Scott

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 10:20 AM

A *big* YAWN!!

Seriously, I thought this topic was old.

Sahil Malik

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 10:31 AM

Scott: indeed... :) Also a killer: mentioning in the newsgroup comp.databases that stored procedures really suck and shouldn't be used :)

Sahil: of course! that's the fun part: people are *still* bickering about this topic as if it's going to change anything :)

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 11:29 AM

Sounds like you're out of date, that documentation is quite old, have a look here, and please note the words "precompiled" and "SQL statements are parsed and compiled when procedures are created", this is sql server 2005 documentation:

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

"Running Stored Procedures  

A stored procedure is an executable object stored in a database. SQL Server supports:

Stored procedures:
One or more SQL statements precompiled into a single executable procedure.

Extended stored procedures:
C or C++ dynamic-link libraries (DLL) written to the SQL Server Open Data Services API for extended stored procedures. The Open Data Services API extends the capabilities of stored procedures to include C or C++ code.

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."

jace

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 11:53 AM

The 'compiled' in that documentation snippet is misleading. If you look up 'Query Performance' in the SQLServer 2005 documentation (index -> Execution plans -> Query performance), you'll see:

"Before a Transact-SQL query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement begins execution on an instance of Microsoft SQL Server, it is compiled into a query plan by the SQL Server Database Engine query optimizer. Then the plan is executed to produce a result-set. The compiled query plans are stored into a part of SQL Server memory that is called the plan cache. Query performance can be analyzed to improve it by viewing query execution plans or by manipulating the way cached query plans are used by applications. The topics in this section contain information about how you can view and manipulate SQL Server Database Engine query plans to improve query performance."

I.o.w.: what counts is the execution plan. THAT's compiled at runtime, not when the proc is saved.

Of course Microsoft wants everyone to use stored procedures: that way the app is tied to T-SQL and thus to SQLServer.

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 12:04 PM

Sorry, but it isn't misleading at all.

"One or more SQL statements precompiled into a single executable procedure."

This does NOT imply a compiled and stored execution plan, how would that make sense? It says "procedure", not plan.

What if I alter the indexes after I create the stored procedure? I could then wind up with a sub-optimal execution plan which had been compiled with the procedure.

Also, the execution plan is NOT the only thing that counts, compiling the query into an executable object which can run against an SQL Server instance ALSO generates overhead, which is saved by using a "Stored" procedure, which leads to guess what, "better performance" :-D

Query plans are a separate topic, hopefully this helps to clear things up. You are pointing to a different topic in the documentation...

jace

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 1:11 PM

The bigger challenge is convincing the DBAs of the world to embrace dynamic SQL. With DLINQ/ADO.NET v3, and the current generation of ORM tools, DBAs with a stored procedure heady mindset are seriously causing a lot of people to make architectural mistakes.

.. but what about security? [ToungueInCheek(true)]

Sahil Malik

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Friday, May 26, 2006 2:18 PM

Jace: Yes it IS misleading, and I'll explain why: 'compiled' gives people who don't know the fine details the idea that a procedure is a compiled piece of code like a .exe, and any other query is interpreted. (and thus slower).

This is the main issue. Both are interpreted, namely their execution plan is interpreted. To get to the execution plan, the dyn. sql query has to be parsed, the proc is already parsed. However the next time the dyn. query is run, parsing doesn't have to occur, the execution plan is already there and is re-used.

My point against the 'compiled' argument is thus that it's not stored 'compiled' as an .exe, but in post-parse state, which is a tiny advantage, though on a query run, this is often ignorable, unless your query is really complex.

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Saturday, May 27, 2006 2:30 AM

Firstly, in respect to one of the comments which cast aspersions on the scalability of SQL Server, I would like to point out that I have a client with a website that daily attracts 40,000 users within a 15 minute session period querying and rapidly updating a 60GB database. Performance is lightning fast. Reason -- a lot of effort goes into writing efficient stored procedures with optimal data access and carefully chosen indexes. The database isn't merely "an object persistence engine", but is viewed as the solid foundation for performance and scalability, without which no application can scale.

Secondly, I take great issue with this new term "early optimization" and the identification of this as some kind of problem: As a consultant that frequently has to pick up the pieces resulting from developers following the latest trend in framework choices, I can say quite confidently that this "early optimization is a mistake" is simply a cop-out by developers keen to implement version 1.0, but who will not be seen for dust when it comes to dealing with the performance problems 6-12 months down the line.

Put quite simply, if your foundations are shakey, how on earth do you expect to build scalable, high performance code upon them?

Ian Posner

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Saturday, May 27, 2006 4:04 AM

Ian: your comment is precisely the reason why this discussion is still popping up from time to time: it's shortsighted. You know why? Because I have customers who have databases which get millions of row mutations/additions a day, using ... an O/R mapper generating SQL on the fly.

So who's right? Not clear.

"Put quite simply, if your foundations are shakey, how on earth do you expect to build scalable, high performance code upon them?"
So, this website's logic, is that written in hand-optimized assembler? C++ perhaps? Or some VM using language? if the latter, why ? as it costs performance: handoptimized assembler is still king.

Also, SQL generated on the fly can be faster than procs, simply because it is made to do the task you want, not a generic CRUD operation which has to work in all situations.

But now I'm again holding this stupid silly discussion with some person who wants to suggest that if you don't use procs you're a stupid consultant who doesn't know jack sh*t about software engineering. Sorry Ian, but you're very very wrong in that.

So end of story. Any comment which continues this stupid discussion here won't be published. This discussion should be over, people should read up and work together instead of pointing fingers to eachother that the other doesn't know anything about software engineering.

STUPID, that's the word. I thought it would be funny to have a post about how silly the discussion still is. And what do you know? People start the discussion again, here in the comments! :-/

FransBouma

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Monday, May 29, 2006 2:37 PM

Frans,

While I agree with most of your opinions on this subject, I think most people (including you and me) engage on pointless religious arguments on SP/Dyn that HURT efforts to program more efficiently by
- Showing only one side of the story (ok, that's what we humans do by default)
- Trying to find the one-size-fits-all approach.

You forgot a couple points AGAINS SPs:
- They are a PITA to include in source control, as does every DB artifact
- They introduce another layer of complexity/problems, specially for maintainance, which is not needed in most cases

I agree that using SPs for CRUD is useless in MOST cases. But there are cases where you gain in performance and maintainability by using SPs:
- Server-intensive tasks. While cursors have problems of their own, there's no point on doing 1 million roundtrips for processes that could be done in the server
- Some complex data update cases are better kept in the server, where they can be "compiled" and tested in better ways

I predict this argument will end in year 2139, when we are finally replaced with robots.

Diego Mijelshon

# re: Yay! A new Stored Proc vs. Dyn. Sql battle!@ Monday, May 29, 2006 9:13 PM

At last I found an article that wasn't totally one sided.
Most articles about SPs only ever mention the benefits of SPs and never the counter arguments.
I am a great fan of SPs.
But I have found some places take it to the extreme and generate more work for themselves then they need to.
So I'd like to add a few other situations where I find that SPs aren't the only way to go.


Possible sitations when _not_ to use SPs:

1) Not in control of the database
When you are a programmer and you have obsessive Database Administrators
These people demand that you request access to the specific SP through the proper channels.
They will then allow you to make a change that you could have done in 2 minutes.
It can be a lot simpler to avoid the paperwork/Diplomacy/Politics to make a change.
Sometimes it's easier to do it in code.  If they want to protect their realm and restrict access
to those developers who might feel the need to develop something, then either
a) give them a document and tell them to do it (how much delay/paperwork/explanation and Re-work will that take?)
b) get a back door in to their precious database (sorry - but I've done this.  An Admin password works wonders)
c) do it yourself in the code.

2) quick development
open up the application
put in the SQL text you want.
run the application
either way you still have to create the connection to the SQL database, but if you are only doing a
5 minute job (OK there's no such thing. . a 1 hour job) then why bother opening up SQL Enterprise
manager and spending time working out parameters and return variables.
Rush it through using the front end.

3) debugging
SQL has a far inferior debugging compared to - say .NET.
An SQL statement in VB.NET can be stepped into, with variables looked at, altered on the fly.
I'm not saying this is a reason to avoid SPs totally. Once you have debugged it, then by all means put it into a SP.
But then when you come to make a change to the SP .. you'll be back in the non-debuggable situation again.
Sjors P. recommends "Why do you think stored procedures are hard to debug? Just keep your profiler in the background, and debugging is very easy."
You can do some debugging in SPs. It's just no comparison to the flexibility you get in a real programmers development environment.

4) Speed doesn't matter
Writing an application that isn't Db heavy and doesn't need to take advantage of the speed that an SP gives you.
I agree that they are faster (up to 50 times the speed of a normal query (query Analyser) depending on the source you hear it from)
And therefore for a large production app that performs a lot of processing then use SPs to save time.
But not every app can justify this.


5) Development - regularly changing the query
If someone else is regularly changing the stored procedure then the code developers won't have any way of knowing about these changes and will get erratic results.
Until you get it locked down, then you will be just creating extra work for yourself by developing a SP instead of having the query in the code.
Source control is the key here. If your small company doesn't have this then
a) talk alot with your workmaets
b) get source control
c) develop the query you want in the code you are working on, and let them work on -possibly- a duplicate task.  compare notes afterwards.
In theory you specify what the SP is to do and someone develops it.  
In reality - after it has been developed it will need changing due to testing/debugging/changes in specs etc.
You're probably changing the code anyway. Just change the code and the query text in the same workspace.


6) CRUD (Create Read etc.)
I've seen VB functions that call VB functions that call VB functions that call Stored Procedures that do a select statement.
And then the results come back through more functions - each carrying out one line to filter the output or check for an error.
Stepping through the code on this nightmare. Writing the code is time wasting.  Handing over the code to someone else is near-on impossible.
Documenting the code generates enough to deforest half of the Amazon basin.
In small apps or in performing simple tasks, 5 lines of code can only ever be better then 5 layers and levels of functions.
hmm I seem to have gone on a bit of a rant here, and it is actually off topic.
What I'm trying to say is don't make it more compliated then it needs to be.
If you have a basic Select statement then implement the Select statement and get on with it.
For more complex ones - Look in to SPs as an option (see the Trigers comment below).


7) Multiple uses of one SP makes them unwieldly.
You have a single SP to carry out an update query.
But you want to use this in multiple situations ('OOP' - 'reuse of code' - buzzwords etc. )
But it has evolved so that many systems can utilise it.
This now becomes a nice Object Oriented stored procedure that is a nightmare to support.
Optional arguments make any group by or where statement difficult to build in a stored procedure.
Solutions:
a) more SPs - 1 per option
b) more SPs - 1 per application
c) Move some of the queries back in to the code.
I'm not against option 1 or 2, but I am strongly against some 'super efficient' silver bullet SP.


8) Inflexibility of PLSQL vs a real development language
SQL (referring to MS SQL 2000 here) is not a fully developed language like C#.NET or even VBA.
OK it is fully developed, but this is more focused towards data manipulation.
It comes down to the developers judgement as to what bits of the code go in to a SP and what bits go
in to the front end.
String handling or other programmatical statements are a lot easier in VB.NET then in SQL.
Your front end might be in Access, with the back end in SQL.  
Access can perform everything you need w.r.t. data manipulation.
Basically my point here is - does it add anything by making it a SP?


9) mixing the Business logic layer and the DAL layer.
(not a true argument against SPs, more a personal gripe against fully structured development.
But I think it's still worth a mention - and I'll brace myself for the flames ;-) )
A SP might get the data you want, but strictly speaking you can't do anything with the data to match the rules of the business.
You have to pass the data back and forth between your DAL and BLL numerous times.
A Stored procedure can handle each individual data retrieval, but do you really want a SP for each of these?
Putting the query inside the code, and deciding on the business logic at the same time is just plain simpler.
The business logic can be used to build the correct query.


10) The database is due to be migrated to another system,
Your developing a system to use a database, but you know that the database is going to be migrated to another environment soon.
Migrating Data is all very easy (theoretically - this is the real world after all) but in any case
it is a darn sight easier then migrating code.
Especially if you don't know what environment the database is going to be moved to.  Does it even have an equivalent to SPs?


11) The developer might lack the skills
A small company might use SQL to store the data, but the only person who develops applications is actually an
Accountant or the Receptionist.  If he develops the front end in Access drops the data in SQL, then
he might not even know SPs exist.  If they can achieve the result they need using Access then why waste their
precious time learning how to develop a SP.
hmm .. this ties in a bit with number 4 (speed doesn't matter).


12) You can use Triggers and views to achieve the same result.
I'm not advocating using triggers to replace every SP.
But if you want to automatically perform an udate on the Audit table when someone adds a row, then a trigger will do this just fine.
Similarly when you need to Select only a few columns, or you want to restrict acces to only a few Columns then a View is a much simpler method of doing this.
These can be set up to do it across multiple tables etc.
Rob Howard mentions in http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx to use the best tool for the job.
I wholeheartedly agree.
Oh I've already mentioned this in 8.

Have I missed out on some other reasons when SPs just don't work?

I'm not going to bother listing the reason why to use a SP. I know you've all heard them plenty of times.
So I am guilty of only showing one side of this story.  Sorry Frans.
I love SPs and use them alot, but I'm just tired of hearing one side of the argument.

[SimonW now dons his fireproof suit]

reference:
I enjoyed reading Frans Bouma document -
http://www.theserverside.net/news/thread.tss?thread_id=31953#158113
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

and Adam Machanacs response is good and valid.
http://sqljunkies.com/WebLog/amachanic/archive/2004/10/26/4823.aspx

SimonW