The truth about Dynamic SQL and Stored Procedures....

Well I am not going to say what's better or what I prefer, but I can only give my view of things.


What everybody seems to forget is that an external person with very good understanding of SQL (commonly know as a DBA) can solve performance problems when stored procedures are used.
And I totally agree that Stored Procedures are overkill for most of the the simple CRUD functionality. So I am not going to start a debate about that.


But just one question for all those who are against stored procedures.


What if the generated dynamic SQL performs bad. If a SPROC was used a SQL DBA could have tuned the query.
And by tuning I don't mean adding some indexes, but maybe cursor's / memory tables / views / CLR etc. Just to get the results in the fastest possible way.


How would you do this with dynamic SQL ??

Published Monday, January 07, 2008 9:34 AM by Patrick Wellink
Filed under:

Comments

# re: The truth about Dynamic SQL and Stored Procedures....

Monday, January 07, 2008 8:35 PM by Dennis van der Stelt

If you can optimize/tune stored procs, you can optimize dynamic sql. Simple.

Same goes for LINQ to SQL or other O/RM, you must optimize everything. And choose the correct technology to solve problems. LINQ to SQL or other O/RM help you solve problems by making them easier. If you need complete control over performance, use a stored proc.

I've seen stored procs of thousands of lines. Tell me, how are you going to optimize that?

# re: The truth about Dynamic SQL and Stored Procedures....

Monday, January 07, 2008 9:16 PM by Patrick Wellink

I have build stored procs of hundreds of lines. This sproc came instead of a sproc of a couple of lines. (The programmers never thought it would get slow) but in time the database grows, and finally they end up with a menu that's just tooooo slow.

This was for the Main menu of I-Mode. So when the simple query didn't work within the specified time frame, it was very simple to rewrite the query and use some logic to speed it up.

So then we ended up with a versiopn 1200 times faster then the original.

This kind of tuning simply cannot be done via dynamic sql. (Sure, you could rewrite the query and compile the dll and deploy that. but that has way more impacht then changing a sproc)

Don't get me wrong, I am not against Dynamic SQL. But I aint against SPROC's eiter.

And you hit a very good point in your comments. You must Optimize them both.....

But who would you trust more to deliver performant code. A programmer or a DBA ? personally I would think a DBA would deliver more performant databases.

And finally dennis, I wont say a sproc of thousands of lines is all that well either. But maybe there was a very valid reason for doing it, or maybe there wasn't one.

# re: The truth about Dynamic SQL and Stored Procedures....

Monday, January 07, 2008 10:35 PM by Chilberto

An important factor in this discussion should be what is being built.  A lot of smaller systems do not require the overhead of stored procedures and would probably benefit from a more "fluid" development environment.

From my standpoint, I prefer stored procedures for business applications that will involve more than one developer over its lifetime.  They tend to be easier to maintain, analyze and tune.

# re: The truth about Dynamic SQL and Stored Procedures....

Tuesday, January 08, 2008 8:03 AM by Dennis van der Stelt

Patrick, there's not a good reason to have thousands of lines of code in a single method, and there isn't one to have them in your sproc. But still people do so.

Of course this isn't about that you can never use stored procedures. Because sometimes, you can handle large amounts of data in them much more effeciently than via code. Because that's what SQL Server or Oracle do best. If you need performance, do it at the source.

But my problem with stored procedures is that so many times I come across them when there's so much business logic in them. And they're still harder to debug than regular code. People should use stored procedures for retrieving and changing data. Not for business rules or all other kinds of stuff...

# re: The truth about Dynamic SQL and Stored Procedures....

Tuesday, January 08, 2008 8:44 AM by Patrick Wellink

I understand your point but I have seen bad Dynamic SQl as well...

But your statement :

you can handle large amounts of data in them much more effeciently than via code. Because that's what SQL Server or Oracle do best. If you need performance, do it at the source.

I totally agree with you at this point. But this implies that you have yo implement some logic in SQL.

You sould always consider where you put stuff. For simple selects and updates it's ok to use dynamic SQL. But as soon as the words 'Large amounts of data' are uttered you should consider switching to Sproc's. Unfortunately this also means you have to implement part of the Business logic in SQL as well.