实体框架查询比较慢，但是 SqlQuery 中的 SQL 比较快

小开

最佳答案

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


...
}

小开

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.

小开

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

小开

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.

小开

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.

小开

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.

小开

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.