InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:15:38 GMT20http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Brett Robson (5/20/2010)[/b][hr]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.[/quote]Sorry, Brett, the correct answer is "it depends", like Gianluca and others rightly pointed out.If it weren't for company confidentiality, I would love to post the SP that generates dynamic SQL comprising nested CTEs to solve formulae stored as SQL strings in the database that consume further formulae and/or aggregated raw data and so on, involving differing aggregate operations, AVG()/SUM()/COUNT etc. at each level, accommodating differing periodicity of inputs, requiring pre-aggregation or not, converting signal counts to digital readings or not, drawing from cached results or raw data and more - 'nuff said as to complexity? And then issue a T-SQL Challenge to come up with a solution that completely obviates dynamic SQL... Under SQL 2005 one of our formulas expands to the point where over 256 tables are involved in the query and it gets rejected upon exec... NEVER is a word that is blown out the door by this core process... And the typical dynamic SQL runs in around 0.5 [u]seconds[/u] on multiple tens of thousands of rows drawn from a base table comprising 2,104,604,629 rows - GO DYNAMIC SQL!If you run Kimberley Tripp's revealing cache query at [url=http://sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx]Plan cache and optimizing for adhoc workloads[/url] you'll see that IF you run dynamic SQL, you CAN see plan reuse. Even the monster described above achieved plan-reuse.Be careful that you look at every problem with an open mind...;-)Fri, 17 Feb 2012 11:52:54 GMTSAinCAhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJames,At the risk of sounding snotty, you handicapped Case 2 intentionally to remove it from consideration. Neither of the other cases will return the same RESULTS as case 2 will. Try it. Case 3 will return nothing because LastName and Email are Null. Case 4 will return nothing as @Email is NULL. Only Case 2 properly represents a FirstName search. Therefore it is the only [i]correct[/i] case.Look at the number of coded cases you will need for the approach in case 3...1 input parameter = 2 cases (with/without null)2 input parameters = 4 cases3 input parameters = 8 cases4 input parameters = 16 cases5 input parameters = 32 cases6 input parameters = 64 cases7 input parameters = 128 cases8 input parameters = 256 casesNow, if you need a true dynamic query the ONLY option that has performance across all input parameters on a million row test table is Dynamic SQL. Otherwise at 8 input parameters you are trying to maintain 256 cases which is not only impractical, it's virtually impossible.Edit: I didn't realize this article/thread was over 2 years old... I don't expect a response given how old it is.[quote][b]James Dingle-651585 (5/26/2010)[/b][hr][code="sql"]If OBJECT_ID('Case3GetUser', 'P') Is Not Null Drop Procedure Case3GetUser;GoCreate Procedure Case3GetUser ( @LastName VarChar(50) = Null, @FirstName VarChar(50) = Null, @Email VarChar(255) = Null)AsRaisError('Case3GetUser', 10, 1) With NoWait;Select *From [User]Where( -- Last name only (omitted but implicit: "And @LastName Is Not Null") UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName Is Null) -- Last name and first name together (omitted but implicit "And LastName Is Not Null And @FirstName Is Not Null") Or UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName = FirstName) -- by email (omitted but implicit "And @Email Is Not Null") Or UserId In (Select UserId From [User] Where @Email = Email))Go[/code]Looking at the execution plans, we observe that:1. Method 2 have a unique execution plan per combination of parameters, while methods 3 and 4 always have the same one.2. Because methods 3 & 4 have each only ONE execution plan, it's easy to review. For method 2, you have to test all the combinations of parameters and look at each execution plan generated.3. The only terrible SQL statement and execution plan come from method 2. It is an illustration of an unexpected code path leading to the execution of something that has not been reviewed nor predicted.Looking at the I/O statistics you will observe that:1. All methods give the same performance when running in the use case they were designed for.3. In the bogus situation, method 2 is the only one to turn into a full scan, while others do not put the server in danger.[/quote]Fri, 17 Feb 2012 07:24:29 GMTvenoymhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxVery nice article.Some month ago, I tried to do something like that, and I tried to improve the query doing my best but…I found that when you have dynamic queries, so you need to do the classic string concat in order to do it, the best I found was CLR Stored Procedure. I found a big difference into the performance when you query tables with more than 20 millions of rows.SQL Server also saves in cache a dynamic query from a CLR Stored procedure, and for a normal dynamic query made in a Stored Procedure it don’t.This is my opinion, of course, I am not a SQL Server guru I am just a .Net Developer and I had big performance problems to solve, so I found this way to solve it.In my case I use very big tables from a Telecom system.., so the performance is very important.CheersMarcosFri, 17 Feb 2012 03:19:45 GMTmarcosjroighttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxVery nice article. Keep up the good work.Very well written and informative.My only complain would be the use of hacks like "always true" or "always false".The "WHERE 1 = 1" was used nicely though. :)Mon, 21 Jun 2010 18:41:00 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxI thinks it will be well preformance using sp_executesql for dynamic sql query. it can reuse the execure plan in cache, so reduce the compile time.:-)Wed, 26 May 2010 23:16:54 GMTchangblueskyhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxGood article. This is one of the most common culprits I have found of long running procedures and lock issues at a number of various clients for whom I have done work. (Search query takes for ever and prevents write operations from other processes). Typically, I'll recommend checking the parameters and executing the appropriate query with only the arguments that are not null (literally duplicating the query for every combination). In cases where you have 4+ optional parameters, then I generally recommend the Dynamic SQL with Variables approach, as the proc woudl become otherwise unwieldy. Though someone mentioned issues with execution scope where data could not be access directly, the duplicated query with only the specified fields, while wordy and ugly, would provide the performance benefits without this limitation. as a consultant, sometimes I like seeing the OR IS NULl approach, as I can quicly re-write it with thousandfold performance improvements and the client loves me for it :-D. Other times, it annoys me because obviously people didn't bother investigating what thier queries were DOING...Thanks Paul W for posting info about the RECOMPILE option changes in SP1 CU5. I'll look into that for some cases, though often when I hit this pattern the SQL itself if generally complex search patterns that would suffer from forced recompilation.Wed, 26 May 2010 14:05:18 GMTPaul Muharsky-474732http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxHey James,So:[color="#0000ff"][i] "It is well know for instance that if we put NEWID() in a SELECT statement, it will be executed once per row. Other functions and expressions such as GetUtcDate() won't. This shows how the functions and expressions are evaluated, and how the mess will be big if they break this behavior."[/i][/color]Really? Read the comments from the SQL Server development team on this Won't Fix bug report:[url]https://connect.microsoft.com/SQLServer/feedback/details/350485/bug-with-newid-and-table-expressions[/url]You're going to get a full response to your long post in due course, but I thought you should see that first.PaulWed, 26 May 2010 11:17:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxFurther to Paul's invitation I have prepared a more elaborated comparison between 3 methods we have talked about in this thread.I hope that, and the end of my comparison, and assuming you will think I have not missed something important, you will understand my concerns and warnings about dynamic SQL that generates code.=========================================================================OBJECT TESTED:- A user table with 1 million rows and 4 columns: ID, First name, Last name, Email.- System tested: SQL2008 x64 10.0.2531, Windows 7 x64, Athlon 64 dual core 3800+GOAL TO ACHIEVE:- Implement a search within the users by: last name, last name and first name, and email.METHODS COMPARED:1) As a point of reference, ad-hoc stored procedures2) Dynamic generation of the code3) Dynamic SQL statement using scalar expressions4) ad-hoc stored procedure with OPTION (RECOMPILE)SPECIAL:- A bug is included, of course on purpose. The bug is that methods 2, 3, 4 allow to execute a seach by first name ONLY while there is no index on this column, and this is not supported by the writer of the stored procedure. In this example, the bug is very obvious to notice, just looking at the code. I ask you to admit that in real conditions, in a code that is 2 or 3 pages long, the bug would be less obvious, and can reach production without having been detected.WHAT I WOULD LIKE TO EVALUATE:- I would like to show how the method 2 can lead to nasty situations when the bug is met, and how can a query and an execution plan that have never been reviewed get executed on production.- I would like to show how the method 3 prevents, when the same bug is met, that a unexpected execution plan is run, and thus how this methods protects the total application from unexpected use of its stored procedures.- I would like to show how method 3 allows the writer to review ALL possible code paths while method 2 can't. - I would like to show that method 4 is actually slower so it's not a solution if you have a lot of activity on the machine and/or if response time is important.- The post is not about performance, the post is about predictability, how robust is your application once shipped. Though it is important to compare performances because, even if a method is better on a predictibility and stability point of view, it's not really usable if it does not perform well.- I WILL NOT discuss about execution plans and how to get to a satisfying one with each method used. I assume that we reached this point so we can compare them. I think I wrote the code impartially.- General comments that have happened will be added as Q&A at the end.==========================================================================TEST SETUPRun [url=http://www.sqlservercentral.com/Forums/Attachment6104.aspx]DynamicQueries_Databases.txt[/url] to initialize the database[code]Set NoCount On;-- Create the databaseUse master;GoIf Exists (Select * From sys.databases Where name = 'DynamicQueries') Drop Database DynamicQueries;Create Database DynamicQueries;GoUse DynamicQueries;Go-- Create the tableCreate Table [User]( UserId Int Not Null Identity (1, 1) Not For Replication, LastName VarChar(50) Not Null, FirstName VarChar(50) Not Null, Email VarChar(255) Not Null, Constraint PK_User Primary Key Clustered (UserId));Create Index IX_Name On [User] (LastName, FirstName);GoCreate Index IX_Email On [User] (Email);Go-- Fill with dataDeclare @I Int;Set @I = 1000000/50000;While @I > 0Begin RaisError('%d filling iterations remaining...', 10, 1, @I) With NoWait; Insert Into [User] (LastName, FirstName, Email) Select Top 50000 NEWID(), NEWID(), NEWID() From sys.messages Set @I = @I - 1;End-- Rebuild so the indexes are most efficientAlter Table [User] Rebuild;Alter Index IX_Email On [User] Rebuild;Alter Index IX_Name On [User] Rebuild;[/code]Run [url=http://www.sqlservercentral.com/Forums/Attachment6105.aspx]DynamicQueries_Databases[/url] to create the stored procedures[code]Use DynamicQueries;Go-- Case 1 : static ad-hoc queries, to serve as referenceIf OBJECT_ID('Case1GetUserByLastName', 'P') Is Not Null Drop Procedure Case1GetUserByLastName;GoCreate Procedure Case1GetUserByLastName ( @LastName VarChar(50))AsRaisError('Case1GetUserByLastName', 10, 1) With NoWait;Select *From [User]Where LastName = @LastNameGoIf OBJECT_ID('Case1GetUserByLastAndFirstNames', 'P') Is Not Null Drop Procedure Case1GetUserByLastAndFirstNames;GoCreate Procedure Case1GetUserByLastAndFirstNames ( @LastName VarChar(50), @FirstName VarChar(50))AsRaisError('Case1GetUserByLastAndFirstNames', 10, 1) With NoWait;Select *From [User]Where LastName = @LastNameAnd FirstName = @FirstNameGoIf OBJECT_ID('Case1GetUserByEmail', 'P') Is Not Null Drop Procedure Case1GetUserByEmail;GoCreate Procedure Case1GetUserByEmail ( @Email VarChar(255))AsRaisError('Case1GetUserByEmail', 10, 1) With NoWait;Select *From [User]Where Email = @Email;Go-- Case 2 : dynamic query using generated codeIf OBJECT_ID('Case2GetUser', 'P') Is Not Null Drop Procedure Case2GetUser;GoCreate Procedure Case2GetUser ( @LastName VarChar(50) = Null, @FirstName VarChar(50) = Null, @Email VarChar(255) = Null, @Debug Bit = 0)AsRaisError('Case2GetUser', 10, 1) With NoWait;Declare @Sql NVarChar(4000);Set @Sql = 'SELECT * FROM [User] WHERE 1 = 1 'If @LastName Is Not Null Set @Sql = @Sql + ' AND LastName = @LastName 'If @FirstName Is Not Null Set @Sql = @Sql + ' AND FirstName = @FirstName 'If @Email Is Not Null Set @Sql = @Sql + ' AND Email = @Email 'If @Debug = 1 Print @Sql;Exec sp_executesql @Sql, N'@LastName VarChar(50), @FirstName VarChar(50), @Email VarChar(50)', @LastName = @LastName, @FirstName = @FirstName, @Email = @Email;Go-- Case 3 : dynamic query using scalar expressionsIf OBJECT_ID('Case3GetUser', 'P') Is Not Null Drop Procedure Case3GetUser;GoCreate Procedure Case3GetUser ( @LastName VarChar(50) = Null, @FirstName VarChar(50) = Null, @Email VarChar(255) = Null)AsRaisError('Case3GetUser', 10, 1) With NoWait;Select *From [User]Where( -- Last name only (omitted but implicit: "And @LastName Is Not Null") UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName Is Null) -- Last name and first name together (omitted but implicit "And LastName Is Not Null And @FirstName Is Not Null") Or UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName = FirstName) -- by email (omitted but implicit "And @Email Is Not Null") Or UserId In (Select UserId From [User] Where @Email = Email))Go-- Case 4 : just-in-time compilationIf OBJECT_ID('Case4GetUser', 'P') Is Not Null Drop Procedure Case4GetUser;GoCreate Procedure Case4GetUser ( @LastName VarChar(50) = Null, @FirstName VarChar(50) = Null, @Email VarChar(255) = Null)AsRaisError('Case4GetUser', 10, 1) With NoWait;Select *From [User]Where( ( @LastName = LastName And ( @FirstName = FirstName Or @FirstName Is Null ) ) Or ( @Email = Email ))Option (Recompile)Go[/code]==========================================================================TEST RUN[u]A. Showing how generation of code on the fly can be dangerous[/u]1. Open [url=http://www.sqlservercentral.com/Forums/Attachment6106.aspx]DynamicQueries_BVTs.txt[/url][code]-- Run this query to initialize the variables below-- Select Top 1 LastName, FirstName, Email From [User]-- Activate SQLCMD mode!:setvar LASTNAME "4A7801E6-B295-46BD-953F-9FAC90E9B5E4":setvar FIRSTNAME "2C4C3500-1ADC-46E0-8A7C-80008B099A28":setvar EMAIL "30A7195A-2E88-49B5-95DC-818E08A893F0"Set NoCount On;Set Language us_english;Set Statistics IO On;Declare @LastName VarChar(50) = NewId();GoPrint 'BVTs'-- BVT case 1Exec Case1GetUserByLastName @LastName = '$(LASTNAME)';GoExec Case1GetUserByLastAndFirstNames @LastName = '$(LASTNAME)', @FirstName = '$(FIRSTNAME)';GoExec Case1GetUserByEmail @Email = '$(EMAIL)';Go-- BVT case 2Exec Case2GetUser @LastName = '$(LASTNAME)';GoExec Case2GetUser @LastName = '$(LASTNAME)', @FirstName = '$(FIRSTNAME)';GoExec Case2GetUser @Email = '$(EMAIL)';Go-- BVT case 3Exec Case3GetUser @LastName = '$(LASTNAME)';GoExec Case3GetUser @LastName = '$(LASTNAME)', @FirstName = '$(FIRSTNAME)';GoExec Case3GetUser @Email = '$(EMAIL)';Go-- BVT case 4Exec Case4GetUser @LastName = '$(LASTNAME)';GoExec Case4GetUser @LastName = '$(LASTNAME)', @FirstName = '$(FIRSTNAME)';GoExec Case4GetUser @Email = '$(EMAIL)';Go-- Test unforeseen situationPrint 'Trick me';GoExec Case2GetUser @FirstName = '$(FIRSTNAME)';GoExec Case3GetUser @FirstName = '$(FIRSTNAME)';GoExec Case4GetUser @FirstName = '$(FIRSTNAME)';GoSet Statistics IO Off;[/code]2. Activate SQLCMD mode if not already3. Execute the first query in second line comment to get values from the table, replace mine in the ":setvar" statements.4. Depending if you want to see them or not, activate execution plans.5. Run the queryLooking at the execution plans, we observe that:1. Method 2 have a unique execution plan per combination of parameters, while methods 3 and 4 always have the same one.2. Because methods 3 & 4 have each only ONE execution plan, it's easy to review. For method 2, you have to test all the combinations of parameters and look at each execution plan generated.3. The only terrible SQL statement and execution plan come from method 2. It is an illustration of an unexpected code path leading to the execution of something that has not been reviewed nor predicted.Looking at the I/O statistics you will observe that:1. All methods give the same performance when running in the use case they were designed for.3. In the bogus situation, method 2 is the only one to turn into a full scan, while others do not put the server in danger.[img]http://www.sqlservercentral.com/Forums/Attachment6103.aspx[/img][u]B. Showing that OPTION RECOMPILE has a cost[/u]1. Open the Windows performance monitor (perfmon.msc)[code="other"]Option ExplicitDim con, com, constrDim Iconstr = "Provider=SQLNCLI;Server=localhost\sql2008;Initial Catalog=DynamicQueries;Integrated Security=SSPI;"Set con = CreateObject("ADODB.Connection")Set com = CreateObject("ADODB.Command")con.Open constrSet com.ActiveConnection = con'================================================='WITH OPTION RECOMPILE (CASE 4), NOT PREPAREDcon.Execute "DBCC FREEPROCCACHE"WScript.Sleep 5000com.CommandText = "Declare @Id VarChar(50); Set @Id = NewId(); Exec Case4GetUser @LastName = @Id;"For I = 1 To 3000 com.ExecuteNext'================================================='WITH OPTION RECOMPILE (CASE 4), PREPAREDcon.Execute "DBCC FREEPROCCACHE"WScript.Sleep 5000com.CommandText = "Declare @Id VarChar(50); Set @Id = NewId(); Exec Case4GetUser @LastName = @Id;"com.Prepared = TrueFor I = 1 To 3000 com.ExecuteNext'================================================='WITHOUT OPTION RECOMPILE (CASE 3), NOT PREPAREDcon.Execute "DBCC FREEPROCCACHE"WScript.Sleep 5000com.CommandText = "Declare @Id VarChar(50); Set @Id = NewId(); Exec Case3GetUser @LastName = @Id;"For I = 1 To 3000 com.ExecuteNextcon.Execute "DBCC FREEPROCCACHE"'================================================='REFERENCE (CASE 1), NOT PREPAREDcon.Execute "DBCC FREEPROCCACHE"WScript.Sleep 5000com.CommandText = "Declare @Id VarChar(50); Set @Id = NewId(); Exec Case1GetUserByLastName @LastName = @Id;"For I = 1 To 3000 com.ExecuteNext' Clean so Cache Hit Ratio graph falls to zerocon.Execute "DBCC FREEPROCCACHE"con.Close[/code]2. Remove all counters.3. Add counters "SQL Statistics::SQL Compilations/sec", "SQL Statistics::SQL Re-compilations/sec", "Plan Cache::Cache hit ratio", "Batch Requests/sec". Put "BatchRequests/sec" bolder and in the background, in a different color than "compilations/sec".4. Put all the compilation scales to 1, the cache hit ratio to 10, and the maximum value of the vertical ladder to 1000. You may have to adjust the output depending on the performance of your machine.5. Open a command line window, and run "cscript TestDynamicQueries.vbs"6. The script runs 3000 executions of the following use cases: - Method 4 without prepare - Method 4 with prepare - Method 3 without prepare - Method 1 without prepare[img]http://www.sqlservercentral.com/Forums/Attachment6102.aspx[/img]Reading the graph it is easy to realize that:1. OPTION RECOMPILE really HURTS! 2. Using "Prepare = True" solves the compilation problem of the SQL command in the VBS script, and thus enables plan cache to work. We jump from two to one compilation per execution.3. But still, there is a compilation per call and this makes the 3000 executions to last way longer.==========================================================================Q&A[i]1. I use method 2, and it is not possible I ever have any problem with unexpected combination of parameters, because I totally control how the stored procedure is executed. So I can't see the point of changing to a would-be "more predictable" method.[/i]Well, happy for you. My experience with method 2 was different: the stored procedures were exposed through APIs to other teams, and in some cases, to external customers or users eventually. It was not rare to see stored procedure with 20 optional parameters that could all be independently set, or not. The database team did not have any control of how they were executed, and we frequently faced outages because of such unexpected conditions. It was very difficult to explain that SQL server has its own logic we can't predict before testing, and this was not playing for a good reputation of the database team.[i]2. I tried to use method 3, but my execution plans were poor. Full scans, or big arrows, I can't achieve something clean.[/i]Most probably, you have not tried hard enough. :-) I usually noted two paths of improvements when facing such a problem: a) Jumping directly in the code without forethinking how this query should be solved, what indexes will trigger the best reduction in early results, when applying filter condition (usually happens with big arrows). b) SQL compiler sees a potential combination you have not thought about and makes the plan ready to execute it. Search for it, decide if you want to support it or disable it using a scalar expression. Usually happens when you see big scans in the execution plan you can't get rid of.[i]3. SQL Server team have never said their engine is designed to understand intents within method-3-like code. As a result, one day they may issue without warning a patch/release that makes this method not working anymore.[/i]This is indeed the weak point of this approach. I hope to have a response from a member of SQL Server team on this subject one day. However, I have strong reasons to believe this is not going to change overnight. First, they document how execution plans are cached and reused. Second, they have to ensure backward compatibility. But with what? It is well know for instance that if we put NEWID() in a SELECT statement, it will be executed once per row. Other functions and expressions such as GetUtcDate() won't. This shows how the functions and expressions are evaluated, and how the mess will be big if they break this behavior. So the day when method 3 won't work, a ton of code won't behave the same as well. SQL team can't make expressions evaluated once per query to suddenly be evaluated once per row.[i]4. I don't get the point of what is method 3. In some post a CTE is used, in another "OR" keywords... in what it is the same?[/i]Method 3 is not about using X or Y keyword. You can use any instruction in the SQL dictionary. The point of method 3 is to use scalar expressions to dynamically enable or inhibit portions of the same SQL statement, while having as many times the table you want to access in your statement as ways to access it. So if you plan for instance to have 3 ways to access the table MyTable on indexes IndexA, IndexB and ClustIndex, you will find at least three times "MyTable" in the code and the output of compilation, while at execution time only one operation is actually executed. When SQL is running the query (so, after compilation), pure scalar expressions such as "GetDate(), A=B, C>1+2" are evaluated before any object (table or index) is opened. Other expressions referring to columns (LastName Like 'A%') are evaluated for every row if they have not been transform into a SEEK operation, i.e. if SQL is not using an index to get the rows that match them.A expression evaluated as FALSE inhibits all the work bound to it. As a very simple exemple is to look at the execution plan of "SELECT * FROM [User] WHERE 1=0".==========================================================================CONCLUSIONI wanted to show that concatenating SQL strings is not the only way to get dynamic execution, there are others which are also viable in terms of stability, predictability and performance.I also wanted to show that you must be very careful when using concatenation of SQL strings, because you and your testers may not think about all the code paths your code allows; and each one of them produces a different execution plan. Depending in how your application is used and by who, those execution plan can be activated on production by a new combination of parameters without being reviewed or predicted by the database team.From my own team experience, we made terrific progress in stability and predictible performance when we put as a principle that *all* execution plans and combinations of variables have to be reviewed, even when the stored procedures allowed many input parameters. The application was using dynamically generated SQL code and it was a very difficult goal to achieve because it would have mean to test a lot of different combinations.When we switched to adopt only one execution plan per stored procedure, we could adopt a methodology which we can classify the 2 or 3 use cases we really want to optimize best, the others use cases for which moderate performance is acceptable, and most important, that there is no "leak" or possibility to trigger a very very bad execution plan leading to immediate outages.Wed, 26 May 2010 11:08:01 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Adam Gojdas (5/25/2010)[/b][hr]Something that often goes overlooked when using sp_executesql is explicitly using unicode strings. It is good to keep in mind the SQL and ParmDefinition parameters are of nvarchar datatype. In the article nvarchar was used to declare the @sSql, @sWhereClause, @ParmDefinition and @NewLine. But throughout there were non-unicode strings being assigned to them.So instead of doing this:[code="sql"] SET @sSql = 'SELECT [Name],' + @NewLine + ' [ProductNumber],' + @NewLine + ' [ListPrice],' + @NewLine + ' [ModifiedDate]' + @NewLine + ' FROM [Production].[Product]' + @NewLine + ' WHERE 1 = 1' -- Always true [/code]Using a unicode string as follows would be preferred:[code="sql"] SET @sSql = N'SELECT [Name],' + @NewLine + N' [ProductNumber],' + @NewLine + N' [ListPrice],' + @NewLine + N' [ModifiedDate]' + @NewLine + N' FROM [Production].[Product]' + @NewLine + N' WHERE 1 = 1' -- Always true [/code]Prepending the "N" will ensure it is a unicode string and avoid the implicit conversion that goes on. As a good practice this should be done where ever the hardcoded strings are assigned to the nvarchar datatypes.[/quote]Thanks, there are more details of why here: [url=http://support.microsoft.com/kb/239530]http://support.microsoft.com/kb/239530[/url]Tue, 25 May 2010 15:50:49 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxSomething that often goes overlooked when using sp_executesql is explicitly using unicode strings. It is good to keep in mind the SQL and ParmDefinition parameters are of nvarchar datatype. In the article nvarchar was used to declare the @sSql, @sWhereClause, @ParmDefinition and @NewLine. But throughout there were non-unicode strings being assigned to them.So instead of doing this:[code="sql"] SET @sSql = 'SELECT [Name],' + @NewLine + ' [ProductNumber],' + @NewLine + ' [ListPrice],' + @NewLine + ' [ModifiedDate]' + @NewLine + ' FROM [Production].[Product]' + @NewLine + ' WHERE 1 = 1' -- Always true [/code]Using a unicode string as follows would be preferred:[code="sql"] SET @sSql = N'SELECT [Name],' + @NewLine + N' [ProductNumber],' + @NewLine + N' [ListPrice],' + @NewLine + N' [ModifiedDate]' + @NewLine + N' FROM [Production].[Product]' + @NewLine + N' WHERE 1 = 1' -- Always true [/code]Prepending the "N" will ensure it is a unicode string and avoid the implicit conversion that goes on. As a good practice this should be done where ever the hardcoded strings are assigned to the nvarchar datatypes.Tue, 25 May 2010 12:29:09 GMTAdam Gojdashttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxThis is the actual code that ran to produce the execution plan in my last post:[code="sql"]DECLARE @Name NVARCHAR(100), @Color NVARCHAR(100), @ListPriceMax MONEY, @ListPriceMin MONEY, @ModifiedDateMin DATETIME, @ModifiedDateMax DATETIME; SELECT @Name = N'A%', @Color = N'G%', @ListPriceMax = $1500, @ListPriceMin = $500, @ModifiedDateMin = '20050101', @ModifiedDateMax = '20060101';SELECT *FROM [Product]WHERE (@Name IS NULL OR [Name] LIKE @Name)AND (@Color IS NULL OR [Color] 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)OPTION (RECOMPILE);[/code]Mon, 24 May 2010 15:58:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJames,Your increasingly complex nested ORs and ANDs (now in multiple combinations) are looking less and less like the original 'simple' CTE every time you post. I do understand the point you are trying to make, but you still have not provided a testable solution to the challenges laid down so far to illustrate your point, and so risk losing credibility here. I would invite you to solve the problem presented by Jonathan so we can truly compare like with like here.The wider point you are missing with recompilation (on the SQL Server 2008 version I keep mentioning) is that the optimiser builds a 'throw away' plan [i]taking advantage of the specific parameter values at the execution time[/i]. This results in a highly optimised plan, not based on start-up filters, but with the unused plan segments [i]removed entirely[/i].For example, on the million-row test, the optimiser produces a plan based on a true 3-way index intersection (with seeks on all indexes) and a key lookup on the cluster to fetch the remaining off-index columns: (Very nearly as efficient as the dynamic SQL).[img]http://www.sqlservercentral.com/Forums/Attachment6079.aspx[/img]I do disagree with most of your comments, but do not propose to restate facts already presented. The time has come for you to back up your statements with a robust code example - no bugs, no excuses - let's see how it runs for real!PaulMon, 24 May 2010 15:56:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote]Things become much more difficult when predicates are non-SARGable (necessitating index scans) or when ANDs are involved. The problem with an AND is that the net result of all the predicates is almost always smaller than any of the individual inputs. If one or more of the (non-NULL) search conditions are not very selective on the target index (or if the optimiser does not have good statistics) the plan produced can be very slow (even with RECOMPILE specified).[/quote]Indeed, but in appearance. I did not want to enter the details because I did not want to write an article within the article. But let's step back from the code, and let's try to think how the AND problem should be solved conceptually.Let's imagine you want to allow a search on the last name AND the first name. You can have an index on the last name only, another one on the first name only, and join the intermediate results. This works only if the selectivity is small in both columns. But if you search for "John Smith", unfortunately we can imagine that the intermediate results will be large on both first and last names, thus turning in a heavy IO query for a very small final set of results.In that case, the answer is very easy for everybody : you just use a covering index on both columns last and first name, i.e. you add the "first name" column to the "last name" index and that's it.You would probably blame the developer who does not use a two-columns index in this case. I would equally blame the developer who does not use a two-column index in the way I proposed to write the dynamic query. After all, it's only different ways to type it, but at the end of the story, SQL compiler should end up with a single index seek, with "seek" (and not "predicate") on the two columns right? So whatever method you use, this is the goal to achieve.So this writing actually reflects this logic.[code]WHERE-- Last name onlyUserId IN (SELECT UserId FROM Users WHERE LastName = @LastName AND @LastName IS NOT NULL AND @FirstName IS NULL)-- Last name and first nameOR UserID IN (SELECT UserId FROM Users WHERE LastName = @LastName AND FirstName = @FirstName AND @LastName IS NOT NULL AND @FirstName IS NOT NULL)-- First name onlyOR UserID IN (SELECT UserId FROM Users WHERE FirstName = @FirstName AND @FirstName IS NOT NULL)[/code]The bottom line of the "AND" problem is that you have two types of "AND" conditions : the one you will solve by a covering or multi-column index (or an indexed view if the columns are not in the same table), and the one you will not optimize because the selectivity of each criteria separately is enough and does not worth the price in disk space. For instance, you may decide that it's not worth it to have a covering index on the last name and the email, because the email is selective enough. Again, you will blame the developer who creates such an index.To say it differently, all the "ANDs" (intersections) have to be applied before the "ORs" (unions). Intersections after unions can turn into very bad performance if sets are large. It seems logical that you have to start with the smallest sets possible from the beginning of the execution plans, i.e. out of SQL indexes.By the way, in case you were thinking about it, there is a reason why you cannot write a statement like this :[code]OR UserID IN (SELECT UserID FROM Users WHERE @LastName = LastName AND (@FirstName IS NULL OR FirstName = @FirstName))[/code]In that case, the LastName will be performed with a seek, and the FirstName will be performed with a predicate; again because SQL will have to choose a plan, not knowing the value of the parameters. It may seems redundant writing, I admit it (you can optimize by using bitwise flags or other techniques if the parameters get too many). But the way to read the total unverse of possibilities, is to list on one hand each case on which you support optimization is actually written down and checking that there is a part of the execution plan that supports it, and on the other hand which queries are an extension of optimized ones, plus basic filtering. My personal experience was that it is way more predictible and easy to have a global view of one stored procedure, while I could never trust dynamic SQL to 100%. When a developer was using dynamic SQL and I asked him what was optimized and what was not, most of the time the answer I get was about the cases he actually tested, but not the total universe of combinations admitted by such a query.About the RECOMPILE statement, this was not a possibility we could afford in our application. We had to handle something like 4000 batches per second and dealing with recompilation at each call was not thinkable.[quote]The fact is that we might infer that SQL Server does do some short-circuiting in some circumstances (start-up filters perhaps) but it is not documented or guaranteed. Given that much better alternatives exist, it strikes me as being extremely poor practice to rely on this sort of "by-inspection" behaviour.[/quote]I don't like the word "short-circuit" because it sounds like a hack, like abusing of some behavior which is not on purpose. I prefer to consider that SQL engine processes scalar logic before set logic, and consider this is a strong design architecture of the execution plan compiler that you can rely your code on, exactly as you will write things like "if (c != null && c.Property = 1)" in C language so you cannot get null pointer exception (while it did not work in VB6). The point of the documentation is relevant though, because it raises the question about knowing for how long this is going to be true. I do not consider this practice as poor, or I will have difficulties to understand how a poor practice succeeded in putting a database project and system in a more stable, predictable and manageable direction than a good one. As I've said in my first post in this thread, I do not recommend the dynamic SQL statement approach, for the reasons I've mentionned, and based on the experience I had and the objectives our team had to meet, which were basically very high performance and reliability metrics. If some people still wants to use it because in their case it's more convenient or agile, well I think that the weak point of this approach is its lack of predictibility which has to be compensated by very strong testing -- and even though, I do not sleep entirely peacefully. :)Mon, 24 May 2010 14:58:57 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxThe approach advocated by James is much more suited to simpler requirements based on OR conditions, as his second example shows.The first submission attempted to apply the technique to a query using ANDed predicates, and turns out to be problematic even when the bugs are removed. Essentially, the idea relies on Start-Up Filters to re-use a generic query plan, but to avoid executing branches that are not needed at run time.The concept is appealing, and I did play around with this several years ago when 2005 was new. It is perfectly possible to produce an efficient plan when all the predicates are SARGable and linked with OR. This is because the keys contributing to each input of the concatenation will always be required, and the effect is similar to an optimised index intersection.Things become much more difficult when predicates are non-SARGable (necessitating index scans) or when ANDs are involved. The problem with an AND is that the net result of all the predicates is almost always smaller than any of the individual inputs. If one or more of the (non-NULL) search conditions are not very selective on the target index (or if the optimiser does not have good statistics) the plan produced can be very slow (even with RECOMPILE specified).In the case presented by Jonathan (the million-row table) it is just about impossible to find a general solution based on start-up filters that performs anywhere near as well as dynamic SQL or the optimal code written for 2008 SP1 CU5 onward with RECOMPILE specified. Some inputs can be made to work reasonably well so long as all the non-NULL conditions are very selective, or if extensive hinting is applied (e.g. FORCE ORDER, LOOP JOIN, RECOMPILE, MERGE UNION...and so on).The start-up filter idea also struggles with some range conditions, since COALESCE just expands to a non-SARGable CASE statement before optimisation. An index scan might be fine on a small table, but it hurts performance severely on the million-row example. It is possible to make the search conditions on Modified Date and List Price SARGable by using a hard-coded constant with ISNULL to fix the lower and upper bounds of the range (e.g. ListPrice <= ISNULL(@ListPriceMax, 922337203685477.5807)) but this is not very satisfactory.In summary, I would avoid this method unless your requirements are for simple query conditions joined exclusively by ORs, with no range conditions. Anyone using 2008 SP1 CU5 or later should ignore the method completely and use dynamic SQL or code based on the following model:[code="sql"]-- SQL 2008 SP1 CU5 onward ONLYSELECT *FROM [Product]WHERE (@Name IS NULL OR [Name] LIKE @Name)AND (@Color IS NULL OR [Color] 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)OPTION (RECOMPILE);[/code]PaulMon, 24 May 2010 13:33:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Gianluca Sartori (5/24/2010)[/b][hr][quote][b]josh shilling (5/21/2010)[/b][hr]See this post by Mark Cohen...[/quote]That page contains quite questionable statements. It's a shame it still comes in the first Google page for "sql short-circuit". I'll try to finish soon my article on short-circuiting and (hopefully) get it published here.[/quote]More than questionable, as I tried to show with the code snippet I posted.The fact is that we might infer that SQL Server does do some short-circuiting in some circumstances (start-up filters perhaps) but it is not documented or guaranteed. Given that much better alternatives exist, it strikes me as being extremely poor practice to rely on this sort of "by-inspection" behaviour. If you need a reviewer or second contributor for your article, send me a PM.PaulMon, 24 May 2010 08:26:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Jeff Moden (5/23/2010)[/b][hr]Heh... I actually forgot to mark the article before. 5 stars it is.;-)[/quote]Thanks Jeff, a bit of balance has been restored to The Force :-)Mon, 24 May 2010 08:22:29 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]James Dingle-651585 (5/24/2010)[/b][hr][i]It is difficult to demonstrate for me now because I am not at the office, the computer I use now does not have SQL... sorry if I take time to get there :)[/i][/quote]James, Ok, I'll be interested to see it working. Could you use the test data in my reply [url=http://www.sqlservercentral.com/Forums/FindPost926555.aspx]here[/url] with only @Name set to a value which is searched using '=' and with 'LIKE' for colour and inequalities for the price and dates i.e. logically equivalent to:[code="sql"]SELECT * FROM [Product] WHERE [Name] = Coalesce(@Name, [Name]) AND [Color] LIKE Coalesce(@Color, [Color] ) AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice]) AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice]) AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate) AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)[/code]Mon, 24 May 2010 07:37:34 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxBy the way Jon, I think I understand why you have scans everywhere while trying the method in my post above: it's a bug in my code. Indeed, there is still the possibility that all of the parameters are NULL, and SQL compiler takes it in account (in that case, the "WHERE ProductID IN ()" subquery gets inhibited, thus fetching all the rows, and then the scan is the most stable execution plan in all combinations including this latter one.Try adding this in the main WHERE clause:[code]... AND NOT (@Name IS NULL AND @Colour IS NULL AND /* rest of the parameters */ )[/code]This is saying explicitly to the compiler that "among those parameters, at least one is mandatory".I observed the compiler really understands it and removes the scan if this is the case here.If it changes your execution plan the way you expect, I hope I will have showed how scalar expressions can really drive execution plans, and consequently what you can do with such an ability. At least you would have seen by yourself the disastrous result of one very specific use case immediately, while using dynamic queries you would have to hit that case to see it; and that is what I am trying to explain. ;)Mon, 24 May 2010 05:17:39 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[i]It is difficult to demonstrate for me now because I am not at the office, the computer I use now does not have SQL... sorry if I take time to get there :)[/i]Though this is what the final EP looks like, with such a query. Note that the syntax is way different from the CTE, but the principle is the same.[code]Select *From [User] uWhere ( Id In (Select Id From [User] Where @FirstName = FirstName And @FirstName Is Not Null) Or Id In (Select Id From [User] Where @LastName = LastName And @LastName Is Not Null) Or Id In (Select Id From [User] Where @Email = Email And @Email Is Not Null) Or Id = @Id)[/code]Actually in this very specific example, the conditions "And @FirstName Is Not Null" are optional because nothing can be [i]equal to[/i] Null. But I encourage developers to write it down anyway so the dynamic triggering condition is explicit and though more readable.This is the execution plan:[img]http://www.sqlservercentral.com/Forums/Attachment6068.aspx[/img]Actually the execution plan will differ from the implementations (merge join, loop joins, etc). The principle is to have no big scan remaining which is not on purpose.At least, if we still can discuss about the ways to get there :), it shows the interest of having everything in one single eyeshot. It shows all the possible paths of execution SQL can take, keeping in mind that because of scalar resolution most of the time only one path will be executed, while there will be only one EP in the cache plan to handle all possibilities.This is helpful when driving a project, because it is more simple for the developer, the DBA, and the team leader to review the query entirely, look at what has been optimized with indexes (maybe we don't want to optimize everything for disk space reasons), and pinpoint the nasty corner cases ("ugh! did you see you have an index scan when no parameter is given? Is this what you want?"). The predictability is immediate, while I found that using dynamic concatenated SQL statements forces to review each one of them, and the number of possible combinations makes that one day, some use case is missed and performs badly on production.Mon, 24 May 2010 04:51:31 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]James Dingle-651585 (5/24/2010)[/b][hr]Hi Jon,Quick remarks:- There is a clustered index scan on the ID. Obviously, this way it won't work. The snippet of code I've wrote was assuming that ProductID is the clustered unique index (usually the PK) of the table.[/quote]ProductID is the clustered PK, see the script to populate the table.[quote]- You have to add the indexes on the columns on which the filtering is done. In the execution plan above, obviously there is no index on "Name" and so on. So it is not possible to figure out how and why it works.[/quote]There is an index on Name, see the table population script.[quote]- Don't trust too much percentages to define a query to be good or bad. Trust CPU time and # of reads. I never knew what those percentages were about exactly and by experience found that they help to focus attention on bottlenecks. But what really matters is bottom line CPU and IO.[/quote]I agree, you can't trust these percentages too much.[quote]- My point was not really about performance, but more about what dynamic queries based on concatenated SQL gives in a real environment, within the lifetime of a project. I used it once, and with the experience of it and others techniques, I went away from dynamic SQL.[/quote]I think there are other possible ways to allow the need for maintenance of dynamic SQL to be nearly as easy to detect as it is on static procedures, maybe by listing all the tables and columns used in the dynamic sql in a query at the end of the procedure that would never be excecuted so the procedure would no longer compile if any of the columns were changed, I started just by adding a non-executing select of the table name so it would be detected by sp_depends if you wanted to check which stored procedures would need changing if a table were changed.[quote][b]My point was to show that it is possible to rely on scalar variable resolution to make some part of the query to be executed or not, and thus triggering dynamic execution without putting some code within quotes[/b].[/quote]I'm not sure that you have shown that also, to me, it seems a more complicated method than using dynamic sql.Mon, 24 May 2010 04:26:44 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Jonathan AC Roberts (5/23/2010)[/b][hr]James Dingle,In fact your query preforms nearly twice as badly as the original non-dynamic SQL that would have been in the initial stored procedure.[...]See SQL and execution plan below.Execution plans:[img]http://www.sqlservercentral.com/Forums/Attachment6058.aspx[/img]Query 1 uses 35% of total cost and query 2 uses 65% of total cost.[/quote]Hi Jon,Quick remarks:- There is a clustered index scan on the ID. Obviously, this way it won't work. The snippet of code I've wrote was assuming that ProductID is the clustered unique index (usually the PK) of the table.- You have to add the indexes on the columns on which the filtering is done. In the execution plan above, obviously there is no index on "Name" and so on. So it is not possible to figure out how and why it works.- Don't trust too much percentages to define a query to be good or bad. Trust CPU time and # of reads. I never knew what those percentages were about exactly and by experience found that they help to focus attention on bottlenecks. But what really matters is bottom line CPU and IO.- My point was not really about performance, but more about what dynamic queries based on concatenated SQL gives in a real environment, within the lifetime of a project. I used it once, and with the experience of it and others techniques, I went away from dynamic SQL. As usal, in database world, "it depends". So if some people fell comfortable with this approach, let them use it. [b]My point was to show that it is possible to rely on scalar variable resolution to make some part of the query to be executed or not, and thus triggering dynamic execution without putting some code within quotes[/b].Mon, 24 May 2010 02:51:38 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]josh shilling (5/21/2010)[/b][hr]Try flipping the (Field LIKE @Param OR @Param IS NULL) To(@Param IS NULL OR FIELD Like @Param)See this post by Mark Cohen...http://beingmarkcohen.com/?p=62[/quote]That page contains quite questionable statements. It's a shame it still comes in the first Google page for "sql short-circuit". I'll try to finish soon my article on short-circuiting and (hopefully) get it published here.Mon, 24 May 2010 02:08:49 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxAnd just one more before anyone else suggests it:[code="sql"]-- Query 10SELECT * FROM [Product] WHERE NOT([Name] <> @Name AND @Name IS NOT NULL)[/code] the where clause above is actually logically identical to: [code="sql"]WHERE ([Name] = @Name OR @Name IS NULL)[/code][img]http://www.sqlservercentral.com/Forums/Attachment6059.aspx[/img]Gives the same full table scan as the other queries.Sun, 23 May 2010 18:15:28 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]Paul White NZ (5/21/2010)[/b][hr][quote][b]clementhuge (5/21/2010)[/b][hr]I feel the article is actually pretty poor. All the dbas know this kind of dynamic sql. Someone suggested recompile and with (optimize) option and he is spot on! I was able to improve performance on parameterized query with the optimize option.[/quote]Many DBAs *think* they know - big difference.The best you can do with any form of static code is recompile every time, and even that only works satisfactorily in SQL Server 2008 SP1 with at least Cumulative Update 5 applied, and with correct coding. Even so, recompiling on every execution (and not caching any plan!) is not always a good 'fix'.If I could, I'd go back and give this article 5 stars now, just to make up for those that think they know better.Paul[/quote]Heh... I actually forgot to mark the article before. 5 stars it is.;-)Sun, 23 May 2010 16:29:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJames Dingle,In fact your query preforms nearly twice as badly as the original non-dynamic SQL that would have been in the initial stored procedure.See SQL and execution plan below.[code="sql"]DECLARE @Name nvarchar(100), @Color nvarchar(100), @ListPriceMax money, @ListPriceMin money, @ModifiedDateMin datetime, @ModifiedDateMax datetime SET @Name = 'Hello'SET @Color = NULLSET @ListPriceMax = NULLSET @ListPriceMin = NULLSET @ModifiedDateMin = NULLSET @ModifiedDateMax = NULL--Query 1SELECT * FROM [Product] WHERE [Name] = Coalesce(@Name, [Name]) AND [Color] LIKE Coalesce(@Color, [Color] ) AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice]) AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice]) AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate) AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)--Query 2;WITH Filtering AS( SELECT ProductId FROM [Product] WHERE @Name IS NOT NULL AND @Name = Name UNION ALL SELECT ProductId FROM [Product] WHERE @Color IS NOT NULL AND @Color = Color UNION ALL SELECT ProductId FROM [Product] WHERE NOT (@ListPriceMin IS NULL AND @ListPriceMax IS NULL) AND (@ListPriceMin IS NOT NULL OR ListPrice >= @ListPriceMin) AND (@ListPriceMax IS NOT NULL OR ListPrice <= @ListPriceMax))SELECT * FROM [Product] WHERE ProductID IN (SELECT ProductID FROM Filtering) AND [Name] = Coalesce(@Name, [Name]) AND [Color] LIKE Coalesce(@Color, [Color] ) AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice]) AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice]) AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate) AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)[/code]Execution plans:[img]http://www.sqlservercentral.com/Forums/Attachment6058.aspx[/img]Query 1 uses 35% of total cost and query 2 uses 65% of total cost.Sun, 23 May 2010 16:02:06 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJames Dingle,I've tried your style of query to obtain the execution plan and it is the worst performing out of all the attempts so far.Please find details below. I created a table with 1 million rows to test it:Create test data:[code="sql"]IF OBJECT_ID('Product','U') IS NOT NULL DROP TABLE [Product]IF OBJECT_ID('Colors','U') IS NOT NULL DROP TABLE [Colors]CREATE TABLE [Product]( [ProductId] int IDENTITY(1,1) NOT NULL, [Name] nvarchar(100) NOT NULL, [Color] nvarchar(100) NOT NULL, [ListPrice] money NOT NULL, [ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)) CREATE TABLE Colors(Color nvarchar(100))INSERT INTO Colors(Color) VALUES('Amber'),('Beige'),('Black'),('Blue'),('Brown'),('Dark blue'),('Dark brown'),('Dark coral'),('Gold'),('Green'),('Indigo'),('Ivory'),('Light blue'),('Light brown'),('Pale blue'),('Pale brown'),('Pastel blue'),('Pastel brown'),('Pastel green'),('Red'),('Rose'),('White'),('Yellow') INSERT INTO Product( Name, Color, ListPrice, ModifiedDate)SELECT TOP 1000000 A.name + '-' + CAST( ROW_NUMBER() over(order by NEWID()) as nvarchar(100)), B.Color, ABS(CHECKSUM(NEWID())) % 5000, Cast('2000-01-01' as datetime) + ABS(CHECKSUM(NEWID())) % 3000 FROM master.sys.columns A CROSS JOIN Colors B CROSS JOIN master.sys.columns C CREATE NONCLUSTERED INDEX Product_X1 ON Product(Name) CREATE NONCLUSTERED INDEX Product_X2 ON Product(ModifiedDate) CREATE NONCLUSTERED INDEX Product_X3 ON Product(Color)GO[/code]Run the queries (Your's are Query 7 & 8)[code="sql"]DECLARE @Name nvarchar(100)SET @Name = 'Hello'-- Query 1SELECT * FROM [Product] WHERE (@Name IS NULL OR [Name] = @Name) -- Query 2SELECT * FROM [Product] WHERE ([Name] = @Name OR @Name IS NULL) -- Query 3SELECT * FROM [Product] WHERE [Name] = Coalesce(@Name, [Name])-- Query 4SELECT * FROM [Product] WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END-- Query 5SELECT * FROM [Product] WHERE 1 = (CASE WHEN @Name IS NULL THEN 1 WHEN @Name IS NOT NULL AND [Name] = @Name THEN 1 ELSE 0 END)-- Query 6SELECT * FROM [Product] WHERE [Name] = ISNULL(@Name, [Name]) DECLARE @Color nvarchar(100), @ListPriceMax money, @ListPriceMin money, @ModifiedDateMin datetime, @ModifiedDateMax datetime SET @Color = NULLSET @ListPriceMax = NULLSET @ListPriceMin = NULLSET @ModifiedDateMin = NULLSET @ModifiedDateMax = NULL-- Query 7;WITH Filtering AS( SELECT ProductId FROM [Product] WHERE @Name IS NOT NULL AND @Name = Name UNION ALL SELECT ProductId FROM [Product] WHERE @Color IS NOT NULL AND @Color = Color UNION ALL SELECT ProductId FROM [Product] WHERE NOT (@ListPriceMin IS NULL AND @ListPriceMax IS NULL) AND (@ListPriceMin IS NOT NULL OR ListPrice >= @ListPriceMin) AND (@ListPriceMax IS NOT NULL OR ListPrice <= @ListPriceMax))SELECT *FROM [Product]WHERE ProductID IN (SELECT ProductID FROM Filtering) AND [Name] = Coalesce(@Name, [Name]) AND [Color] LIKE Coalesce(@Color, [Color] ) AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice]) AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice]) AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate) AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)-- Query 8;WITH Filtering AS( SELECT ProductId FROM [Product] WHERE @Name IS NOT NULL AND @Name = Name)SELECT *FROM [Product]WHERE ProductID IN (SELECT ProductID FROM Filtering) AND [Name] = Coalesce(@Name, [Name])-- Query 9SELECT * FROM [Product] WHERE [Name] = @Name[/code]And here is a picture of all the execution plans:[img]http://www.sqlservercentral.com/Forums/Attachment6056.aspx[/img][img]http://www.sqlservercentral.com/Forums/Attachment6057.aspx[/img]Your query without any other columns included in it ([i]Query 8[/i]) is as good as the dynamic sql but as soon as some of the other columns are added ([i]Query 7[/i]) it becomes the worst performing out of all of them. The dynamic sql ([i]Query 9[/i]) would only include [i]WHERE [Name] = @Name[/i] if the other columns are null but your's would have all the columns listed as it is not dynamic.Sun, 23 May 2010 14:09:34 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxI have experimented the approach above for a couple of years in a production system.While it seems to simplify development on a very first time, personnally now I do not recommend the use of dynamic SQL in heavy OLTP environments, for those reasons :1) The execution plans are not easily predictible and there is a lot of possible code paths. The developers will usually work with 2 or 3 use cases, but not a hundred. It's easy to miss a combination of parameters that is very slow, contains some index scan, or whatever you haven't thought about. This lack of predictability have to be compensated by a lot of tests or by a very close survey of the production machines. But it's a shared resource, most of the time. Even a single slow running corner case combination of parameters executed once per 5 second can choke the whole system.2) Considering an application has to be stable over time and modifications, the consequence of the problem above is that it gets worse over time, taking in account developers turnover. The natural future of this application is to have so many "IFs" that you do not really know which one is used or not.3) Since there is some SQL within quotes, there is always a probability that, over time and modifications, it does not compile in some corner cases you have not seen in tests.4) It gets complicated to have a good index structure to handle all those use cases. The tendency is then to have too many or too large indexes.5) It should not be a reason, but I have seen SQL servers on production crashing because they were not able to compile a execution plan in some corner case of such dynamic queries. I have opened cases to Microsoft SQL customer support for those.6) The only reason why people use it is that they were unsuccessful with the former approach, but without really understanding why the SQL compiler was not giving what they wanted. But if they really understood what was problematic, I bet they would like to solve it in a more stable way.Let's take the original query as an example[code]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)[/code]If you look at the execution plan, you will see a clustered index scan on this table, and you will be a little puzzled about why. The reason is that SQL can't choose an index because one optimized query for one criteria will perform very poorly with another criteria. In addition to this, in this example, the COALESCE function forces a computation for every row, thus impeaching the use of indexes. As a SQL developer, you have to understand all this.You can help SQL in choosing its indexes by just adding the CTE below:[code]WITH Filtering As(SELECT ProductIdFROM [Production].[Product]WHERE @Name IS NOT NULL AND @Name = NameUNION ALLSELECT ProductIdFROM [Production].[Product]WHERE @Colour IS NOT NULL AND @Colour = ColourUNION ALLSELECT ProductIdFROM [Production].[Product]WHERE NOT (@ListPriceMin IS NULL AND @ListPriceMax IS NULL)AND (@ListPriceMin IS NOT NULL OR ListPrice >= @ListPriceMin)AND (@ListPriceMax IS NOT NULL OR ListPrice <= @ListPriceMax)-- ...)SELECT [Name], [Color], [ListPrice], [ModifiedDate]FROM [Production].[Product]WHERE ProductID In (SELECT ProductID From Filtering)AND [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)[/code]As an outcome you have all the possible combinations of indexes in one single execution plan, and then it is way more convenient to optimize which indexes you want, do not want, are still useful, and so on.On other environments on which the compilation duration is not really a problem, and not exposed to users directly, I advise to keep the former statement and use RECOMPILE option, this is usually more accessible to developers - though it forces DBAs to look at slow queries more often.The key tricks to understand here are:- Having one statement per optimized case (in the CTE, separated by UNION ALL) allows SQL to choose the proper index for each one. A simple look at the execution plan is enough to realize it.- It's important to have "WHERE @Name IS NOT NULL" statements in the Filtering CTE, because if it happens that @Name IS NULL, then SQL will not perform any read on this part of the union (scalar expression are evaluated [u]before[/u] any fetching). So even if the execution plan looks bigger, actually the # of reads will remain economic. [b]It is written as a static query, but it is executed as a dynamic one.[/b]Having experimented it for two years in a team, there was a serious improvement in quality of deliverables from developers. After some training, most of the time the first shot was working properly enough to get on production.Sat, 22 May 2010 10:32:22 GMTJames Dingle-651585http://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxI thought I'd better put some tests in after all the discussion.Create some test data:[code="sql"]IF OBJECT_ID('Product','U') IS NOT NULL DROP TABLE [Product]IF OBJECT_ID('Colors','U') IS NOT NULL DROP TABLE [Colors]CREATE TABLE [Product]( [ProductId] int IDENTITY(1,1) NOT NULL, [Name] nvarchar(100) NOT NULL, [Color] nvarchar(100) NOT NULL, [ListPrice] money NOT NULL, [ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)) CREATE TABLE Colors(Color nvarchar(100))INSERT INTO Colors(Color) VALUES('Amber'),('Beige'),('Black'),('Blue'),('Brown'),('Dark blue'),('Dark brown'),('Dark coral'),('Gold'),('Green'),('Indigo'),('Ivory'),('Light blue'),('Light brown'),('Pale blue'),('Pale brown'),('Pastel blue'),('Pastel brown'),('Pastel green'),('Red'),('Rose'),('White'),('Yellow') INSERT INTO Product( Name, Color, ListPrice, ModifiedDate)SELECT A.name + '-' + CAST( ROW_NUMBER() over(order by NEWID()) as nvarchar(100)), B.Color, ABS(CHECKSUM(NEWID())) % 5000, Cast('2000-01-01' as datetime) + ABS(CHECKSUM(NEWID())) % 3000 FROM master.sys.columns A CROSS JOIN Colors B CREATE NONCLUSTERED INDEX Product_X1 ON Product(Name) CREATE NONCLUSTERED INDEX Product_X2 ON Product(ModifiedDate) CREATE NONCLUSTERED INDEX Product_X3 ON Product(Color)GO[/code]I've been through the discussion and put in all the suggestions I could find:[code="sql"]DECLARE @Name nvarchar(100)SET @Name = 'Hello' SELECT * FROM [Product] WHERE (@Name IS NULL OR [Name] = @Name) SELECT * FROM [Product] WHERE ([Name] = @Name OR @Name IS NULL) SELECT * FROM [Product] WHERE [Name] = Coalesce(@Name, [Name])SELECT * FROM [Product] WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDSELECT * FROM [Product] WHERE 1 = (CASE WHEN @Name IS NULL THEN 1 WHEN @Name IS NOT NULL AND [Name] = @Name THEN 1 ELSE 0 END)SELECT * FROM [Product] WHERE [Name] = ISNULL(@Name, [Name]) SELECT * FROM [Product] WHERE [Name] = @Name[/code]Only the final query uses an index and is how the dynamic SQL would be written.Fri, 21 May 2010 22:46:22 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[code="sql"]DECLARE @Param INTEGER;SET @Param = NULL;SELECT *FROM master.dbo.spt_values VWHERE (@Param IS NULL)OR (V.number = 999)OR (SQRT(COALESCE(@Param, -1)) = 0);SELECT *FROM master.dbo.spt_values VWHERE (@Param IS NULL)OR (V.number = 999)OR (SQRT(ISNULL(@Param, -1)) = 0);[/code]Fri, 21 May 2010 13:44:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxTry flipping the (Field LIKE @Param OR @Param IS NULL) To(@Param IS NULL OR FIELD Like @Param)See this post by Mark Cohen...http://beingmarkcohen.com/?p=62Fri, 21 May 2010 12:44:43 GMTjosh shillinghttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]clementhuge (5/21/2010)[/b][hr]I feel the article is actually pretty poor. All the dbas know this kind of dynamic sql. Someone suggested recompile and with (optimize) option and he is spot on! I was able to improve performance on parameterized query with the optimize option.[/quote]Many DBAs *think* they know - big difference.The best you can do with any form of static code is recompile every time, and even that only works satisfactorily in SQL Server 2008 SP1 with at least Cumulative Update 5 applied, and with correct coding. Even so, recompiling on every execution (and not caching any plan!) is not always a good 'fix'.If I could, I'd go back and give this article 5 stars now, just to make up for those that think they know better.PaulFri, 21 May 2010 12:32:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJust to add my two cents... I loved this article. It's well written, the code is formatted for easy readability. Topics were exposed in the correct order. The narrative made sense. Etc. Like Paul White, the only reason I didn't give it 5 stars is because proof of performance was never exposed in the form of test code.I have to say much the same about most of the discussions on this thread... lots of ideas and claims but no proof in code (except for a couple of notable links with code that should still be tested just to be sure). My recommendation to all is that if you have a claim of performance or a claim that an index will (or will not) be preperly used, post the code to build a million row test harness for your claim and the results of your testing before you make such a claim. ;-)"A Developer must not guess... A Developer must KNOW."Fri, 21 May 2010 12:17:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]shaytoder (5/20/2010)[/b][hr]my mistake,for some reason i thought this approach will use seeks and not scans,but now i tried it again, and i see scans.sorry...:blush:[/quote]That's been the common problem throughout this thread... lot's of claims... no coded proof of claims. I'm not sure who first said it but "One test is worth a thousand expert opinions." :-)Thanks for the test you did and the feedback. It's a good person who can admit that a mistake was made. Well done.Fri, 21 May 2010 12:06:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxI feel the article is actually pretty poor. All the dbas know this kind of dynamic sql.Someone suggested recompile and with (optimize) option and he is spot on!I was able to improve performance on parameterized query with the optimize option.BestFri, 21 May 2010 11:44:41 GMTclementhugehttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]chris.shaw (5/21/2010)[/b][hr]Jonathan, I must say that I found your article to have some really good information in it. I guess there was only a couple comments to it that would concern me. 1) If I understand what you are saying it sounds like yuo have a task that you needed to complete. You tried a number of ways to do that, however yu found that Dynamic SQL was working better. This was not your first options and well you found that the Dynamic SQL worked well. Is it safe to say you recommend that it could be used with static stored proces are not cutting it.Chris Shaw[/quote]Chris,The particular query was timing out as the table it was querying had gradually grown to about 20 million rows. I found out that it would be a potential solution by taking the static SQL that was timing out and rewriting it to take out the unnecessary predicates that tested where the parameters were passed in as null. There are several things to consider when using Dynamic SQL, procedures using it have a greater overhead they are concatenating strings when creating SQL and doing more internal procedure calls, but the overhead is micro-seconds so when compared to the execution time of the query (several seconds in this case) it becomes insignificant. It is also only useful if the SQL you are executing needs to be changed depending on the parameters, but it can be very useful for making certain types of procedure perform better and also increasing the versatility of procedures. JonathanFri, 21 May 2010 05:31:32 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJonathan, I must say that I found your article to have some really good information in it. I guess there was only a couple comments to it that would concern me. 1) If I understand what you are saying it sounds like yuo have a task that you needed to complete. You tried a number of ways to do that, however yu found that Dynamic SQL was working better. This was not your first options and well you found that the Dynamic SQL worked well. Is it safe to say you recommend that it could be used with static stored proces are not cutting it.Chris ShawFri, 21 May 2010 00:47:13 GMTSQLShawhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]CirquedeSQLeil (5/20/2010)[/b][hr][quote][b]Atif Sheikh (5/20/2010)[/b][hr]Dynamic SQL is the last thing to do. [/quote]Sometimes Dynamic SQL is quite useful. There are alternatives to using case statements or dynamic sql (such as child procs with the different variations of the code to execute).[/quote]100% agree. But I think that there are different Execution Plans when use "LIKE" operator and "=" operator. If we use LIKE operator, it always goes for Index Scan (As in the dynamic query in the article). But if we use the same Dynamic query with "=", it gives different execution plan (atleast on my PC with SQL Server 2005 Standard edition).Thu, 20 May 2010 22:46:41 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspxJust wanted to say thanks! I spent the day breaking a big SP that handled 8 different filters into 8 SPs, eliminating all need to have NULL parameters. We have about 1.5 million records, and the sum total time to run is now about half of what it was previously. I will definitely keep this (don't use NULL parameters if at all possible) in mind for future SPs.Thu, 20 May 2010 19:30:37 GMTtigriswoodshttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]jack.erdey (5/20/2010)[/b]Enter the table-value-function:Wrap your table SELECTs in TVF's. Remove all external CRUD permissions from table objects.Create dynamic, parameterized SQL in your DAL against the TVF's, and SQL Server (2005+) will create optimize planes and reuse cached plans, and give you the best performance (specially when parameterized).[/quote]Do you really wrap basic SELECTs, meaning that there is no row-level permission, input parameters, joins, column exclusion, calculations or other logic behind it, to a single table into a TVF? If so, what's the point? Why not just grant SELECT permissions ot the table itself?Thu, 20 May 2010 17:07:08 GMTNils Gustav Stråbøhttp://www.sqlservercentral.com/Forums/Topic924792-2691-1.aspx[quote][b]jack.erdey (5/20/2010)[/b]Unless you have defined your SP with RECOMPILE, you will run into a performance wall with large table/multi-join queries because the first "dynamic" SQL plan will be used from cache. (speaking from experience). Please try it.[/quote]And how exactly is this different from plan caching in your TVF approach? A plan is a plan, and sometimes the plan is sub-optimal, no matter if you use dynamic SQL, stored procedures or your TVF approach.Thu, 20 May 2010 16:59:26 GMTNils Gustav Stråbø