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

Which one of these three (2) WHERE clauses is BEST for PERFORMANCE? Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 12:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:04 AM
Points: 39, Visits: 90
Which one of these [b]three (3) WHERE clauses is BEST for PERFORMANCE?[/b]

1) where databasename ='myDBname'
and charindex ('SQLAgent', applicationname)=0
and charindex ('SQL Server Log Shipping', applicationname )=0
and charindex ('Spotlight Diagnostic',applicationname )=0

2) where databasename =my'DBname'
and applicationname NOT LIKE 'SQLAgent'
and applicationname NOT LIKE ' SQL Server Log Shipping'
and applicationname NOT LIKE 'Spotlight Diagnostic'

3) where databasename ='myDBname'
and applicationname
NOT IN (''SQLAgent' ', ' SQL Server Log Shipping', 'Spotlight Diagnostic')



Voldemar
likes to play chess
Post #1536515
Posted Thursday, January 30, 2014 12:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 1,059, Visits: 5,757
Tell us - try them.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1536522
Posted Thursday, January 30, 2014 1:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 2,763, Visits: 5,909
I agree with Chris, you should try them.
However, you might note that No.1 is using a function so it's not SARGable and No.2 is using LIKE with no wildcards, so LIKE operator is not needed.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536523
Posted Thursday, January 30, 2014 1:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:04 AM
Points: 39, Visits: 90
Sorry I missed the % in LIKEs. Of course I meant wildcards..

As far as the clause with FUNCTION being NOT SARGeable - what do you mean? why not?

THANKS !!!



Voldemar
likes to play chess
Post #1536530
Posted Thursday, January 30, 2014 1:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 2,763, Visits: 5,909
This explains the basics: http://bit.ly/1aKPF3N


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536532
Posted Thursday, January 30, 2014 4:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
VoldemarG (1/30/2014)
Sorry I missed the % in LIKEs. Of course I meant wildcards..


If that's true, then the best solution (Option 3) cannot be accomplished.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536587
Posted Friday, January 31, 2014 12:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
VoldemarG (1/30/2014)
Sorry I missed the % in LIKEs. Of course I meant wildcards..


In that case option 2 and option 3 are not equivalent and hence cannot be compared for performance.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1536654
Posted Friday, January 31, 2014 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:04 AM
Points: 39, Visits: 90
so from everything I heard/read, would that be fair to conclude that option 2 is the way to go in the majority of cases?

2) where databasename ='myDBname'
and applicationname NOT LIKE 'SQLAgent%'
and applicationname NOT LIKE ' SQL Server Log Shipping%'
and applicationname NOT LIKE 'Spotlight Diagnostic%'



Voldemar
likes to play chess
Post #1536849
Posted Friday, January 31, 2014 9:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
VoldemarG (1/31/2014)
so from everything I heard/read, would that be fair to conclude that option 2 is the way to go in the majority of cases?

2) where databasename ='myDBname'
and applicationname NOT LIKE 'SQLAgent%'
and applicationname NOT LIKE ' SQL Server Log Shipping%'
and applicationname NOT LIKE 'Spotlight Diagnostic%'


If you need the wildcard then yes that would be the best way. Option 1 is nonSARGable and option 3 is not the same thing.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1536865
Posted Friday, January 31, 2014 9:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 2,763, Visits: 5,909
Sean Lange (1/31/2014)
VoldemarG (1/31/2014)
so from everything I heard/read, would that be fair to conclude that option 2 is the way to go in the majority of cases?

2) where databasename ='myDBname'
and applicationname NOT LIKE 'SQLAgent%'
and applicationname NOT LIKE ' SQL Server Log Shipping%'
and applicationname NOT LIKE 'Spotlight Diagnostic%'


If you need the wildcard then yes that would be the best way. Option 1 is nonSARGable and option 3 is not the same thing.

Note that you have a leading space on your second condition and that if you add a leading wildcard it will become nonSARGable as well. So, what's the best option? I can't give a definite answer.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536870
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse