Entity Framework query slow, but same SQL in SqlQuery is fast

ef-code-first entity-framework performance

Question

I'm seeing some really strange perf related to a very simple query using Entity Framework Code-First with .NET framework version 4. The LINQ2Entities query looks like this:

 context.MyTables.Where(m => m.SomeStringProp == stringVar);

This takes over 3000 milliseconds to execute. The generated SQL looks very simple:

 SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
 ...
 FROM [MyTable] as [Extent1]
 WHERE [Extent1].[SomeStringProp] = '1234567890'

This query runs almost instantaneously when run through Management Studio. When I change the C# code to use the SqlQuery function, it runs in 5-10 milliseconds:

 context.MyTables.SqlQuery("SELECT [Extent1].[ID] ... WHERE [Extent1].[SomeStringProp] = @param", stringVar);

So, exact same SQL, the resulting entities are change-tracked in both cases, but wild perf difference between the two. What gives?

1
85
3/31/2016 4:00:16 PM

Accepted Answer

Found it. It turns out it's an issue of SQL data types. The SomeStringProp column in the database was a varchar, but EF assumes that .NET string types are nvarchars. The resulting translation process during the query for the DB to do the comparison is what takes a long time. I think EF Prof was leading me astray a bit here, a more accurate representation of the query being run would be the following:

 SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
 ...
 FROM [MyTable] as [Extent1]
 WHERE [Extent1].[SomeStringProp] = N'1234567890'

So the resulting fix is to annotate the code-first model, indicating the correct SQL data type:

public class MyTable
{
    ...

    [Column(TypeName="varchar")]
    public string SomeStringProp { get; set; }

    ...
}
85
4/2/2013 7:39:05 PM

Popular Answer

The reason of slowing down my queries made in EF was comparing not nullable scalars with nullable scalars:

long? userId = 10; // nullable scalar

db.Table<Document>().Where(x => x.User.Id == userId).ToList() // or userId.Value
                                ^^^^^^^^^    ^^^^^^
                                Type: long   Type: long?

That query took 35 seconds. But a tiny refactoring like that:

long? userId = 10;
long userIdValue = userId.Value; // I've done that only for the presentation pursposes

db.Table<Document>().Where(x => x.User.Id == userIdValue).ToList()
                                ^^^^^^^^^    ^^^^^^^^^^^
                                Type: long   Type: long

gives incredible results. It took only 50ms to complete. It's possible that it is a bug in EF.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow