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


Use Dynamic SQL to Improve Query Performance


Use Dynamic SQL to Improve Query Performance

Author
Message
Jonathan AC Roberts
Jonathan AC Roberts
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1880 Visits: 1932
Comments posted to this topic are about the item Use Dynamic SQL to Improve Query Performance
chris-945648
chris-945648
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 38
Before you went through all of the rigmarole of creating a stored procedure that constructed dynamic sql, did you try a combination of adding "WITH RECOMPILE" or using OPTION(OPTIMIZE FOR UNKNOWN) in the select itself?
gretchkosiy 2550
gretchkosiy 2550
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 71
Dynamic SQL should be last resort - but sometimes it is unavoidable. Please have a look this article regarding dynamic sql. http://www.sommarskog.se/dynamic_sql.html
Vladimir Basanovic
Vladimir Basanovic
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 46
Maybe you should consider rewriting original function with coalesce on every parameter. That's how you would avoid table scan if the parameter is null.

BR,
Vladimir
Jon Saltzman
Jon Saltzman
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 147
I second the sommarskog.se site (which gretchkosiy 2550 refers to above) - that is an excellent writeup which includes gotchas for SQL 2008 SP1 and dynamic SQL if I recall correctly.

Having had some even more insanely complex queries (many conditions) suffering because of the complete failure of the SQL query engine to short-circuit effectively, I can say that I have resorted to this approach after trying every other one (recompile, optimize for unknown, etc.) and I feel very confident saying that at least as of SQL 2008 SP1, dynamic parameterized SQL is THE way to go for proper index usage and performance.

It is a pain to debug, and it's awfully weird to have SQL generating SQL in a stored proc (in my opinion the stored proc compiler should have been smarter) just to build this kind of search query, but it just plain works. I've seen queries that performed 10x faster as dynamic parameterized SQL vs. static short-circuiting style code - all due to the ability for the right indexes to be used. I tried to contact Microsoft once to ask why the engine didn't recognize this use case, but I never heard back.

I'm certain not all complex search style where condition procs (which often require many OR conditions if statically coded and are therefore not easily SARG-able) need to be dynamic parameterized SQL (for example, if you're dealing with smaller tables - say 10,000 to 100,000 rows - I don't think it's a huge difference). For larger tables or complex sets/long lists of dynamic conditions, I think, as of today at least, it's a good way to go.

As always, test it for yourself, as every environment is different.

Thanks - good article!
bjarup
bjarup
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 14
Nice writeup.
Maybe I am a bit off topic, but I would never allow null values to pass through to the select statement in a procedure if the tables underneath do not allow nulls. This should be handled in the front end application og by using non null default values in the parameters.
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3751 Visits: 3575
chris-945648 (5/19/2010)
Before you went through all of the rigmarole of creating a stored procedure that constructed dynamic sql, did you try a combination of adding "WITH RECOMPILE" or using OPTION(OPTIMIZE FOR UNKNOWN) in the select itself?

And why would that make a difference? SQL Server still has to come up with an execution plan for all the statements in the WHERE clause. I've seen enough procedures with multiple optional parameters and developers trying to create "clever" methods of combining them into one ugly TSQL statement, to know that dynamic SQL cannot be avoided. In SQL Server 2005 and SQL Server 2008 you can also prevent the problem with broken ownership-chaining in dynamic SQL by signing the procedure. Problem solved.
Vladimir Basanovic
Vladimir Basanovic
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 46
Sometimes, dynamic queries are the way to go. But in this example would be interesting to see performance of the modified static query. It would look something like this:

SELECT [Name],
[Color],
[ListPrice],
[ModifiedDate]
FROM [Production].[Product]
WHERE [Name] LIKE coalesce(@Name, [Name])
AND Colour] LIKE coalesce(@Color, [Colour] )
AND [ListPrice] >= coalesce(@ListPriceMin, [ListPrice])
AND [ListPrice] <= coalesce(@ListPriceMax, [ListPrice])
AND ModifiedDate >= coalesce(@ModifiedDateMin, ModifiedDate)
AND ModifiedDate <= coalesce(@ModifiedDateMax, ModifiedDate)


I must say that I am not completely sure that this query is faster than dynamic sql, but seems like it should be.

BR
mohd.nizamuddin
mohd.nizamuddin
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 198
Very well written, a nice article.

Can you please put some light on the excution plan of this SP, while writing dynamic SQL.

As per knowledged, the execution plan won't be resued.

Please suggest.
mohd.nizamuddin
mohd.nizamuddin
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 198
Vladimir Basanovic (5/20/2010)
Maybe you should consider rewriting original function with coalesce on every parameter. That's how you would avoid table scan if the parameter is null.

BR,
Vladimir


Is it Vladmir... Cool

Thanks for sharing the idea. I know it is very simple thing but not aware.

Although I use to write WHERE clause using COALESCE, but do not know the performance improvement.

Really appriciate.
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