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 Thursday, May 20, 2010 12:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:46 AM
Points: 15, Visits: 63
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.
Post #924846
Posted Thursday, May 20, 2010 1:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,869, Visits: 3,457
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.
Post #924855
Posted Thursday, May 20, 2010 1:06 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
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.
Post #924857
Posted Thursday, May 20, 2010 1:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:48 AM
Points: 3,241, Visits: 5,002
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

Post #924858
Posted Thursday, May 20, 2010 1:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,869, Visits: 3,457
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).
Post #924864
Posted Thursday, May 20, 2010 1:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:49 PM
Points: 4,324, Visits: 10,583
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #924866
Posted Thursday, May 20, 2010 1:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 1,869, Visits: 3,457
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.
Post #924868
Posted Thursday, May 20, 2010 1:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:12 AM
Points: 212, Visits: 1,116
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...

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 !
Post #924869
Posted Thursday, May 20, 2010 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:09 AM
Points: 1, 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
Post #924871
Posted Thursday, May 20, 2010 2:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:49 PM
Points: 4,324, Visits: 10,583
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #924879
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse