Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

Question

Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

Renée

SSC-Addicted

Points: 470
More actions
June 23, 2011 at 9:43 am

#239856

Hi all,
I'm a SQL newbie who sort of inherited a 2005 db. I've got a query that returns a SUBSTRING of a field value in one column. I need to run this query today to only pull records that have a specific substring in that column.
The column containing the values is assigned an alias in the SELECT statement, and I've now learned the SELECT statement isn't processed until after the WHERE statement (yes, that's how new I am!!), so [Column Alias] = 'NCB' won't work in the WHERE statement.
So, which is more efficient?
(1)... WHERE SUBSTRING(fldField,1,3) = 'NCB'
or
(2)... WHERE fldField LIKE 'NCB%'
...or maybe something else I haven't thought of?
I know having the wildcard at the beginning of a LIKE pattern is slower, but since that's not the case here, I have no clue which of the two options above are faster for the SQL engine.
Thanks so much!
[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

If the column is indexed the like operator seems to work faster , however execution plans are the same , basically a index scan

Jayanth Kurup[/url]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

As you are looking from the start of the column, you can use this

WHERE LEFT(fldField,3) = 'NCB'

- just a little tidier and avoids wildcards.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Renée SSC-Addicted Points: 470 More actions · Answer 3

Thanks for the quick response. I suspected there might not be much difference. This db is minimally indexed because of the way it is completely repopulated twice an hour (not my design, so I'm not fully familiar with the logic).

[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Renée SSC-Addicted Points: 470 More actions · Answer 4

Phil, you were posting as I was typing my previous post. Thanks! Tidier, indeed.

[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There's a massive difference between the two.

The first there's a function applied to the column. That makes the predicate non-SARGable, meaning SQL can NEVER use an index seek operation to satisfy that.

The second is SARGable meaning that SQL can (not necessarily will) use an index seek should there be a suitable index to seek on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

GilaMonster (6/23/2011)
There's a massive difference between the two.
The first there's a function applied to the column. That makes the predicate non-SARGable, meaning SQL can NEVER use an index seek operation to satisfy that.
The second is SARGable meaning that SQL can (not necessarily will) use an index seek should there be a suitable index to seek on.

This suggests that LEFT should never be used because LIKE is always either at least as fast, or faster, in terms of performance. Is that correct?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Correct.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

roshan.zanwar SSChasing Mays Points: 617 More actions · Answer 8

As someone already mentioned if the column is an indexed spetially if it is cluster index then the LIKE keyword will work faster coz it will directly search from that perticular index as you are searching for

LIKE 'char%'

but if u are searching for like'%char' then clustered index will not help for faster result.

How ever you can check for execution time for both & check which one working faster. That one is good practice.

_watching Mr or Mrs. 500 Points: 530 More actions · Answer 9

If you're interested in updated speed comparisons on how fast each is (well, on SQL 2014 anyway), this ➡ LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article with benchmarks makes for a great read.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

_watching (10/14/2015)
If you're interested in updated speed comparisons on how fast each is (well, on SQL 2014 anyway), this ➡ LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article with benchmarks makes for a great read.

ChrisM@Work (10/7/2015)
It's quite a comprehensive test. A note about SARGable LIKE queries within the article would improve it - or a link to the same statement elsewhere in the blog. If you're Dave Lozinski, then your blog post suggestion that temp tables always get written to disk (and table variables don't) requires an update[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

ChrisM@Work (10/15/2015)
_watching (10/14/2015)
If you're interested in updated speed comparisons on how fast each is (well, on SQL 2014 anyway), this ➡ LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article with benchmarks makes for a great read.
ChrisM@Work (10/7/2015)
It's quite a comprehensive test. A note about SARGable LIKE queries within the article would improve it - or a link to the same statement elsewhere in the blog. If you're Dave Lozinski, then your blog post suggestion that temp tables always get written to disk (and table variables don't) requires an update[/url].

Chris - I'm not sure Dave Lozinski knows what SARGable means, since not one of queries used in the test is. No disrespect intended, but for all of the setup being done (and thumbs up on putting together a nicely formatted testbed) - every one one of these queries turns back to a table scan, since it's performing a "like '%a' OR like 'a%'" search.

With no help whatsoever from indexing, it's interesting to see that using the function is faster than not using it, but otherwise I am not sure what else it proves. With indexing though - LIKE wins fairly handily.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

To be honest, I trust no one that uses pure RBAR to create test data nor do I trust fixed width/grooved test data where the data is usually variable in length in real life. It's almost always an indication that something else will be missed or done incorrectly that will end up in the results being skewed.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

_watching Mr or Mrs. 500 Points: 530 More actions · Answer 13

Matt Miller (#4) (10/15/2015)

ChrisM@Work (10/15/2015)

_watching (10/14/2015)
If you're interested in updated speed comparisons on how fast each is (well, on SQL 2014 anyway), this ➡ LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article with benchmarks makes for a great read.

ChrisM@Work (10/7/2015)
With no help whatsoever from indexing, it's interesting to see that using the function is faster than not using it, but otherwise I am not sure what else it proves. With indexing though - LIKE wins fairly handily.

Hmmm... not why you're saying LIKE wins fairly handily with indexing where according to the page, the table with the first results on a clustered index column, LIKE is more than 4x slower.

??

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

_watching (10/15/2015)

Matt Miller (#4) (10/15/2015)

ChrisM@Work (10/15/2015)
_watching (10/14/2015)
If you're interested in updated speed comparisons on how fast each is (well, on SQL 2014 anyway), this ➡ LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article with benchmarks makes for a great read.
ChrisM@Work (10/7/2015)
With no help whatsoever from indexing, it's interesting to see that using the function is faster than not using it, but otherwise I am not sure what else it proves. With indexing though - LIKE wins fairly handily.
Hmmm... not why you're saying LIKE wins fairly handily with indexing where according to the page, the table with the first results on a clustered index column, LIKE is more than 4x slower.
??

Did you read Matt Miller's comment a few posts above? Try making the query SARGable, re test and post the results.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin