InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 16:24:22 GMT20http://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]GSquared (12/3/2012)[/b][hr]There's no need to convert Like to = if it doesn't include any wildcards. Just use Like. [b][u]SQL Server will treat it as an equality test if the input doesn't have any wildcards in it.[/u][/b] Let the computer do the work for you.[/quote]BINGO! [b][u]That's[/u][/b] what I was hoping for, and hadn't been able to figure out myself.[quote]For a shortcut on the Where clause, try this:...[/quote]That's a good trick. You're right, that will make the code considerably less messy. Another one for the toolbox.Many thanks, I'm clear on how I want to build this now.Mon, 03 Dec 2012 09:38:28 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxThere's no need to convert Like to = if it doesn't include any wildcards. Just use Like. SQL Server will treat it as an equality test if the input doesn't have any wildcards in it. Let the computer do the work for you.For a shortcut on the Where clause, try this:[code="sql"]DECLARE @Where NVARCHAR(max) = 'WHERE 1=1'IF @Parameter > '' SET @Where += ' AND AppropriateColumn LIKE @Parameter'IF @Parameter2 > '' SET @Where += ' AND AppropriateColumn LIKE @Parameter2'[/code]Simplifies the string construction. You can begin each with "AND" by default, because you start the whole thing with "1=1". The query optimizer will ignore the 1=1 part, so it doesn't have any negative impact.No real difference, just easier to maintain and read.Mon, 03 Dec 2012 06:03:55 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]GSquared (11/30/2012)[/b][hr][quote][b]pdanes (11/30/2012)[/b][hr]I'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)If @Param1 contains AnyWildCardCharacter @DSQL = @DSQL + 'Field1 Like @Param1'Else @DSQL = @DSQL + 'Field1 = @Param1'for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?[/quote]I'd have to see the specific code as-written, before I could suggest improvements.[/quote]That's sensible enough, but I was trying to avoid writing a bunch of bad code. I assume you don't want the entire SP anyway - it's several hundred lines long. But here's a sample of what I have now:[code="sql"]if @Series <> '' set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Series Like @Series'if @Stage <> '' set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Stage Like @Stage'[/code]What I would have to do is modify each such statement to something like this:[code="sql"]if @Stage <> '' set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Stage ' + Case charindex('%',@Stage) > 0 Or CHARINDEX('_',@Stage) > 0 Then 'Like' Else '=' End + ' @Stage'[/code]And looking at it now, I'm not even sure if that's enough. I might want to check for brackets as well, or maybe only matched brackets, or who knows what, come to think of it. To be completely accurate, I might have to build a miniature regular expression recognition function, to be sure that I captured every possibility. Hmm, this is starting to look like a major pain in the fundament. I think I'll shine it for now, just code it with 'LIKE' and see how it works.[quote]Is it slow enough that users have a problem with it? I gather it's infrequently used, so the overall hit on the server should be minimal even if it's pretty slow and expensive. Thus, the only real reason to worry about tuning is does it impact users negatively.[/quote]It was slow enough to be somewhat irritating, but not really a problem, and you're right, it doesn't get used all that much. I'm just kind of a nit-picker. My general attitude is that good enough isn't good enough, but sometimes you have to be realistic about when you're wasting effort that could be better expended elsewhere. This is starting to look like one of those cases.Thanks for the input. I'm going to continue with the strategy of building special routines for frequently used combinations, leave this as a last-resort fallback and just not worry about the speed and efficiency.Fri, 30 Nov 2012 09:17:37 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]pdanes (11/30/2012)[/b][hr]I'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)If @Param1 contains AnyWildCardCharacter @DSQL = @DSQL + 'Field1 Like @Param1'Else @DSQL = @DSQL + 'Field1 = @Param1'for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?[/quote]I'd have to see the specific code as-written, before I could suggest improvements.Is it slow enough that users have a problem with it? I gather it's infrequently used, so the overall hit on the server should be minimal even if it's pretty slow and expensive. Thus, the only real reason to worry about tuning is does it impact users negatively.Fri, 30 Nov 2012 08:31:22 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxI'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)If @Param1 contains AnyWildCardCharacter @DSQL = @DSQL + 'Field1 Like @Param1'Else @DSQL = @DSQL + 'Field1 = @Param1'for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?Fri, 30 Nov 2012 06:24:44 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]capnhector (11/27/2012)[/b][hr][quote][b]pdanes (11/27/2012)[/b][hr][quote][b]GSquared (11/27/2012)[/b][hr][quote][b]pdanes (11/27/2012)[/b][hr]There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.[/quote]I tend to use dynamic SQL for that kind of thing.[/quote]I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.[/quote]i found [url]http://www.sommarskog.se/dynamic_sql.html[/url] to be very helpful to my understanding of dynamic SQL. After reading that article several times i am confident i can write injection free code.[/quote]Thanks, Captain, that's an excellent article. Bookmarked.Fri, 30 Nov 2012 02:52:23 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]pdanes (11/27/2012)[/b][hr][quote][b]GSquared (11/27/2012)[/b][hr][quote][b]pdanes (11/27/2012)[/b][hr]There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.[/quote]I tend to use dynamic SQL for that kind of thing.[/quote]I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.[/quote]Injection-safety is a large part of why MS added sp_executeSQL to SQL Server. Much safer than Exec(), since strings are passed in as values instead of executed directly.No way to inject with this method.Wed, 28 Nov 2012 06:36:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]pdanes (11/27/2012)[/b][hr][quote][b]GSquared (11/27/2012)[/b][hr][quote][b]pdanes (11/27/2012)[/b][hr]There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.[/quote]I tend to use dynamic SQL for that kind of thing.[/quote]I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.[/quote]i found [url]http://www.sommarskog.se/dynamic_sql.html[/url] to be very helpful to my understanding of dynamic SQL. After reading that article several times i am confident i can write injection free code.Tue, 27 Nov 2012 16:46:55 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]GSquared (11/27/2012)[/b][hr][quote][b]pdanes (11/27/2012)[/b][hr]There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.[/quote]I tend to use dynamic SQL for that kind of thing.[/quote]I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.Tue, 27 Nov 2012 16:09:07 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]pdanes (11/27/2012)[/b][hr]There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.[/quote]You're thinking procedurally. SQL Server has to generate an execution plan that can take any value or none for any given parameter. It's not a runtime environment, like .NET or JAVA, it's a "declarative language". So when you say, "The variable can be this or can be that", it has to build a single plan that handles both of those correctly. To the optimizer, "correct" is more important than "efficient", so if you give it a query that can have wildly different "most efficient means to execute", you end up with a plan that has to cover all eventualities, and thus will be less efficient.There are ways to work around that. Various flavors of parameterized dynamic SQL, nested procs, With Recompile, etc., are all popular and effective methods of essentially generating a different query for different parameter combinations.I tend to use dynamic SQL for that kind of thing.[code="sql"]DECLARE @SQL NVARCHAR(max) = 'SELECT *FROM dbo.MyTableWHERE 1 = 1';IF @Parameter1 IS NOT NULLSET @SQL += 'AND MyColumn = @Parameter1';EXEC sp_executeSQL @SQL, '@Parameter1 INT', @Parameter1;[/code]That kind of thing. Don't actually build the values into the string, pass them in as parameters.Then, if a parameter value is null or blank, it simply doesn't get added to the Where clause, and you end up with a highly optimizable query, containing only those criteria that actually matter at runtime. No over-broad execution plans because of CASE or OR in Where.Tue, 27 Nov 2012 11:13:33 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxThere doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.Tue, 27 Nov 2012 09:34:33 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]sjimmo (11/27/2012)[/b][hr]Have you considered a full text index? I have had very good luck in some cases with issues such as yours. Check it out here and see if it would work for your case. [quote]http://msdn.microsoft.com/en-us/library/ms345119(v=sql.90).aspx[/quote]It will not always fit your needs, but may be an option to explore.[/quote]I have never used one of those, but this query can adress derived fields in up to five joined views. I'll take a look at it, but that doesn't seem like the best candidate to start on something I know nothing about. Thanks for the tip, though.Tue, 27 Nov 2012 09:27:09 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxHave you considered a full text index? I have had very good luck in some cases with issues such as yours. Check it out here and see if it would work for your case. [quote]http://msdn.microsoft.com/en-us/library/ms345119(v=sql.90).aspx[/quote]It will not always fit your needs, but may be an option to explore.Tue, 27 Nov 2012 06:49:20 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]ScottPletcher (11/26/2012)[/b][hr][quote][b]pdanes (11/26/2012)[/b][hr][quote][b]DiverKas (11/26/2012)[/b][hr][quote]But wouldn't this always compare all variables, though? In the syntax with OR, the query engine can see that a variable is empty, thereby satisfying one side of the OR condition, and need not even look in the database. [/quote]I am not sure that this is true. This isn't .NET where code branches based on one side of an IF or SWITCH. From what I have seen, the query engine runs both sides and then compares. But thats just me.[/quote]I don't know either. In VBA, such a comparison does get all parameters evaluated before the comparison is performed. In the Access Jet engine, it does not - a SWITCH function, for instance, stops evaluating when the first true condition is reached, so you can put in computations that yield a Null or unknown value, if something else stops the eval first, which is not possible in VBA.But the SQL Server optimizer is so smart about avoiding unnecessary work, it seems to me that in a situation when an already known value of a local variable can -completely- obviate the need to examine table data, it would most certainly do so. Maybe examining a few query plans would tell the story, but I'm still not very good at reading those, despite many hours spent over tutorials on the subject.[/quote]SQL can indeed do short-circuiting, and sometimes it will. It might also test things in a different order than you've specified. For example, you write "A = B OR C = D", SQL might test "C = D" first, and then skip "A = B", you really can't be sure which, if either, will happen.A CASE statement, however, is guaranteed to work in order. So you might try coding it like this:[code="sql"]WHERE (1 = CASE WHEN @input_variable = '' THEN 1 WHEN column_name LIKE @input_variable THEN 1 ELSE 0 END) AND (1 = CASE ... END) AND ...[/code]No guarantees, but it might be worth trying.[/quote]CASE statement like that in the Where clause guarantees index/table scans.Tue, 27 Nov 2012 06:32:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]pdanes (11/26/2012)[/b][hr][quote][b]DiverKas (11/26/2012)[/b][hr][quote]But wouldn't this always compare all variables, though? In the syntax with OR, the query engine can see that a variable is empty, thereby satisfying one side of the OR condition, and need not even look in the database. [/quote]I am not sure that this is true. This isn't .NET where code branches based on one side of an IF or SWITCH. From what I have seen, the query engine runs both sides and then compares. But thats just me.[/quote]I don't know either. In VBA, such a comparison does get all parameters evaluated before the comparison is performed. In the Access Jet engine, it does not - a SWITCH function, for instance, stops evaluating when the first true condition is reached, so you can put in computations that yield a Null or unknown value, if something else stops the eval first, which is not possible in VBA.But the SQL Server optimizer is so smart about avoiding unnecessary work, it seems to me that in a situation when an already known value of a local variable can -completely- obviate the need to examine table data, it would most certainly do so. Maybe examining a few query plans would tell the story, but I'm still not very good at reading those, despite many hours spent over tutorials on the subject.[/quote]SQL can indeed do short-circuiting, and sometimes it will. It might also test things in a different order than you've specified. For example, you write "A = B OR C = D", SQL might test "C = D" first, and then skip "A = B", you really can't be sure which, if either, will happen.A CASE statement, however, is guaranteed to work in order. So you might try coding it like this:[code="sql"]WHERE (1 = CASE WHEN @input_variable = '' THEN 1 WHEN column_name LIKE @input_variable THEN 1 ELSE 0 END) AND (1 = CASE ... END) AND ...[/code]No guarantees, but it might be worth trying.Mon, 26 Nov 2012 15:51:08 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxThe T-SQL query optimizer doesn't really shortcut the way you're thinking.I took a relatively large table on one of my test servers, added an index to an nvarchar(75) column, and tried both query constructs. The Select statement only covered the clustered index key, so no lookups were needed.One query had a Where clause like this:[code="sql"]WHERE MyColumn LIKE @Input1 + '%'AND MyColumn2 LIKE @Input2 + '%'[/code]The other had:[code="sql"]WHERE (@Input1 = '' OR MyColumn LIKE @Input1 + '%')AND (@Input2 = '' OR MyColumn2 LIKE @Input2 + '%')[/code]The first version, without the OR statements, gets an Index Seek in the Actual Execution Plan, the second gets an Index Scan. Both use the index I created to test this.I added a column to the query that wasn't included in the index, and the first one got an Index Seek + a Key Lookup, while the second remained as a full Index Scan (of course).Not that it means very much, but the one with the seek and the lookup was estimated at 17% of the total work, while the scan was estimated at 83%.On the data volume I'm dealing with (a few thousand rows in this case), the actual compile+execution time for both was comparable, with the Index Seek being consistently slightly faster. Like 15 milliseconds vs 16 milliseconds. Larger data volume and more complex searches (like '%' + @Input + '%', to find strings inside a column, instead of just on the leading edge), the speed advantage will be more pronounced.Mon, 26 Nov 2012 11:52:59 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxWhat you mention about choosing the best execution plan, is covered on Gail's article.And no, it won't give you better performance as it will use a "safe plan".You might not believe this, and the best thing to do, instead of guessing is to test.However, as you mention you need NULL values, then my option would be a dynamic query. As most things regarding SQL Server, the best solution would depend on the situation.;-)Mon, 26 Nov 2012 11:52:09 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]DiverKas (11/26/2012)[/b][hr][quote]But wouldn't this always compare all variables, though? In the syntax with OR, the query engine can see that a variable is empty, thereby satisfying one side of the OR condition, and need not even look in the database. [/quote]I am not sure that this is true. This isn't .NET where code branches based on one side of an IF or SWITCH. From what I have seen, the query engine runs both sides and then compares. But thats just me.[/quote]I don't know either. In VBA, such a comparison does get all parameters evaluated before the comparison is performed. In the Access Jet engine, it does not - a SWITCH function, for instance, stops evaluating when the first true condition is reached, so you can put in computations that yield a Null or unknown value, if something else stops the eval first, which is not possible in VBA.But the SQL Server optimizer is so smart about avoiding unnecessary work, it seems to me that in a situation when an already known value of a local variable can -completely- obviate the need to examine table data, it would most certainly do so. Maybe examining a few query plans would tell the story, but I'm still not very good at reading those, despite many hours spent over tutorials on the subject.Mon, 26 Nov 2012 10:50:47 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote]But wouldn't this always compare all variables, though? In the syntax with OR, the query engine can see that a variable is empty, thereby satisfying one side of the OR condition, and need not even look in the database. [/quote]I am not sure that this is true. This isn't .NET where code branches based on one side of an IF or SWITCH. From what I have seen, the query engine runs both sides and then compares. But thats just me.Mon, 26 Nov 2012 05:57:41 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]Luis Cazares (11/23/2012)[/b][hr]The easiest way is to do it like this[code="sql"]WHERE Oink.[System] like @System + '%'AND Oink.Stratigrafie like @Stratigrafie + '%'AND Oink.Ulozeni like @Ulozeni + '%'AND Oink.DrEvid like @DrEvid + '%'[/code]Or you can add it to the variables before the query if it's easier for you.[/quote]I see - it wasn't clear to me that you meant a concatenation. I thought that you simply suggested substituting a single percent for the empty string, and I couldn't see how that would change anything.But wouldn't this always compare all variables, though? In the syntax with OR, the query engine can see that a variable is empty, thereby satisfying one side of the OR condition, and need not even look in the database. If I use your construct, I think it would require examining every field, every time, to see if there is something in it. Also, it discards any record with nulls in any of the fields. I just tried a small test, and LIKE '%' does not pass a Null.Sat, 24 Nov 2012 06:36:57 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxThe easiest way is to do it like this[code="sql"]WHERE Oink.[System] like @System + '%'AND Oink.Stratigrafie like @Stratigrafie + '%'AND Oink.Ulozeni like @Ulozeni + '%'AND Oink.DrEvid like @DrEvid + '%'[/code]Or you can add it to the variables before the query if it's easier for you.Fri, 23 Nov 2012 14:35:26 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]Luis Cazares (11/23/2012)[/b][hr]I have two options for you.The first is to change the empty string for a '%', that way you get rid of the ORs. You need to do that before the select.Another option is to check Gail Shaw's article on [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]Catch-All queries[/url]Try what's best for your situation.[/quote]Gail's articles are always a good bet, I spend hours some times going through her stuff, and still there's things I miss. Thanks for the link.I'm confused about the '%' for empty string, though. How exactly would you code that to eliminate the OR? Gail has ORs in her article, and I don't see how putting a wildcard in for the empty '' would affect that.Fri, 23 Nov 2012 13:57:06 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspx[quote][b]Gazareth (11/23/2012)[/b][hr]If speed isn't a issue here, I doubt it'd be worth it. I imagine there's easier ways to get bigger improvements too.Best bet would be to test for each case and see!Are you using OPTION (RECOMPILE) in your select? I've seen it have pretty dramatic effects on this type of query.Cheers[/quote]Well, speed is always somewhat of an issue, isn't it? :-) But it's not critical, since this is not intended to be used much. Testing numerous variations in this case is rather a pain, since I have around thirty parameters, any of which may or may not be present. I would have to try numerous field lengths, various placements of the searched-for text in the fields, various combinations of parameters to have any real idea, all of which would take quite a bit of work. If someone already knows the answer, much better use of resources.I'm not using the recompile - I keep forgetting about it. I'll give it a try on some of the queries that I have working, but are a little slow. Thanks.Fri, 23 Nov 2012 13:49:31 GMTpdaneshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxI have two options for you.The first is to change the empty string for a '%', that way you get rid of the ORs. You need to do that before the select.Another option is to check Gail Shaw's article on [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]Catch-All queries[/url]Try what's best for your situation.Fri, 23 Nov 2012 11:33:55 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxIf speed isn't a issue here, I doubt it'd be worth it. I imagine there's easier ways to get bigger improvements too.Best bet would be to test for each case and see!Are you using OPTION (RECOMPILE) in your select? I've seen it have pretty dramatic effects on this type of query.CheersFri, 23 Nov 2012 10:23:30 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1388180-391-1.aspxI have a set of pretty well tuned queries with indexes and all the appropriate trinkets. This is a 'last restort' query, for when the user was absolutely unable to find what he needed. Speed is not crucial here, since it will not be used all that much - if any regular pattern of searches develops from this, I'll build new, separate queries to handle them, but this one will remain as a fallback, when the user has been unable to find anything using more orthodox approaches.The WHERE clause may comprise any number of fields, with any or all of them containing wildcards. A sample (air code) may look like this:WHERE fldA = 'abc' AND fldB LIKE '%def' AND fldC LIKE 'ghi_jkl' AND fldD = 'mno' ... The string literals here are actually all optional parameters, passed to the stored procedure from the user application. To deal with potentially missing parameters, I use the following syntax in my WHERE clause:[code="sql"] WHERE (@System = '' or Oink.[System] like @System) AND (@Stratigrafie = '' or Oink.Stratigrafie like @Stratigrafie) AND (@Ulozeni = '' or Oink.Ulozeni like @Ulozeni) AND (@DrEvid = '' or Oink.DrEvid like @DrEvid)[/code]This all works reasonably well, though obviously the performance is never going to be stellar. I recently started fiddling with scanning the parameters for wildcard characters, with the notion of building a conditional phrase that would use 'LIKE' when there is a wildcard and '=' when there is not. However, the syntax is quite messy and I haven't even gotten it to work yet. Then it occurred to me that I may be doing some completely unnecessary work, considering how smart the SQL engine is in general.So, here (finally) is my question: Is the query engine smart enough on its own to recognize the absence of wildcard characters and use a straight compare in such a case, even though I have coded a 'LIKE' comparison? Or, if the engine is not that smart, is this worth bothering about anyway? It's almost certainly going to be a full table scan in any case, with multiple joined tables. Is the extra overhead of a LIKE comparison even relevant, considering how much other work this query has to do?Fri, 23 Nov 2012 08:11:45 GMTpdanes