Which is faster CHARINDEX or LIKE ?????

  • Which is faster CHARINDEX or LIKE ?????

    where fld_description is nvarchar(500)

     

    where fld_description like '%HOLD%'

    or

    where charindex('hold',fld_description) > 0

     

     

  • Should be the same speed... with the wildcard at the front of the like, a scan is required to find all possible matches. Using charindex will always result in a scan. However if you where to do like 'abc%', this could result in an index seek. Again however, if you were to do something like this in a proc you'd get a scan again :

    where somecol like @SomeParam + '%'

    This is because the optimizer must take into consideration that the parameter could containt a wildcard at the beginning of the string.

  • thanks.. thats what i thought but wasnt sure..

  • Just an answer to Rémi's comment:

    SQLserver will still use the index if you have this kind of query:

    declare @v varchar(50)

    set @v = 'E'

    select * from dbo.T_CUS_customer

    where cus_code_char like @v + '%'

    StmtText                                                                                                                                                                                                                                                                       

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[LHC].[dbo].[T_CUS_customer]))

           |--Nested Loops(Inner Join, OUTER REFERENCES[Expr1009], [Expr1010], LikeRangeInfo([@v]+'%', NULL)))

                |--Compute Scalar(DEFINE[Expr1009]=Convert(LikeRangeStart([@v]+'%', NULL)), [Expr1010]=Convert(LikeRangeEnd([@v]+'%', NULL)), [Expr1007]=LikeRangeInfo([@v]+'%', NULL)))

                |    |--Constant Scan

                |--Index Seek(OBJECT[LHC].[dbo].[T_CUS_customer].[IX_1_CUS_code_char]), SEEK[T_CUS_customer].[cus_code_char] > [Expr1009] AND [T_CUS_customer].[cus_code_char] < [Expr1010]),  WHERElike([T_CUS_customer].[cus_code_char], [@v]+'%', NULL)) ORDERED FORWARD)

    and if you are using a clustered index you can even avoid the bookmark lookup:

    declare @v varchar(50)

    set @v = 'E'

    select * from dbo.T_CUS_customer

    where cus_name like @v + '%'

    StmtText                                                                                                                                                                                                                                                  

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      |--Nested Loops(Inner Join, OUTER REFERENCES[Expr1009], [Expr1010], LikeRangeInfo([@v]+'%', NULL)))

           |--Compute Scalar(DEFINE[Expr1009]=Convert(LikeRangeStart([@v]+'%', NULL)), [Expr1010]=Convert(LikeRangeEnd([@v]+'%', NULL)), [Expr1007]=LikeRangeInfo([@v]+'%', NULL)))

           |    |--Constant Scan

           |--Clustered Index Seek(OBJECT[LHC].[dbo].[T_CUS_customer].[I_C_CUS]), SEEK[T_CUS_customer].[cus_name] > [Expr1009] AND [T_CUS_customer].[cus_name] < [Expr1010]),  WHERElike([T_CUS_customer].[cus_name], [@v]+'%', NULL)) ORDERED FORWARD)



    Bye
    Gabor

  • I can't replicate that index seek here..

    Can you post the ddl and some sample data so I can understand what I'm doing wrong?

  • Watch Gabor's code

    declare @v varchar(50)

    set @v = 'E'

    select * from dbo.T_CUS_customer

    where cus_code_char like @v + '%'

    There is no wildcard at the beginning, only at the end. So, if there is an index on that cus_code_char column, it's likely to be used.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I know that but this query results in a scan :

    Declare @name as varchar(10)

    set @Name = 'id'

    Select * from dbo.SysColumns where name like @Name + '%'

  • There isn't a separate index on name. This column is at second position in another nonclustered indexes. So, I would say, this is somewhat expected behaviour.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • That clears it up... obviously works now on another table.

Viewing 9 posts - 1 through 9 (of 9 total)

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