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 «««34567»»

Conditional WHERE Clauses and Boolean Algebra Expand / Collapse
Author
Message
Posted Tuesday, August 31, 2010 3:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #978362
Posted Tuesday, August 31, 2010 4:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 2,916, Visits: 1,853
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
Post #978390
Posted Tuesday, August 31, 2010 4:57 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:03 PM
Points: 405, Visits: 572
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.
Post #978396
Posted Tuesday, August 31, 2010 6:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 21, 2014 4:31 PM
Points: 41, Visits: 438
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
Post #978415
Posted Tuesday, August 31, 2010 6:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 4:13 AM
Points: 6, Visits: 13
use fulltext search. its fast, its adaptable and its simple
Post #978418
Posted Tuesday, August 31, 2010 6:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 21, 2014 4:31 PM
Points: 41, Visits: 438
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
Post #978419
Posted Tuesday, August 31, 2010 8:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #978462
Posted Wednesday, September 1, 2010 7:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #978745
Posted Wednesday, September 1, 2010 7:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #978748
Posted Wednesday, September 1, 2010 3:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 21, 2014 4:31 PM
Points: 41, Visits: 438
That or simply redeclaring local varaibles for each parameter and setting the local variable to the parameter.


Yes, thats exactly what I do
Post #979169
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse