SQL Server Performance

Performance of LIKE vs LEFT/LEN

Discussion in 'T-SQL Performance Tuning for Developers' started by vbweb, Jul 14, 2004.

  1. vbweb New Member

    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/
  2. Luis Martin Moderator

    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.

  3. Adriaan New Member

    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 + '%'

  4. vbweb New Member

    Ok, thanks <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  5. ChrisFretwell New Member

    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.



  6. vaxman New Member

    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.
  7. Christian_Wade New Member

    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 ...
  8. hellznrg New Member

    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.
  9. FrankKalis Moderator

    Welcome to the forum and thanks for sharing your experience!
  10. Johnny Boy New Member

    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%'
    
  11. Luis Martin Moderator

    Welcome to the forum and thanks for sharing your experience!
    Check this thread is 5 years old.:)
  12. Johnny Boy New Member

    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:

Share This Page