Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

LIKE with and without wildcards in WHERE clause Expand / Collapse
Author
Message
Posted Friday, November 23, 2012 8:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:20 PM
Points: 308, Visits: 862
I 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:

	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)

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?
Post #1388180
Posted Friday, November 23, 2012 10:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1388210
Posted Friday, November 23, 2012 11:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 2,763, Visits: 5,912
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 Catch-All queries
Try what's best for your situation.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388223
Posted Friday, November 23, 2012 1:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:20 PM
Points: 308, Visits: 862
Gazareth (11/23/2012)
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
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.
Post #1388249
Posted Friday, November 23, 2012 1:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:20 PM
Points: 308, Visits: 862
Luis Cazares (11/23/2012)
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 Catch-All queries
Try what's best for your situation.
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.
Post #1388252
Posted Friday, November 23, 2012 2:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 2,763, Visits: 5,912
The easiest way is to do it like this
WHERE 	Oink.[System] like @System + '%'
AND Oink.Stratigrafie like @Stratigrafie + '%'
AND Oink.Ulozeni like @Ulozeni + '%'
AND Oink.DrEvid like @DrEvid + '%'

Or you can add it to the variables before the query if it's easier for you.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388257
Posted Saturday, November 24, 2012 6:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:20 PM
Points: 308, Visits: 862
Luis Cazares (11/23/2012)
The easiest way is to do it like this
WHERE 	Oink.[System] like @System + '%'
AND Oink.Stratigrafie like @Stratigrafie + '%'
AND Oink.Ulozeni like @Ulozeni + '%'
AND Oink.DrEvid like @DrEvid + '%'

Or you can add it to the variables before the query if it's easier for you.
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.
Post #1388307
Posted Monday, November 26, 2012 5:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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.


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.
Post #1388568
Posted Monday, November 26, 2012 10:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:20 PM
Points: 308, Visits: 862
DiverKas (11/26/2012)
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.


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.

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.
Post #1388730
Posted Monday, November 26, 2012 11:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 2,763, Visits: 5,912
What 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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388757
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse