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
bsciprian
bsciprian
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: 65
What about this approach?

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

By adding first IS NULL parameter checking, logically the second condition after OR should not be checked if first one is TRUE.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3575
mohd.nizamuddin (5/20/2010)
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.
Execution plans will be cached when using dynamic SQL, and SQL Server will cache a separate plan for each combination of the values in the parameters for sp_executesql.

To test that this actually works I suggest you run Profiler and add SP:CacheHit, SP:CacheMiss and SP:CacheInsert events when you use sp_executesql.
mohd.nizamuddin
mohd.nizamuddin
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 198
Nils Gustav Stråbø (5/20/2010)
mohd.nizamuddin (5/20/2010)
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.
Execution plans will be cached when using dynamic SQL, and SQL Server will cache a separate plan for each combination of the values in the parameters for sp_executesql.

To test that this actually works I suggest you run Profiler and add SP:CacheHit, SP:CacheMiss and SP:CacheInsert events when you use sp_executesql.


Thanks for your prompt response.

Just going through http://www.sommarskog.se/dynamic_sql.html and I got the idea.
:-)
Atif-ullah Sheikh
Atif-ullah Sheikh
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: 3806 Visits: 5190
Dynamic SQL is the last thing to do. I use and prefer the following way;



SELECT [Name],
[Color],
[ListPrice],
[ModifiedDate]
FROM [Production].[Product]
WHERE ([Name] LIKE case when @Name is Null then [Name] else @Name end)
AND ([Color] LIKE case when @Color is Null then [Color] else @Color end)
AND ([ListPrice] >= case when IsNull(@ListPriceMin,0) = 0 then [ListPrice] else @ListPriceMin end)
AND ([ListPrice] <= case when IsNull(@ListPriceMax,0) = 0 then [ListPrice] else @ListPriceMax end)
AND (ModifiedDate >= case when @ModifiedDateMin is Null then ModifiedDate else @ModifiedDateMin end)
AND (ModifiedDate <= case when @ModifiedDateMax is Null then ModifiedDate else @ModifiedDateMax end)




----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3575
Atif Sheikh (5/20/2010)
Dynamic SQL is the last thing to do

Dymamic SQL is the only efficient way (in my knowledge) to solve the problem with multiple optional parameters. Whether you use CASE, COALESCE, ISNULL or OR to try to avoid dynamic SQL, you will most likely (if not always) end up with index scans. I have never seen the methods mentioned above work in a performance perspective. Look at your execution plan the next time you try a MyCol=case when @val is null then MyCol else @val end. You will see a index scan, not a seek (if the column is indexed).
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10092 Visits: 13351
Nils Gustav Stråbø (5/20/2010)
Atif Sheikh (5/20/2010)
Dynamic SQL is the last thing to do

Dymamic SQL is the only efficient way (in my knowledge) to solve the problem with multiple optional parameters. Whether you use CASE, COALESCE, ISNULL or OR to try to avoid dynamic SQL, you will most likely (if not always) end up with index scans. I have never seen the methods mentioned above work in a performance perspective. Look at your execution plan the next time you try a MyCol=case when @val is null then MyCol else @val end. You will see a index scan, not a seek (if the column is indexed).


Ditto. See what Gail Shaw writes about this topic: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3575
Gianluca Sartori (5/20/2010)Ditto. See what Gail Shaw writes about this topic: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.

My experience is that developers (who doesn't understand how a DBMS executes a query and have never heard of execution plans) write the queries on tables with small number of test rows. When they see that it works, and quite fast because of the small numbers of rows, they are happy with it and puts it to production. A year or two later, when the amount of data has grown large, the start to get complaints from the users because things are slow.
Abrar Ahmad_
Abrar Ahmad_
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 1296
mohd.nizamuddin (5/20/2010)
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.


After traversing here and there
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

nothing but more controversies ! Crying

Brett Robson
Brett Robson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 19
I don't see why you guys are promotiing the use of dynamic SQL. I'm often asked in job interviews when it's appropriate to use dynamic SQL - the correct answer is never.

I have never (and will never) see dynamic SQL out perform a properly coded & compiled stored procedure. It's impossible - NOTHING gets cached.


Regards,

Brett Robson
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10092 Visits: 13351
Brett Robson (5/20/2010)
I don't see why you guys are promotiing the use of dynamic SQL. I'm often asked in job interviews when it's appropriate to use dynamic SQL - the correct answer is never.

I have never (and will never) see dynamic SQL out perform a properly coded & compiled stored procedure. It's impossible - NOTHING gets cached.


"Never" and "Always" hardly apply to the database world. "It depends" fits better.

You have two possibilities:
1) Keep your idea that dynamic sql is evil and should never be used
2) Read the excellent articles (especially Erland Sommarskog's and Gail Shaw's blogs) that were linked in this thread and try to learn something
The coiche is yours.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
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