InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 07:31:04 GMT20http://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]UncleJimBob (8/31/2010)[/b][hr]I ran into performance issues caused by parameter sniffing so many times using this type of constructat work that I had to stop using it.Now I use this:declare @local_firstname nvarchar(50)declare @local_lastname nvarchar(50)select @local_firstname = isnull(@firstname,''), @local_lastname = isnull(@lastname,'')SELECT ID, FirstName, LastName FROM PeopleWHEREFirstName LIKE '%' + @local_firstname + '%'andLastName LIKE '%' + @local_lastname + '%'It's more work up front but:a) makes the query easier to understand (and therefore maintain) andb) avoids slow queries caused by the use of inappropriate query plans via parameter sniffingJust my 2c[/quote]I've also found it necessary to use a variable in the sql, rather than the parameter from the header, due to parameter sniffing. As indicated, a little up-front work assingning a value to the variable can save you a lot of headache. Specifically I find this useful for date-related reports. Users can run a report and specify a start and end date, but they can leave them blank, or null, and sql will return a month-to-date, or previous month report. This is very useful for report schedulers, such as Crystal Reports. Crystal does not provide a way to pass a 'today' parameter, so I create the stored procedure to accept a null, and then use ISNULL (in a simple SET or SELECT, not in the 'main' query) to assign a variable using a FirstOfMonth function. The I use that variable in my 'main' query.Thu, 02 Sep 2010 08:06:39 GMTEric L Hacketthttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote]That or simply redeclaring local varaibles for each parameter and setting the local variable to the parameter.[/quote]Yes, thats exactly what I do :-)Wed, 01 Sep 2010 15:06:49 GMTcantorhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]UncleJimBob (8/31/2010)[/b][hr]I ran into performance issues caused by parameter sniffing so many times using this type of constructat work that I had to stop using it.[/quote]TRACEFLAG 4136 will be your friend.That or simply redeclaring local varaibles for each parameter and setting the local variable to the parameter.either will get you around Microsoft's silly new Parameter sniffing feature that actually hurts performance more than it helps.Wed, 01 Sep 2010 07:30:04 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]David.Poole (8/31/2010)[/b][hr]Why not use sp_ExecuteSQL and build up the query that is needed in T-SQL code. This will cache the execution plan for the query statement.The other thing I would suggest is that if you are going to using multiple LIKE '%[some string]% then you consider using full text indexes.[/quote]Unfortunately Full Text Indexing doesn't handle middle of a string search like LIKE does.It seems to only handle the LIKE 'sometext%' type searches... and not inner text type searches.Or at least that's what it did in SQL 2005 and earlier.Wed, 01 Sep 2010 07:27:34 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxThanks for the article.Tue, 31 Aug 2010 20:24:36 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxI just used the example given, I have yet to use a like clause in any of my code, the focus of the comment is on the performance issues around the use of optional paramaters in a stored procedure.hthTue, 31 Aug 2010 18:22:57 GMTcantorhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxuse fulltext search. its fast, its adaptable and its simpleTue, 31 Aug 2010 18:19:40 GMTasirakyhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxI ran into performance issues caused by parameter sniffing so many times using this type of constructat work that I had to stop using it.Now I use this:declare @local_firstname nvarchar(50)declare @local_lastname nvarchar(50)select @local_firstname = isnull(@firstname,''), @local_lastname = isnull(@lastname,'')SELECT ID, FirstName, LastName FROM PeopleWHEREFirstName LIKE '%' + @local_firstname + '%'andLastName LIKE '%' + @local_lastname + '%'It's more work up front but:a) makes the query easier to understand (and therefore maintain) andb) avoids slow queries caused by the use of inappropriate query plans via parameter sniffingJust my 2cTue, 31 Aug 2010 18:16:36 GMTcantorhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxTo everyone who recommended the articles on dynamic SQL, thank-you. That is an elegant solution which I had missed due to my hard-wired "Dynamic SQL is Bad" thinking.I also had a play around with using CTEs to evaluate each condition at the start and then join the results back together, but I haven't been able to get onto a decent sized data set to play with the idea.The example below assumes a unique key (column name is "Id") exists on the Person table which is not related to their first and last names[code="sql"]WITH FN AS ( SELECT * FROM Person WHERE FirstName LIKE COALESCE(@l_FirstName, LastName) ) , LN AS ( SELECT * FROM Person WHERE LastName LIKE COALESCE(@l_LastName, LastName) )SELECT [FN].* FROM FN INNER JOIN LN ON FN.Id = LN.Id[/code]Anyone care to test this on a sufficiently non-trivial data set?Edit: And yes, I'm sure that full-text indexes would simplify the situation. However, the original article (thanks again for posting Tony) was more about boolean algebra and I was trying to offer an alternative structure for that.Tue, 31 Aug 2010 16:57:20 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxWhy not use sp_ExecuteSQL and build up the query that is needed in T-SQL code. This will cache the execution plan for the query statement.The other thing I would suggest is that if you are going to using multiple LIKE '%[some string]% then you consider using full text indexes.Tue, 31 Aug 2010 16:21:28 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]LSAdvantage (8/31/2010)[/b][hr]Very good point: "So I would be looking for clarification of the requirements, and making it clear that there is a performance issue, if they want to search the middle of words."Thank you Jasmine.Lisa[/quote]Exactly.On the bright side, with a dynamic query, if they don't search on that one option, it won't do it.I've actually got dynamic search queries that change the joins if specific text values aren't part of the search.Tue, 31 Aug 2010 15:04:29 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxVery good point: "So I would be looking for clarification of the requirements, and making it clear that there is a performance issue, if they want to search the middle of words."Thank you Jasmine.LisaTue, 31 Aug 2010 14:55:50 GMTLSAdvantagehttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]LSAdvantage (8/31/2010)[/b][hr][quote][b]roger.plowman (8/31/2010)[/b][hr]I'd think (from a performance standpoint) using LIKE is the *last* thing you'd ever want to do. Especially the "Like %X%" construction, since at best it's going to do an index scan."LIKE X%" performs adequately since it lets the query optimizer at least limit the scan to a subset of the index, but both "LIKE %X%" and "LIKE %X" are horrible performance killers.[/quote]Is there any way around this dilemma? I only add the LIKE syntax dynamically if the user passed data for that parameter, but if this is bad regardless, I'd like to do this correctly if possible. I use "LIKE '%X%'" because the user requested the ablility to search on any portion of the content. I hope this is not too much of a tangent question![/quote]No there really isn't... since you're searching for a substring, SQL Server has to look at each column value explicitly and search it for the substring. There is no way for it to know if the substring is contained in the column, without actually reading the column. If you leave off the first %, then it can use an index, but sometimes it still doesn't. I find that most of the time when users say they want to search for substrings, really they don't want to search the middle of words. So I would be looking for clarification of the requirements, and making it clear that there is a performance issue, if they want to search the middle of words.Tue, 31 Aug 2010 14:31:42 GMTJasmine D. Adamsonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]roger.plowman (8/31/2010)[/b][hr]I'd think (from a performance standpoint) using LIKE is the *last* thing you'd ever want to do. Especially the "Like %X%" construction, since at best it's going to do an index scan."LIKE X%" performs adequately since it lets the query optimizer at least limit the scan to a subset of the index, but both "LIKE %X%" and "LIKE %X" are horrible performance killers.[/quote]Is there any way around this dilemma? I only add the LIKE syntax dynamically if the user passed data for that parameter, but if this is bad regardless, I'd like to do this correctly if possible. I use "LIKE '%X%'" because the user requested the ablility to search on any portion of the content. I hope this is not too much of a tangent question!Tue, 31 Aug 2010 13:15:56 GMTLSAdvantagehttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxI was interested to know what this actually does in SQL 2008... So I created this...[code="sql"]drop procedure dbo.USR_TEST_SEARCHGOcreate procedure dbo.USR_TEST_SEARCH @fname_search varchar(40) = null, @lname_search varchar(40) = nullasselect FIRST_NAME, LAST_NAME, MASTER_CUSTOMER_IDfrom dbo.CUSTOMERwhere (@fname_search is null OR FIRST_NAME like '%'+@fname_search+'%') AND (@lname_search is null OR LAST_NAME like '%'+@lname_search+'%')GO[/code]I have a table here with 271,000 customers, so I did some tests on that. Note that this is against real data, in an active database, in the middle of the day.Run_timesno params: 2283ms (an index scan was done, because I have an index with the three result columns.)with fname only = "jim": 173ms (2100 rows found) again, an index scan is usedwith both names: 170ms (5 rows found) also an index scanSo that was pretty good... but then I devised a way to show what I said above... I added a third search field...[code="sql"]create procedure dbo.USR_TEST_SEARCH @fname_search varchar(40) = null, @lname_search varchar(40) = null, @class_search varchar(24) = nullasselect FIRST_NAME, LAST_NAME, MASTER_CUSTOMER_IDfrom dbo.CUSTOMERwhere (@fname_search is null OR FIRST_NAME like '%'+@fname_search+'%') AND (@lname_search is null OR LAST_NAME like '%'+@lname_search+'%') AND (@class_search is null OR CUSTOMER_CLASS_CODE = @class_search)GO[/code] Now the performance is horrible, even when you only use the first name in the search. The 170 above went to 313, almost double the time... and the query plan became complicated with nested loops and one index seek and one index scan. NOT IDEAL when you aren't searching with the customer class parameter.Tue, 31 Aug 2010 13:08:24 GMTJasmine D. Adamsonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxThis concept was discussed extensively in Erland Sommarskog's "Dynamic Search Conditions" http://www.sommarskog.se/dyn-search-2005.html which is a pretty famous article. You should at least link to it in your main article. :-)One of the previous comments was correct in index use; if I wanted to use a Like condition or not use a Like condition, I would use an If clause around the whole thing:If <need to use Like> Select <Columns> From Table Where <Column> Like '%' + @String + '%'Else Select <Columns> From TableEnd IfSee Erland Sommarskog's discussion in the referenced link, about "x = @x AND @x IS NOT NULL" that says this syntax might cause SQL to "add[] a filter with a startup expression to the plan" which will help performance.Tue, 31 Aug 2010 12:19:37 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Jeffrey Wiener (8/31/2010)[/b][hr]But then, for a very common task as described in this article, the most optimal solution is to code it with dynamic SQL. This then forces the developer to sacrifice all the benefits of that color coding and on-the-spot compiling.[/quote]And? Proper stored procs "in theory" have no optional parameters. Because these are optional parameters what you're getting is a query cache with different plans for each combination of parameters... it's actually very cool that it works this way.I like to think of my dynamic procedures like this as stored procedures that write stored procedures... it makes me smile.Tue, 31 Aug 2010 10:33:37 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Brannon Weigel (8/31/2010)[/b][hr]About a year ago, someone in our company started using this approach to make stored procedures generic enough so multiple places in the application can use the same stored procedure. BAD MOVE! This practice brought the system to its knees. Bad plans are cached, high I/O, table scans, heavy blocking and deadlocks are just some of the problems with using OR statements in WHERE clauses. We went through a huge exercise to remove ALL of this code from stored procedures but we still find one we missed every once in a while. They will eventually show up on our daily list of stored procedures with the most reads.I'd recommend against this practice.[/quote]Any method can be abused, that doesn't make it "always bad" - see my post before. If this technique is used correctly, performance can be pretty good, but the situation you describe is extreme. I have seen that many times in various places - someone gets an idea that one technique can solve every problem, and pretty soon you have a real mess. This is like anything else, it will bite you if you use it wrong.Tue, 31 Aug 2010 09:34:35 GMTJasmine D. Adamsonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Tim Brooks (8/31/2010)[/b][hr]I've seen this construct used many times, and typically it is a little poor on performance. An index on First and / or Last Name will usually be used, for sure, but it will tend to be an index scan, rather than a seek. I've tried many variations to get this to change, but with no joy as yet.[/quote]Yes you are correct. I've used this trick myself for many years. Performance of it was pretty bad in some situations. I think, if you are using SQL Server, the dynamic query can be faster - SQL Server will look at your stored proc, and generate and cache a different plan for each possibility of the dynamic query. If certain options are heavily used, you will see an improvement in those, possibly at the expense of more rarely used options. Dynamic SQL in general should probably be avoided, but in the case where you are doing a search query with a lot of optional parameters, I think it can be used effectively.But the conditional where clause (if that's what we're calling it now) is a good way to do it if you only have one or two optional search parameters... when you get over two, it gets ugly.Tue, 31 Aug 2010 09:30:58 GMTJasmine D. Adamsonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxAbout a year ago, someone in our company started using this approach to make stored procedures generic enough so multiple places in the application can use the same stored procedure. BAD MOVE! This practice brought the system to its knees. Bad plans are cached, high I/O, table scans, heavy blocking and deadlocks are just some of the problems with using OR statements in WHERE clauses. We went through a huge exercise to remove ALL of this code from stored procedures but we still find one we missed every once in a while. They will eventually show up on our daily list of stored procedures with the most reads.I'd recommend against this practice.Tue, 31 Aug 2010 09:18:30 GMTBrannon Weigelhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxI agree that the approach described is the most rational, and therein lies the irony that is Microsoft. Please correct me if I am mistaken, but this approach will yield an index scan, while the dynamic SQL approach can get an index seek, which is faster.The irony is even more exasperating when one considers that Microsoft sells a developer environment that provides all the bells and whistles, like color coding for key words and on-the-spot compiling to identify what would be a runtime error. But then, for a very common task as described in this article, the most optimal solution is to code it with dynamic SQL. This then forces the developer to sacrifice all the benefits of that color coding and on-the-spot compiling.Tue, 31 Aug 2010 08:32:00 GMTJeffrey Wienerhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxThe main concern is about the use of indexes eventually present on the tables queried.Though is quite different to "evaluate" the term, consider the word [center]small medium large[/center] referred to the size of table or to the size of the resulting rowset:For "small" you can use ISNULL or COALESCE as suggested by pfranceschini.For "medium" you can use AND/OR sequences as suggested by tony.For "large" you can use temp table:FIRST - using "medium" approach to create a temp table with the rows that match indexed fields, the temp table will have index on the fields that are NOT indexed in the original tableAFTER - query the temp table using remaining field not indexed in the original table but indexed in the temp.I think each case must be worked on separately, there aren't a general rule of thumb! We must approach the most valuable solution that worth.Tue, 31 Aug 2010 08:20:17 GMTfabio banfihttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]WayneS (8/31/2010)[/b][hr]For those that have read this far, I'd like to suggest that you read [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/][u]this blog[/u][/url] by Gail Shaw on working with this type of query (catch-all query).[/quote]Another great article about the same thing I quoted. :)Tue, 31 Aug 2010 08:08:55 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Tony Alicea (8/31/2010)[/b][hr]As far as efficiency - in 14 years I've always found this technique to be acceptable in the realm of efficiency and (especially in the old days) was given weight over closing the potential security holes of dynamic SQL. [/quote]Building Parameterized Dynamic SQL and then invoking it via EXEC sp_executesql and passing back in the parameters will outperform this by a mile.No more (filed IS NULL OR filed = @parameter) junk. If the parameter is NULL it's not part of the WHERE clause.Read here.http://www.sommarskog.se/dyn-search-2005.htmlThis is applicable even in SQL 2008 R2 because the new parameter sniffing screws up more than it fixes.Tue, 31 Aug 2010 08:07:20 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxWhile I agree with some of the initial arguments in the article, particular those around familiarizing oneself with Set logic, boolean Algebra, Etc. I strongly disagree with the approach proposed. While very simple, it doesn NOT optimize correctly. There is no Short-Circuiting in SQL. The first step SQL takes when running a query is to Normalize this into an object graph (from which a hash of this object graph will determine if a matching execution plan exists). As a result, pretty much ANY @Variable = Value check will result in a full scan. in some cases an @Parameter = Value may rarely behave differently when contained in a stored proc. the functional difference being that at compile time, @Parameter values are considered as specific values, whereas @Variables defined in the procedure are considered unknowns and will ALWAYS result in a scan for this approach (See BOL: Statistics Used by the query optimizer) ...you can use the Optimize For hint to counter this behavior, OPTION RECOMPILE also works). this is one of the most common patterns I have identified as causing problems in enterprise level systems because of the forced scan behavior. Generally I recommend 1 of 2 approachs: Dynamic SQL using SP_ExecuteSQL w/ Binding (this treats the values correctly as parameters), but costs extra due to likely recompiles...and in cases where you have no more than 3 optional parameters, check the parameters with IF's and execute only the query that has your specified arguments. Either of these approachs will optimize correctly and yield MUCH better results.In rare cases, I would accept this patterns, as long as there were sufficient NON-NULLable arguments to ensure that the optimizer performed SEEK operations on the non-nullable values.Tue, 31 Aug 2010 08:06:43 GMTPaul Muharsky-474732http://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxmore elegant and better performing COALESCE solutionfirstname = COALESCE(@firstname,firstname)andlastname= COALESCE(@lastname,lasttname).. and one more comment dynamic SQL is always has to be a last and least favorite choicebecause it is not precompiled query and other many valid reasons http://www.sommarskog.se/dynamic_sql.htmlTue, 31 Aug 2010 07:51:41 GMTaruzhanshttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]opc.three (8/31/2010)[/b][hr][quote][b]Rob Fisk (8/31/2010)[/b][hr][quote][b]opc.three (8/31/2010)[/b][hr][quote][b]Rob Fisk (8/31/2010)[/b][hr]Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?[code="sql"]CREATE PROCEDURE [spSearchPeople] @firstname nvarchar(50) = '', @lastname nvarchar(50) = ''ASBEGIN SELECT ID, FirstName, LastName FROM People WHERE FirstName LIKE '%' + @firstname + '%' OR LastName LIKE '%' + @lastname + '%'END[/code][/quote]This approach does not correctly answer the search question when @firstname and @lastname are not null.[/quote]The parameters are defaulted to an empty string rather than null.OK, there is the front end caveat that you don't pass a parameter unless needed but given that there is no need to use ISNULL within the query.[/quote]The problem is not with the default value of the parameters it is with the boolean logic. If someone provide Jim for @firstname and Thorpe for @lastname your query will incorrectly return people with the name Jim Smith, Jim Williams, etc.[/quote]Silly me. Of course it just needs changing to AND and it's all good.Nice spot. Was such a basic error I didn't see it even when looking again to see f I had missed something.That's another issue with complex boolean logic of course is that the simplest of things can be a mare to track down. Especially if you start throwing the odd NOT into the mix.To be fair though the article's main point is that you should be familiar with it which should make following the logic somewhat easier both when building and trouble shooting.Tue, 31 Aug 2010 07:24:08 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxi would also add that teaching the benefit of using good mathematical principals, you should pick a better example query... not one that is at best teaching people how to write poor queries. If you look at the results in the above (real world example posted) the numbers clearly back the point of view which is to steer clear of using this method. sorry to rain on the paradeTue, 31 Aug 2010 07:23:51 GMTasirakyhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxthanx Eric Wahner-345205I already noticed and corrected the mistakeTue, 31 Aug 2010 07:16:33 GMTpfranceschinihttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxyes but its not as flexible ie doing something like:select * from table where (@date is null or [datecol] < @date)either way though, for best performance with this type of where clause stick to a dynamic sql. It will out perform any other way described in this forum.the proof:tested on a table of items that has 20 something million records. An index on the table on the [date] and [qty] fields. Doing:a) select [date], count(qty) from items where [date] < @dateb) select [date], count(qty) from items where [date] < isnull(@date, [date])c) select [date], count(qty) from items where (@date is null or [date] < @date)query A takes 3 seconds and B&C each take 22seconds. BIG difference.in the latter two the optimizer decides to do an index scan, whereas the first does an index seek(obviously much faster in a large table).Tue, 31 Aug 2010 07:16:18 GMTasirakyhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxHello All,First, good job Tony for the article. If anything, always good to put ideas out there for a vibrant debate.Now, I can unequivocally state that the performance aspect of using this technique is "OK" for very, very small datasets but once you have any real amount of data in a table to sift through, the performance degrades very quickly. As well, if you have many columns to filter on, it just drags the performance down even more.Having been bitten by this technique before, I switched back to dynamic SQL to take care of the job.I place all SQL commands in stored procs anyway so use of dynamic SQL is acceptable security wise (still have to be vigilant against injections...check your variables!).Again, with any technique you choose, test...test...test and check every aspect from, what I like to call "cold start" executions (first time run of a query) as that is a good estimation of what the "worst" performance of your query would be (subsequent calls to your query would be pulled from sql cache/precompiled) to a large set of data to search through.I really liked the methodology at first but once it was placed into a real world scenario, it just couldn't cut the mustard.2 cents...DaveTue, 31 Aug 2010 07:12:05 GMTDavid Athertonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxIt is nice to see people thinking outside the box. This was a short but nice article. As a sidenote another way of conditionally peforming a where is to utilize Not Null on the variables. I do this with queries that pass a multitude of parameters which may or may not have data in them such as:Create procedure dbo.some_Procedure_Name @var1 int Null, @var2 char(3) null, @var3 char(2) nullas Select field1, field2, field3 from TableName where (@var1 is not null and Field1 = @var1) and (@var2 is not null and field2 like '%' + @var2) and (@var3 is not null and Field3 = @var3)Now, we could Or these conditions together Or mix and and or depending on the desire results. As the author points out, this type of approach can get rather unwieldly very fast!I like figuring this stuff out, makes the day go by faster! :-)Cheers - DaveTue, 31 Aug 2010 07:05:51 GMTdavid.mortonhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxHere is another link from Gail Shaw relevant to this thread: [url]http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]People, if you use this type of construct either do it with one of the 3 mechanisms covered by Gail or take my business card because performance will be "suboptimal". :-DTue, 31 Aug 2010 07:04:15 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxGail Shaw posted a blog entry with several tests on performance for these types of "catch all" queries.[size="6"][u][b][url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]Catch-all queries[/url][/b][/u][/size](Sorry WayneS, I missed your post.)Tue, 31 Aug 2010 07:04:02 GMTAndrewJacksonZAhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxI'd think (from a performance standpoint) using LIKE is the *last* thing you'd ever want to do. Especially the "Like %X%" construction, since at best it's going to do an index scan."LIKE X%" performs adequately since it lets the query optimizer at least limit the scan to a subset of the index, but both "LIKE %X%" and "LIKE %X" are horrible performance killers.Tue, 31 Aug 2010 07:02:00 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Rob Fisk (8/31/2010)[/b][hr][quote][b]opc.three (8/31/2010)[/b][hr][quote][b]Rob Fisk (8/31/2010)[/b][hr]Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?[code="sql"]CREATE PROCEDURE [spSearchPeople] @firstname nvarchar(50) = '', @lastname nvarchar(50) = ''ASBEGIN SELECT ID, FirstName, LastName FROM People WHERE FirstName LIKE '%' + @firstname + '%' OR LastName LIKE '%' + @lastname + '%'END[/code][/quote]This approach does not correctly answer the search question when @firstname and @lastname are not null.[/quote]The parameters are defaulted to an empty string rather than null.OK, there is the front end caveat that you don't pass a parameter unless needed but given that there is no need to use ISNULL within the query.[/quote]The problem is not with the default value of the parameters it is with the boolean logic. If someone provide Jim for @firstname and Thorpe for @lastname your query will incorrectly return people with the name Jim Smith, Jim Williams, etc.Tue, 31 Aug 2010 07:00:36 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]Toby Harman (8/30/2010)[/b][hr]Always good to see a firm grasp of logic being used in computing!If I may suggest a variation to this technique I have seen is to use ISNULL or COALESCE around the parameter[code="sql"]ALTER PROCEDURE [spSearchPeople] @firstname nvarchar(50) = null, @lastname nvarchar(50) = nullASBEGIN SELECT ID, FirstName, LastName FROM People WHERE FirstName LIKE COALESCE('%' + @firstname + '%', FirstName)END[/code]If @firstname is NULL then the % + @firstname + % becomes NULL, so the table self matchesThis should mean less confusion over indices and better query plan caching as the optimiser can determine that the column we are interested in is FirstNameThe major drawback is it is less intuitive and probably T-SQL specific[/quote]The solution making use of COALESCE is more readable in my opinion however it is not logically equivalent to the solution presented in the article when the column being searched allows NULL. The article does not specify the DDL of the table being searched however many natural searches do go against columns that allow NULL, e.g. name parts, address parts, phone numbers, emails, etc.NULL != NULL so unlike in the article when @firstname IS NULL the search criteria for First Name is short=circuited enabling columns with a NULL First Name to be returned, the solution making use of COALESCE will attempt to compare a NULL value in the table with itself resulting in that row being excluded from the resultset. Here is a re-runnable example demonstrating the point:[code]IF OBJECT_ID(N'tempdb..#addy') IS NOT NULL DROP TABLE #addyCREATE TABLE #addy( id INT, firstname VARCHAR(20) NULL, lastname VARCHAR(20) NULL) ;INSERT INTO #addy (id, firstname, lastname) VALUES (1,'Rick','Barnes')INSERT INTO #addy (id, firstname, lastname) VALUES (2,'Rick',NULL)DECLARE @firstname VARCHAR(20), @lastname VARCHAR(20)-- we are searching for all names with 'ic' in the first nameSELECT @firstname = 'ic', @lastname = NULL-- row with NULL lastname is excludedSELECT *FROM #addyWHERE firstname LIKE COALESCE('%' + @firstname + '%', firstname) AND lastname LIKE COALESCE('%' + @lastname + '%', lastname) -- NULL != NULL -- row with NULL lastname is includedSELECT *FROM #addyWHERE (@firstname IS NULL OR firstname LIKE '%' + @firstname + '%') AND (@lastname IS NULL OR lastname LIKE '%' + @lastname + '%')[/code]Tue, 31 Aug 2010 06:53:30 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxFor those that have read this far, I'd like to suggest that you read [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/][u]this blog[/u][/url] by Gail Shaw on working with this type of query (catch-all query).Tue, 31 Aug 2010 06:49:30 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspx[quote][b]opc.three (8/31/2010)[/b][hr][quote][b]Rob Fisk (8/31/2010)[/b][hr]Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?[code="sql"]CREATE PROCEDURE [spSearchPeople] @firstname nvarchar(50) = '', @lastname nvarchar(50) = ''ASBEGIN SELECT ID, FirstName, LastName FROM People WHERE FirstName LIKE '%' + @firstname + '%' OR LastName LIKE '%' + @lastname + '%'END[/code][/quote]This approach does not correctly answer the search question when @firstname and @lastname are not null.[/quote]The parameters are defaulted to an empty string rather than null.OK, there is the front end caveat that you don't pass a parameter unless needed but given that there is no need to use ISNULL within the query.Tue, 31 Aug 2010 06:48:31 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic977674-2777-1.aspxLet me start by saying that I quite agree with the initial point of the article. When confronted with a complex SQL problem you are better off thinking in Venn diagrams than logic trees. Now into the meat of the discussion:There is a way to avoid dynamic SQL while still caching plans correctly, although most of the discussion above about cached plans ignores that the names are unqualified.(simplified code)[code="sql"][font="Courier New"]IF @FirstName IS NULL AND @LastName IS NULL SELECT ID, FirstName, LastName FROM MyDB.dbo.PeopleELSE IF @FirstName IS NULL AND @LastName IS NOT NULL SELECT ID, FirstName, LastName FROM MyDB.dbo.People WHERE LastName LIKE @LastName -- I usually let the caller supply wildcardsELSE IF @FirstName IS NOT NULL AND @LastName IS NULL SELECT ID, FirstName, LastName FROM MyDB.dbo.People WHERE FirstName LIKE @FirstName -- I usually let the caller supply wildcardsELSE -- NEITHER ARE NULL SELECT ID, FirstName, LastName FROM MyDB.dbo.People WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName [/font][/code]NOTE: Normally I use BEGIN and END for every conditional, even if there is only a single statementIn the past I have also checked for wild cards in the parameter and used separate queries with like and equal, but that is beyond the scope of this discussion.So is that using sp_executesql and passing in parameters protects against most SQL injection. Tue, 31 Aug 2010 06:42:34 GMTsteven.malone