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 12345»»»

Use Dynamic SQL to Improve Query Performance Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 9:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
Comments posted to this topic are about the item Use Dynamic SQL to Improve Query Performance
Post #924792
Posted Wednesday, May 19, 2010 9:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:26 PM
Points: 17, 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?

Post #924795
Posted Wednesday, May 19, 2010 9:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:44 PM
Points: 2, Visits: 70
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
Post #924796
Posted Thursday, May 20, 2010 12:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 2:53 AM
Points: 2, 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
Post #924826
Posted Thursday, May 20, 2010 12:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 30, 2013 1:20 PM
Points: 56, Visits: 137
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!
Post #924831
Posted Thursday, May 20, 2010 12:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 6, 2011 11:53 PM
Points: 2, 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.
Post #924833
Posted Thursday, May 20, 2010 12:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 1,880, Visits: 3,460
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.
Post #924834
Posted Thursday, May 20, 2010 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 2:53 AM
Points: 2, 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
Post #924836
Posted Thursday, May 20, 2010 12:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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.
Post #924842
Posted Thursday, May 20, 2010 12:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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...

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.
Post #924845
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse