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 12345»»»

Conditional WHERE Clauses and Boolean Algebra Expand / Collapse
Author
Message
Posted Monday, August 30, 2010 9:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, August 30, 2010 10:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:46 PM
Points: 381, Visits: 535
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
Posted Monday, August 30, 2010 11:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:26 PM
Points: 175, Visits: 288
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
Posted Monday, August 30, 2010 11:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:46 PM
Points: 381, Visits: 535
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
Post #977696
Posted Monday, August 30, 2010 11:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:26 PM
Points: 175, Visits: 288
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

Post #977698
Posted Tuesday, August 31, 2010 1:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:47 AM
Points: 406, Visits: 196
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
Posted Tuesday, August 31, 2010 3:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, August 31, 2010 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 31, 2010 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:24 AM
Points: 4, Visits: 95
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 :

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


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
Posted Tuesday, August 31, 2010 4:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:32 PM
Points: 92, Visits: 200
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse