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


Conditional WHERE Clauses and Boolean Algebra


Conditional WHERE Clauses and Boolean Algebra

Author
Message
Tony Alicea
Tony Alicea
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
Comments posted to this topic are about the item Conditional WHERE Clauses and Boolean Algebra
Toby Harman
Toby Harman
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 668
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


ALTER PROCEDURE [spSearchPeople]
@firstname nvarchar(50) = null,
@lastname nvarchar(50) = null
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE
FirstName LIKE COALESCE('%' + @firstname + '%', FirstName)
END



If @firstname is NULL then the % + @firstname + % becomes NULL, so the table self matches

This should mean less confusion over indices and better query plan caching as the optimiser can determine that the column we are interested in is FirstName

The major drawback is it is less intuitive and probably T-SQL specific
Filipe
Filipe
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 351
I have to agree that this is a very elegant solution, but can be very bad for performance.
Imagine you have a table with 1 million names in it, and you have indexes on first name and on last name.
If you query that table by first name, SQL will use the first name index.
If you query that table by last name, SQL will use the last name index.
If you use the boolean logic, SQL will likely read the whole table because it won't know which one to use. Even if SQL is smart enough to choose one index, it will save that query plan for every execution, and if the conditions change (i.e. first time read by first name and now is selecting by last name) it will use the wrong index.
For cases like this dynamic SQL is usually better, since SQL will have a differnt plan for each type of search.
Please note that this is not an issue in example case because the name in this case is always between '%' , and SQL would not use an index anyway.
Toby Harman
Toby Harman
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 668
Sad to think that SQL would fail to optimise when both FirstName and LastName are provided. I would expect it to use one Index and subselect the results.

Alternatively it could resolve on both indexes simultaneously and inner join the results. Effectively, create a virtual table of Customers that match first name, a second virtual table of Customers that match the second name and then INNER JOIN the two virtual tables.

As with all these things, if you are prepared to put in the hours there are usually 42 ways to solve the problem. ;-)

And yes, I'd forgotten the leading %. Thanks for the reminder
Filipe
Filipe
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 351
Please don't get me wrong. If SQL finds that either first name or last name with the parameters passed will give you a good plan, it will use that index.
The problem is that now your query has a plan that uses that index (let's say it used last name).
The next time you call this same procedure and supply a first name and NULL for last name, the query plan is ready and SQL will not create a new one.
So in our example SQL will read the whole table using the last name index to find people by first name, which is even worse than doing a full table scan :-)
timwam
timwam
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 199
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.
Rob Fisk
Rob Fisk
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 428
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?

CREATE PROCEDURE [spSearchPeople]
@firstname nvarchar(50) = '',
@lastname nvarchar(50) = ''
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE FirstName LIKE '%' + @firstname + '%'
OR LastName LIKE '%' + @lastname + '%'
END



_______________________________________________________
Change is inevitable... Except from a vending machine.

asiraky
asiraky
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 13
Bad for performance in general is my experience with using this type of sequence, even in some smaller tables and particularly when i used alot of parameters with this same sequence.

I am also curious as to why its posted as an article being such a minor and commonly used thing. i.e. i could see just as much use in writing an article teaching people how to write select * from tablename. But thats just me...
pfranceschini
pfranceschini
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 100
Very nice and pragmatic article.
I also like the solution proposed by Toby Harman, though I'm not able to determine the impact on performance.

I propose another solution, the one that I use most Hehe :

ALTER PROCEDURE [spSearchPeople]
@firstname nvarchar(50) = null,
@lastname nvarchar(50) = null
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE
FirstName LIKE '%' + ISNULL(@firstname, FirstName) + '%'
END

obviously it also works perfectly with equal where conditions :-P


ALTER PROCEDURE [spSearchPeople]
@firstname nvarchar(50) = null,
@lastname nvarchar(50) = null
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE
ISNULL(@firstname, FirstName) = FirstName
END

Cheers
Dave Morrison
Dave Morrison
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 242
This is a pretty poor example of how to do this imho.


If you wish to do the dreaded "like" you should have two sets of params, one set when you know the whole first / last name and one for partial first / last names.
This way indexes get used properly and you dont have to use like when you know the whole names etc

Coalesce should also be used to to remove the reams of or / and where clauses
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