T-SQL speed comparison between LEFT() vs. LIKE operator

Question

I'm creating result paging based on first letter of certain nvarchar column and not the usual one, that usually pages on number of results.

And I'm not faced with a challenge whether to filter results using LIKE operator or equality (=) operator.

select *
from table
where name like @firstletter + '%'

vs.

select *
from table
where left(name, 1) = @firstletter

I've tried searching the net for speed comparison between the two, but it's hard to find any results, since most search results are related to LEFT JOINs and not LEFT function.

Have you looked at the query plans for both? Have you run your own benchmark? — Oded, Oct 31 '10 at 11:06
No I haven't. I thought I wasn't the first one to ask myself this question so I though someone else might've tested it already. Because of LEFT mostly relating to joins I just can't seem to find this data. Hence the question if someone has the link somewhere. I suspect LEFT should be much faster — Robert Koritnik, Oct 31 '10 at 11:37
The first one (using `LIKE`) has an opportunity to use an index on `name`, whereas the second one (function against column value) doesn't. — Marcus Adams, Apr 30 '14 at 13:56
@MarcusAdams is correct. When using any function such as LEFT, SUBSTRING, etc, the server cannot use indexes. — Free Coder 24, May 27 '14 at 13:04
The sargability of `like` makes it a superior default choice in SQL Server. Also even if no index exists it can make better use of the string statistics on columns and give better cardinality estimates for number of matching rows. — Martin Smith, Jul 07 '14 at 05:43

score 19 · Answer 1 · answered Jul 07 '14 at 05:02

19

"Left" vs "Like" -- one should always use "Like" when possible where indexes are implemented because "Like" is not a function and therefore can utilize any indexes you may have on the data.

"Left", on the other hand, is function, and therefore cannot make use of indexes. This web page describes the usage differences with some examples. What this means is SQL server has to evaluate the function for every record that's returned.

"Substring" and other similar functions are also culprits.

answered Jul 07 '14 at 05:02

1

Whilst I understand and agree, I feel the MySQL codebase should be intelligent enough to convert simple LEFT() calls to LIKE statements, or to utilise an index on the first operand of LEFT(). Like the OP I just stumbled on the slowness of LEFT() in my code, and was surprised that it's not optimized to be similar to LIKE in performance. – Codemonkey Jan 15 '20 at 17:13

score 9 · Accepted Answer · answered Oct 31 '10 at 11:11

9

Your best bet would be to measure the performance on real production data rather than trying to guess (or ask us). That's because performance can sometimes depend on the data you're processing, although in this case it seems unlikely (but I don't know that, hence why you should check).

If this is a query you will be doing a lot, you should consider another (indexed) column which contains the lowercased first letter of name and have it set by an insert/update trigger.

This will, at the cost of a minimal storage increase, make this query blindingly fast:

select * from table where name_first_char_lower = @firstletter

That's because most database are read far more often than written, and this will amortise the cost of the calculation (done only for writes) across all reads.

It introduces redundant data but it's okay to do that for performance as long as you understand (and mitigate, as in this suggestion) the consequences and need the extra performance.

answered Oct 31 '10 at 11:11

paxdiablo

854,327
234
1,573
1,953

This is a really nice idea although data from this table will only be read this way in less than 5% of all reads. All the others are going to be filtered on other things not name... So index doesn't seem to be feasible. – Robert Koritnik Oct 31 '10 at 11:36
An index on the name column can be used for `name like 'a%'`, no need for the computed column. – Andomar Oct 31 '10 at 12:33
Andomar, good point - that wouldn't handle the case-insensitive nature, although I'm aware some DBMS' allow a calculated index on the column (so it would be automagically lowercased). – paxdiablo Oct 31 '10 at 12:38
1

Whether it is case sensitive or not depends on column collation in SQL Server. The question is tagged TSQL. – Martin Smith Jul 07 '14 at 05:45

score 6 · Answer 3 · answered Jul 17 '13 at 13:36

I had a similar question, and ran tests on both. Here is my code.

where (VOUCHER like 'PCNSF%'
    or voucher like 'PCLTF%'
    or VOUCHER like 'PCACH%'
    or VOUCHER like 'PCWP%'
    or voucher like 'PCINT%')

Returned 1434 rows in 1 min 51 seconds.

vs

where (LEFT(VOUCHER,5) = 'PCNSF'
    or LEFT(VOUCHER,5)='PCLTF'
    or LEFT(VOUCHER,5) = 'PCACH'
    or LEFT(VOUCHER,4)='PCWP'
    or LEFT (VOUCHER,5) ='PCINT')

Returned 1434 rows in 1 min 27 seconds

My data is faster with the left 5. As an aside my overall query does hit some indexes.

Someone else had the same idea and wrote their own [benchmark test against LEFT, RIGHT, LIKE, SUBSTRING, and CHARINDEX](http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex). — , Dec 01 '15 at 08:31

score 1 · Answer 4 · answered Jul 27 '16 at 07:00

I would always suggest to use like operator when the search column contains index. I tested the above query in my production environment with select count(column_name) from table_name where left(column_name,3)='AAA' OR left(column_name,3)= 'ABA' OR ... up to 9 OR clauses. My count displays 7301477 records with 4 secs in left and 1 second in like i.e where column_name like 'AAA%' OR Column_Name like 'ABA%' or ... up to 9 like clauses.

Calling a function in where clause is not a best practice. Refer http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/

score 0 · Answer 5 · answered Jun 23 '19 at 04:23

Entity Framework Core users

You can use EF.Functions.Like(columnName, searchString + "%") instead of columnName.startsWith(...) and you'll get just a LIKE function in the generated SQL instead of all this 'LEFT' craziness!

Depending upon your needs you will probably need to preprocess searchString.

See also https://github.com/aspnet/EntityFrameworkCore/issues/7429

This function isn't present in Entity Framework (non core) EntityFunctions so I'm not sure how to do it for EF6.

T-SQL speed comparison between LEFT() vs. LIKE operator

5 Answers5

Entity Framework Core users

Linked