CODEWITHSUNDEEP
performanceentity-frameworkef-code-first
Brian Sullivan
Brian Sullivan

Reputation: 28553

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

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?

Upvotes: 126

Views: 85713

Answers (11)

Simon_Weaver
Simon_Weaver

Reputation: 145880

Here's a fun one! It could be your WiFi connection!

It turns out I had some queries that are about 1400 bytes in length (according to SSMS client statistics) that were taking 500ms+ instead of around 25ms.

The query was just a SELECT for a simple table with only a few dozen rows. I completely commented out all but one of the included columns and it was still very slow. In fact SELECT * was way faster than the SELECT with all the columns names.

I eventually realized the only explanation was something to do with my home Wifi conncection. I don't fully understand what but must be something like packet size. Switching to ethernet fixed the problem.

This took 520ms consistently

enter image description here

This took 25ms (the only difference is less xxxx sent)

enter image description here

The real crazy thing is that much longer queries would again take around 25ms. I'm still quite puzzled but at least I have a solution.

Upvotes: 0

SoItBegins
SoItBegins

Reputation: 534

This happened to me again in a similar part of the code I was working on (see my other answer here for how I solved it the first time).

This time, the culprit was the query optimizer forgetting to use an index. When running the query in most cases, or under SSMS, the correct indexes would be used. Only occasionally under specific circumstances would it fail.

When I used an SQL profiler and set it to show execution plans, it showed that in the long-running queries, the query optimizer was ignoring the index that had been set and doing a table scan instead (of 6 million+ rows!), drastically increasing the run time.

The fix for this was to turn the query into a table-valued function with an index hint, forcing the correct index, and call that function from within EF. This resolved the problem, though it's somewhat of a drastic solution. I don't know why the query optimizer decided to ignore the index, but this guarantees it will not in the future.

Upvotes: 0

tim_p
tim_p

Reputation: 31

I've had a similar experience unsolvable with other replies in here and went for the direct SQL query outside the EF

Upvotes: 0

Erik123
Erik123

Reputation: 201

In my case, it was a deadlock cause by a calling method that forgot to await my async method. The query.ToArrayAsync() was executed but never returned to the caller thread.

Upvotes: 0

SoItBegins
SoItBegins

Reputation: 534

Here is a very strange EF bug that happened to me and is one more reason this could go south.

I had the following line of code:

var fvQuery = db.DataValues.Where(x => x.DataId == dataId && x.Scope == scope && x.Start <= endTime && startTime <= x.End)

Note that the last part of the Where clause has the terms reversed, with the value being compared against on the left side!

This almost always worked, then bugged out in rare circumstances, with the query taking several minutes in Entity Framework before it was sent to the DB, if at all.

The fix:

var fvQuery = db.DataValues.Where(x => x.DataId == dataId && x.Scope == scope && x.Start <= endTime && x.End >= startTime)

Swapping the order of the terms in the last part of the WHERE clause seems to have fixed it.

Upvotes: 2

cryss
cryss

Reputation: 4499

The reason of slowing down my EF queries 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 the following:

long? userId = 10;
long userIdValue = userId.Value;

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

gives incredible results: it took only 50ms to complete. It looks like a bug in EF.

Upvotes: 64

Oskar Sj&#246;berg
Oskar Sj&#246;berg

Reputation: 2878

I had this problem as well. It turns out the culprit in my case was SQL-Server parameter sniffing.

The first clue that my problem was in fact due to parameter sniffing was that running the query with "set arithabort off" or "set arithabort on" yielded drastically different execution times in Management Studio. This is because ADO.NET by default uses "set arithabort off" and Management Studio defaults to "set arithabort on". The query plan cache keeps different plans depending on this parameter.

I disabled query plan caching for the query, with the solution you can find here.

Upvotes: 5

user2622095
user2622095

Reputation: 39

I also came across this with a complex ef query. One fix for me which reduced a 6 second ef query to the sub second sql query it generated was to turn off lazy loading.

To find this setting (ef 6) go to the .edmx file and look in the Properties -> Code generation -> Lazy Loading Enabled. Set to false.

Massive improvement in performance for me.

Upvotes: 3

Matt
Matt

Reputation: 325

If you're using the fluent mapping, you can use IsUnicode(false) as part of the configuration to get the same effect -

http://msdn.microsoft.com/en-us/data/jj591617.aspx#1.9

http://msdn.microsoft.com/en-us/library/gg696416%28v=vs.103%29.aspx

Upvotes: 12

Vladimir Gedgafov
Vladimir Gedgafov

Reputation: 41

I had the same problem (the query is fast when executed from SQL manager) but when executed from EF the timeout expires.

Turns out that the entity (which was was created from the view) had wrong entity keys. So the entity had duplicate rows with the same keys, and I guess it had to do grouping on the background.

Upvotes: 4

Brian Sullivan
Brian Sullivan

Reputation: 28553

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; }

    ...
}

Upvotes: 135

Related Questions