Conditional WHERE Clauses and Boolean Algebra

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tony Alicea » Conditional WHERE Clauses and Boolean Algebra...

61 posts, Page 1 of 7

1 2 3 4 5 »»»

Conditional WHERE Clauses and Boolean Algebra

Rate Topic

Display Mode

Topic Options

Author

Message

Tony Alicea

Posted Monday, August 30, 2010 9:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 02, 2010 8:09 AM
Points: 1, Visits: 37

Comments posted to this topic are about the item Conditional WHERE Clauses and Boolean Algebra

Post #977674

Toby Harman

Posted Monday, August 30, 2010 10:17 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:21 AM
Points: 322, Visits: 475

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

Post #977684

Filipe

Posted Monday, August 30, 2010 11:03 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:31 AM
Points: 158, Visits: 234

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.

Post #977691

Toby Harman

Posted Monday, August 30, 2010 11:19 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:21 AM
Points: 322, Visits: 475

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. Wink

And yes, I'd forgotten the leading %. Thanks for the reminder

Post #977696

Filipe

Posted Monday, August 30, 2010 11:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:31 AM
Points: 158, Visits: 234

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 Smile

Post #977698

timwam

Posted Tuesday, August 31, 2010 1:26 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:04 AM
Points: 397, Visits: 176

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.

Post #977724

Rob Fisk

Posted Tuesday, August 31, 2010 3:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163, Visits: 427

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.

Post #977776

asiraky

Posted Tuesday, August 31, 2010 3:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 4:13 AM
Points: 6, 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...

Post #977781

pfranceschini

Posted Tuesday, August 31, 2010 4:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 9:01 AM
Points: 4, Visits: 87

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 Tongue

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

Post #977805

Dave Morrison

Posted Tuesday, August 31, 2010 4:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 8:44 AM
Points: 92, Visits: 197

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

Post #977808

« Prev Topic | Next Topic »

61 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions