Click here to monitor SSC
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
LSAdvantage (8/31/2010)
Very good point: "So I would be looking for clarification of the requirements, and making it clear that there is a performance issue, if they want to search the middle of words."

Thank you Jasmine.

Lisa


Exactly.

On the bright side, with a dynamic query, if they don't search on that one option, it won't do it.

I've actually got dynamic search queries that change the joins if specific text values aren't part of the search.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3696 Visits: 3120
Why not use sp_ExecuteSQL and build up the query that is needed in T-SQL code. This will cache the execution plan for the query statement.

The other thing I would suggest is that if you are going to using multiple LIKE '%[some string]% then you consider using full text indexes.

LinkedIn Profile

Newbie on www.simple-talk.com
Toby Harman
Toby Harman
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 668
To everyone who recommended the articles on dynamic SQL, thank-you. That is an elegant solution which I had missed due to my hard-wired "Dynamic SQL is Bad" thinking.

I also had a play around with using CTEs to evaluate each condition at the start and then join the results back together, but I haven't been able to get onto a decent sized data set to play with the idea.

The example below assumes a unique key (column name is "Id") exists on the Person table which is not related to their first and last names


WITH
FN AS (
SELECT
*
FROM Person
WHERE FirstName LIKE COALESCE(@l_FirstName, LastName)
)
, LN AS (
SELECT
*
FROM Person
WHERE LastName LIKE COALESCE(@l_LastName, LastName)
)
SELECT
[FN].*
FROM FN
INNER JOIN LN
ON FN.Id = LN.Id



Anyone care to test this on a sufficiently non-trivial data set?

Edit: And yes, I'm sure that full-text indexes would simplify the situation. However, the original article (thanks again for posting Tony) was more about boolean algebra and I was trying to offer an alternative structure for that.
cantor
cantor
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 443
I ran into performance issues caused by parameter sniffing so many times using this type of construct
at work that I had to stop using it.

Now I use this:

declare @local_firstname nvarchar(50)
declare @local_lastname nvarchar(50)

select @local_firstname = isnull(@firstname,''), @local_lastname = isnull(@lastname,'')

SELECT ID, FirstName, LastName FROM People
WHERE
FirstName LIKE '%' + @local_firstname + '%'
and
LastName LIKE '%' + @local_lastname + '%'

It's more work up front but:
a) makes the query easier to understand (and therefore maintain) and
b) avoids slow queries caused by the use of inappropriate query plans via parameter sniffing

Just my 2c
asiraky
asiraky
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 13
use fulltext search. its fast, its adaptable and its simple
cantor
cantor
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 443
I just used the example given, I have yet to use a like clause in any of my code, the focus of the comment is on the performance issues around the use of optional paramaters in a stored procedure.

hth
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21101 Visits: 18259
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
David.Poole (8/31/2010)
Why not use sp_ExecuteSQL and build up the query that is needed in T-SQL code. This will cache the execution plan for the query statement.

The other thing I would suggest is that if you are going to using multiple LIKE '%[some string]% then you consider using full text indexes.


Unfortunately Full Text Indexing doesn't handle middle of a string search like LIKE does.

It seems to only handle the LIKE 'sometext%' type searches... and not inner text type searches.
Or at least that's what it did in SQL 2005 and earlier.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
UncleJimBob (8/31/2010)
I ran into performance issues caused by parameter sniffing so many times using this type of construct
at work that I had to stop using it.



TRACEFLAG 4136 will be your friend.

That or simply redeclaring local varaibles for each parameter and setting the local variable to the parameter.

either will get you around Microsoft's silly new Parameter sniffing feature that actually hurts performance more than it helps.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
cantor
cantor
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 443
That or simply redeclaring local varaibles for each parameter and setting the local variable to the parameter.


Yes, thats exactly what I do :-)
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