SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get a Return Every Time.


Get a Return Every Time.

Author
Message
Craig@Work
Craig@Work
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1340 Visits: 326
hmmm maybe over-engineered? Surely variable checking should be at presentation tier?

I've grown allergic to dynamic-SQL, tended to find it such a performance killer and generator of bugs in its own right.

While always recompiling may deliver better performance, would have thought this would be the exception rather than the rule.

I've arrived at the point of only using dynamic-SQL as an absolute last resort (and that equates to never at the moment )

Thanks for insight into another approach though Smile
Andrew McCrackin
Andrew McCrackin
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 11
( Insert USP_Get_Customer_Data Script)

I don't see the actual example script........ bad merge? missing file?

Takes some of the kick out of the article. Also, I see some limited usefulness for development, but I can't imagine depending on non-compiler optimized procedures in a production envirionment.
Ben Vegiard
Ben Vegiard
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 80
I have to throw in here. I have had similar thoughts as to the author in the past. However, at the time, I was working on low volume systems.

For the last year and a half I have been working on a much higher volume (1,500 transactions a minute) system with a complex structure (900+ tables) and have seen the evils of using dynamic SQL for all the points given in this forum. Since I suspect the author is working on much lower volume systems, I am betting they do not see the ill effects (yet). From that perspective, let me say:

1) Performance: When SQL is consistently busy, recompiling plans on every execution drains resources in a way that is very noticeable to an end user. This is, of course, death to a large system.
2) Performance: Yes, I have seen some queries run faster under dynamic SQL, but I have seen the opposite more often. When a query is faster under dynamic SQL, it is usually because the search screens are written TOO flexibly, and the query cannot anticipate all reasonable combinations of parameters. I’d strongly suggest instilling in the development team that separate searches for different TYPES of searches be developed, or have your procs “sense” what type of search the user is trying to perform and route the query to an appropriate static stored proc so that the query plan can be cached.
3) Performance: When using complex dynamic SQL, it’s just too easy to get knocked off an index and start a table scan. Again, you won’t see this too often on smaller systems that join three 40,000 record tables, but when you’re working with 6 1,000,000+ record tables, you’ll see it immediately.
4) Maintainability: When there are 20 different coders in the environment, any pattern that is not well known (no matter how worthy) is confusing.
5) Security: Dynamic SQL will execute with different security contexts depending on how you connect (Trusted connection, SQL Login, etc) and you will find that if you start implementing security that the dynamic SQL queries may start not having access to tables!!! This one bit me very hard!
I say all this not to take a shot at anyone, but hopefully to share my learned experience.

Ben
www.sqlcoach.blogspot.com
Charlie-551146
Charlie-551146
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 74
Thank You all for your comments. I will use your posted tips, and pitfalls to expand on my knowledge. This is a learning experience for me.

Thank You,

Charlie

Charles Eaton
the sqlist
the sqlist
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 739
Cliff Knight (3/4/2008)
I have found dynamic SQL statements to be faster in many situations where the exact nature (and number, when using optional parameters) of passed parameters is unknown at the time a static stored procedure is saved.

In these cases the query is often not optimised to the execution plan that is the best plan for the parameters employed and their values and a dynamic query that forces parsing and optimisation will run 2x to 3x faster.

Additional Tips for enhanced readability of code:

Use plenty of whitespace, it makes no discernable difference in execution time;

Build string literals into your dynamic queries using double quotes and then use the Replace() function to fold them to single quotes before execution;

SET @SQL =
'
SELECT LastName + ", " + FirstName

FROM MyTable

WHERE LastName LIKE "knigh%"
'

SET @SQL = Replace(@SQL, '"', '''')

EXEC(@SQL)


I use these methods in an 8000+ user classic ASP app, with SQL 2000 databases having 6 to 10 million records in the main transaction and master balance tables--with very acceptable performance.


In these situation you don't have to use dynamic queries but use the WITH RECOMPILE clause in the procedure definition. This will cause to recompile and rebuild the execution plan with every execution of the stored procedure.

Related to the article itself it is by NO means the way to go. Using the dynamic queries is not something for the development or the returned result convenience but to the situations where it CAN'T be avoided, like having to use different tables or columns depending of the situation.

Point is use dynamic queries ONLY when absolutely necessary. Other than that static queries is the way to go.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
Cliff Knight
Cliff Knight
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 9
rsingh84 (3/4/2008)
I am quite surprised that someone thinks querystring based queries are faster than compiled queries.

Cliff - in your example, you will mostlikely have same performace as a compiled SP since you query is using only a single filter which may be having an index on it. So the performance may be more or less the same.

Don't be silly, that was an example of using the Replace() function--not a serious example of a dynamic query...



Using dynamic query is gives rise to SQL Injection - way to hack into your db and even delete your data. So you have to be very carefully and should avoid using it.

Nonsense...

A dynamic query inside a stored procedure is no more or less susceptibe to "SQL Injection" than a static query inside a stored procedure.


As a final comment, I have been writing manage reporting and OLT systems for well over 30 years and have been on the planet for 60 years--if there is any one thing that's made it through my thick head it is that there is NO single best way of doing anything...

I said in my original post that I have found "dynamic SQL statements to be faster in many situations where the exact nature (and number, when using optional parameters) of passed parameters is unknown at the time a static stored procedure is saved."

I stand by that statement and suspect those that have not experienced this have not dealt with particularly complex and configurable queries--it's absolutely not true all the time, however it is much more often that one would think.

This is especially true with contemporary machines having gobs of CPU cycles and memory.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search