Performance of LIKE vs LEFT/LEN | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance of LIKE vs LEFT/LEN

Hi, I’ve got a slightly unusual query I need to perform on a database table. Suppose I’ve got some string ‘STRING’, then I need to return all rows such that "myColumn" is a prefix of or equal to the string…. so we’d return all rows where myColumn contained exactly S
ST
STR
STRI
STRIN
STRING but obviously STRING is a variable value. I’ve found two ways of doing this: LEFT(@var, LEN(myColumn)) = myColumn @var LIKE myColumn + ‘%’ However, I’m not sure how the performance of these two compare, or how SQL Server optimizes LIKE operators where the column is on the right hand side rather than the left. Could anyone offer any insight? Cheers, ~ James Crowley
http://www.developerfusion.com/
I was reading some information about VB, and that article said: use LEN instead LIKE because give more perfomance.
But, wait for developers members to have more information.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
The general rule is that in WHERE clauses you must avoid maipulating the data that is coming from the table field. That effectively blocks the use of indexes and statistics to optimise the query, so the query is potentially slower. Basically SQL Server needs go through all records in the table to truncate data before it can evaluate your WHERE statement. That would suggest that
LEFT(@var, LEN(myColumn)) = myColumn
is slower than
@var LIKE myColumn + ‘%’
Ok, thanks <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Actually, for what you are looking for, I think patindex would also work. I’m using a temp value, you can join declare @myval varchar(20) set @myval = ‘s’ SELECT mycolumn
FROM mytable
where PATINDEX(@myval + ‘%’, mycolumn)> 0 I dont know how this would affect performance though over the like or left. Check it out and see. I ran it on a large table, on the surname (which has a clustered indexed) and it flew through it in no time.
Using the others, might not use the clustered (or even non clustered) index the same way.
Some sample data
create table t (s varchar(30)) insert t select ‘S’
insert t select ‘ST’
insert t select ‘STR’
insert t select ‘STRI’
insert t select ‘STRIN’
insert t select ‘STRING’
Create a function to return a table containing your expanded variable as columns
Create Function f_expand(@str varchar(255))
RETURNS TABLE
AS
RETURN (
SELECT substring(@str,1,ID) Parts
FROM (SELECT 1 ID UNION SELECT 2 UNION SELECT 2 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Numbers
WHERE ID <= len(@str)
) SELECT *
FROM dbo.f_expand(‘STRING’) Parts
—–
S
ST
STRI
STRIN
STRING
Then you can join against the function with a exact match on your mycolumn table which should be about as fast as you can get.
The function itself takes about .0002 seconds.
SELECT s
FROM t,
dbo.f_expand(‘STRING’) e
WHERE s = e.Parts s
——-
S
ST
STRI
STRIN
STRING
One thing to note. The function I wrote used a derived table called numbers containing a sequence of integers from 1 to 9. That will limit it to strings 9 characters long. You will want to create a table of numbers, with more than 9 numbers for your function. See here http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4890 for a few alternatives to the derived numbers table.
I had a table join that used LIKE in this way that was taking 2 mins 38 seconds. I tested all 3 ways and here are my results. LIKE: 2 mins 38 seconds
PATINDEX: 2 mins 39 seconds
LEFT / LEN: 2 mins 20 seconds Not much difference …
i work in the telecom industry and i had a similar problem. I tried "like" and "left/len" when trying to join a lookup table with about 10k rows with a table with about 500 million rows…. the performance was terrible….
but i figured out a way to speed it up about 200x…
suppose your lookup table x has columns: area_code, zone_name
suppose the other table y has columns: telephone_number
and you basically want to do this: select telephone_number, zone_name from x inner join y on left(telephone_number, len(area_code))=area_code;
instead, what you can do is create indexed views beforehand like so:
create view x_01 with schemabinding as select area_code, zone_name from x where len(area_code)=1;
create view x_02 with schemabinding as select area_code, zone_name from x where len(area_code)=2;
create view x_03 with schemabinding as select area_code, zone_name from x where len(area_code)=3;
create view x_04 with schemabinding as select area_code, zone_name from x where len(area_code)=4;
create view x_05 with schemabinding as select area_code, zone_name from x where len(area_code)=5;
create view x_06 with schemabinding as select area_code, zone_name from x where len(area_code)=6;
create view x_07 with schemabinding as select area_code, zone_name from x where len(area_code)=7;
i won’t explain how to index the views, i’m assuming u know how to do that… basically create indexes on the area_code column of all the views.
then, the final step is to do the join, but this time:
select telephone_number, zone_name from x_01 inner join y on left(telephone_number, 1)=area_code
union
select telephone_number, zone_name from x_02 inner join y on left(telephone_number, 2)=area_code
union
select telephone_number, zone_name from x_03 inner join y on left(telephone_number, 3)=area_code
union
select telephone_number, zone_name from x_04 inner join y on left(telephone_number, 4)=area_code
etc etc etc;
i know, i know… ur like, WTF??? it’s a bit tortuous, but it can be automated using C# or something else… you wouldn’t believe how fast SQL server can do this…. literally 275x times speed up in my case.
Welcome to the forum and thanks for sharing your experience!
If the column you’re selecting has indexes on it, you should avoid all functions such as LEFT, SUBSTRING, etc, and instead use LIKE. Why? LEFT, SUBSTRING, and similar functions are just that – functions. They do not allow the database engine to take advantage of indexes.
This website has an example:
Code:
-- Do not do this if there is an index on Column3
SELECT Column1, Column2
FROM Table1
WHERE LEFT(Column3, 1) = 'a'
-- Do not do this either if there is an index on Column3
SELECT Column1, Column2
FROM Table1
WHERE SUBSTRING(Column3, 1, 1) = 'a'
-- Instead, use LIKE
SELECT Column1, Column2
FROM Table1
WHERE Column3 LIKE 'a%'
Welcome to the forum and thanks for sharing your experience!
Check this thread is 5 years old.:)
Thanks Luis.
May be 5 years old but I believe the basic practice hasn’t changed. Besides, I found this forum and that blog post doing a search…someone else in a few months time might do the same search and want to read the info. I didn’t think it would hurt with further examples.:cool:
Welcome to the forum and thanks for sharing your experience!
Check this thread is 5 years old.:)
]]>