CHARINDEX vs LIKE search gives very different performance, why?

Question

We use Entity Frameworks for DB access and when we "think" LIKE statement - it actually generates CHARINDEX stuff. So, here is 2 simple queries, after I simplified them to prove a point on our certain server:

-- Runs about 2 seconds
SELECT * FROM LOCAddress WHERE Address1 LIKE '%1124%' 
-- Runs about 16 seconds
SELECT * FROM LOCAddress WHERE ( CAST(CHARINDEX(LOWER(N'1124'), LOWER([Address1])) AS int)) = 1

Table contains about 100k records right now. Address1 is VarChar(100) field, nothing special.

Here is snip of 2 plans side by side. Doesn't make any sense, shows 50% and 50% but execution times like 1:8

I searched online and general advice is to use CHARINDEX instead of LIKE. In our experience it's opposite. My question is what causing this and how we can fix it without code change?

Answer 1

I will answer my own question since it was hard to find correct answer and I was pointed to the problem by SQL Server 2012 Execution Plan output. As you see in original question - everything looks OK on surface. This is SQL Server 2008.

When I run same query on 2012 I got warning on CHARINDEX query. Problem is - SQL Server had to do type conversion. Address1 is VarChar and query has N'1124' which is Unicode or NVarChar. If I change this query as so:

SELECT * 
FROM LOCAddress 
WHERE (CAST(CHARINDEX(LOWER('1124'), LOWER([Address1])) AS int)) 

It then runs same as LIKE query. So, type conversion that was caused by Entity Framework generator was causing this horrible hit in performance.