Dynamic SQL vs. Stored Procedure
-
I have a co-worker at another corporate site, less experienced in SQL Server than myself, who is using dynamic SQL all over the place in an app he is developing. He asked me to help with a query to try to make it more efficient. He did not send me the code but gave me the scenario so I sent him some code which he tossed out because he was using Dynamic SQL and I did not write it that way. I mentioned that stored porcedures are usually more efficient than Dynamic SQL and he responded with this:
I read someplace on the internet that someone tested the speed of dynamic SQL vs Stored Procedures and he was extremely surprised to find that dynamic sql was faster than Stored Procedures. Might have been a specific case, but he said he tried several scenarios.
Granted, I do not know everything about SQL, but I am pretty sure that I am right in this case. Any good arguments, articles out there?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi Jack!
Dynamic SQL is as bad as using cursors in SQL Server
First of all it is a huge sequrity risk to use dynamic sql, since there are various ways you can hack yourself into a sql box using dynamic sql.
Second, SQL Server has to recompile the query every single time you run it. Using a stores procedure (without dynamic sql in it), SQL Server will only compile it when you create or alter the sproc (or tells it to recompile it).
There might be cases where dynamic sql is faster then a stored sproc, but I have never seen it with my own eyes...
Maybe other persons here have?
robbac
___the truth is out there___ -
In SQL 2000 "dynamic SQL" is not recompiled every time, but rather cached in on the server.
From MSDN:
When SQL Server receives a call to sp_executesql, it automatically checks the procedure cache for a matching plan and reuses that plan or generates a new plan.
More from MSDN: "Execution Plan Caching and Reuse"
When any SQL statement is executed in SQL Server 2000, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2000 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2000 generates a new execution plan for the query.
About the "Huge Security Risk".
This is partially true. If you don't do proper input validation, then yes this will happen. But proper input validation can be a little as 5 lines of code using regular expressions. And can save a call to the DB, and display a nice error message to the user. Where as the alternative (as you mention) would be a SQL parameter error, and a nasty error returned to the user (or maybe just ignored).
However, it is still better to make Stored Procs, for the reason that code is contained in one location, where as using "dynamic Stored Procs" on the web, you might need to change in many locations.
About the "as bad as using cursors in SQL Server"
Again I agree and disagree. I have seen many instances where cursors were incorrectly used, and could have been replaced with a "Bulk Insert / Update" statement (and would have speeded the whole process up. But I have also implemented some cursors, that run a lot faster in a SQL stored Proc, then writing the equivalent in VB (Com+) or VBS (ASP).
My rule of thumb about cursors are:
If one column relies on the sequential result of the previous column, then use a cursor, otherwise use a "bulk Insert / Update". (Thus a cursor serializes the columns / values / results)
Regards
Anton
-
Assembler is faster than VB. Which would you rather use to code a Windows app?
-
check out : The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hey, that's my usual link
Interestingly this article http://www.sommarskog.se/dyn-search.html on Erland's site gives additional information when to consider the use of dynamic sql and when to avoid it.
I would be interested what source Jack's coworker read that resumes dynamic sql is faster than stored procedures. I bet it's of of those dynamic search scenarios mentioneed in the above article.
But after all, it's not all about performance and speed. One of the IMHO most important drawbacks of dynamic sql are the security implication.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply