Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get a Return Every Time. Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2008 7:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:18 AM
Points: 1,140, 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 :)
Post #463748
Posted Tuesday, March 4, 2008 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 21, 2011 10:15 AM
Points: 3, 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.
Post #463766
Posted Tuesday, March 4, 2008 7:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 16, 2012 6:18 AM
Points: 53, 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
Post #463784
Posted Tuesday, March 4, 2008 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 8, 2011 9:12 AM
Points: 393, 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
Post #463824
Posted Tuesday, March 4, 2008 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:58 AM
Points: 136, Visits: 616
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

Post #463844
Posted Tuesday, March 4, 2008 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 16, 2009 7:18 AM
Points: 2, 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.
Post #463932
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse