sql server 2008 - CHARINDEX vs LIKE search gives very different performance, why? -

we use entity frameworks db access , when "think" statement - generates charindex stuff. so, here 2 simple queries, after simplified them prove point on our server:

-- runs 2 seconds select * locaddress address1 '%1124%'  -- runs 16 seconds select * locaddress ( cast(charindex(lower(n'1124'), lower([address1])) int)) = 1 

table contains 100k records right now. address1 varchar(100) field, nothing special.

here snip of 2 plans side side. doesn't make sense, shows 50% , 50% execution times 1:8

i searched online , general advice use charindex instead of like. in our experience it's opposite. question causing , how can fix without code change?

first, can see both queries identical , neither can use index. charindex , perform same wildcard. ex: %yourvalue%. however, there performance varies when use wildcard 'yourvalue%'. here, operator perform faster charindex because may allow partial scan of index. now, in case, both queries same there performance difference because of following possible reason:

statistics: sql server maintains statistics sub string in string columns use operator not usable charindex. in case, operator work faster charindex. can force sql server use index charindex proper table hints

ex: locaddress (index (index_name))

read more here, in section "string summary stastics" says:

sql server 2008 includes patented technology estimating selectivity of conditions. builds statistical summary of substring frequency distribution character columns (a string summary). includes columns of type text, ntext, char, varchar, , nvarchar. using string summary, sql server can accurately estimate selectivity of conditions pattern may have number of wildcards in combination.