SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LIKE with and without wildcards in WHERE clause


LIKE with and without wildcards in WHERE clause

Author
Message
pdanes
pdanes
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1354
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?
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 5798
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16220 Visits: 19059
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
pdanes
pdanes
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1354
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.
pdanes
pdanes
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1354
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16220 Visits: 19059
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
pdanes
pdanes
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1354
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.
DiverKas
DiverKas
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 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.
pdanes
pdanes
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1354
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16220 Visits: 19059
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search