string.contains in LINQ to entities causes very slow execution

Question

This LINQ expression:

var result = entities.Cases
            .Where(c => c.House.Address.Contains("otte"))
            .ToList();

Executes this sql at the server:

SELECT 
...
--rows
...
FROM  [dbo].[Case] AS [Extent1]
INNER JOIN [dbo].[House] AS [Extent2] ON [Extent1].[HouseID_FK] = [Extent2].[HouseID]
WHERE [Extent2].[Address] LIKE '%otte%'

This takes about 100 ms to complete.

This LINQ expression:

var value = "otte";
var result = entities.Cases
            .Where(c => c.House.Address.Contains(value))
            .ToList();

Executes this sql at the server:

exec sp_executesql N'SELECT 
...
--rows
...
FROM  [dbo].[Case] AS [Extent1]
INNER JOIN [dbo].[House] AS [Extent2] ON [Extent1].[HouseID_FK] = [Extent2].[HouseID]
WHERE [Extent2].[Address] LIKE @p__linq__0 ESCAPE N''~''',N'@p__linq__0     nvarchar(4000)',@p__linq__0=N'%otte%'

This takes about 1400 ms to complete.

If i declare "value" as a constant i can make it generate the "fast" sql too, but i want to be able to change the value of "value" at runtime. Is there any way to force Entity Framework to not generate "exec sp_executesql" - style sql as this is obviously much slower?

Maybe this helps since it is related: http://stackoverflow.com/questions/9270490/entity-framework-4-2-exec-sp-executesql-does-not-use-indexes-parameter-sniffing — Tim Schmelter, Aug 30 '12 at 11:20
Might be an issue of ["parameter sniffing"](http://stackoverflow.com/a/10933405/21567). — Christian.K, Aug 30 '12 at 11:38
I tried OPTION(RECOMPILE) and it made the query execute about twice as fast. It is still too slow to be acceptable, because i actually include a number of other tables in the query, which enhances the problem. For now we've found another solution to our problem, which does not involve a "contains" - query. — ThomasH, Sep 03 '12 at 07:07

score 1 · Answer 1 · answered Sep 14 '12 at 09:34

Following on from here, I would suggest that SQL Server (LINQ/EF) is trying to outsmart you and can tell that you're going to be re-using that query just with a different parameter, and utilising sp_executesql is the currently recommended way of running that query consistently (pretty sure it's so that SQL Server can efficiently cache the query). You might lose out on this one query, but make the penalty back through re-use?

You could look into modifying the database to require either simple or forced parametrisation (you want 'forced'), but this may incur a significant performance decrease on any other queries, but YMMV.

I would suggest avoiding any string comparisons within SQL Server itself... RAM is more than plentiful these days and I've found that occasionally it can be faster to just lift the two corresponding data-sets and compare the string values in C# (again, YMMV)

Failing that, experiment with SOUNDEX or DIFFERENCE vs LIKE or CHARINDEX to see if there are any gains to be made in regards to performance, according your requirements.

Good luck.

string.contains in LINQ to entities causes very slow execution

1 Answers1