EntityFramework 6.1.1与Linq性能问题

Question

我使用EntityFramework从我的mssql数据库中选择数据。我的查询看起来像这样:

int param = 123456;
using (var context = new DatabaseContext())
{
    var query = context.Table.AsQueryable();
    var result = query.Where(o => o.Id == param).ToList();
}

查询耗时约10秒。

using (var context = new DatabaseContext())
{
    var query = context.Table.AsQueryable();
    var result = query.Where(o => o.Id == 123456).ToList();
}

查询时间小于1秒。

我刚刚发现EntityFramework生成了两个不同的查询。

查询1:

SELECT TOP (20) 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name],  
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], row_number() OVER (ORDER BY [Project1].[Id] DESC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name]
        FROM [dbo].[Table] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Id] DESC
-- p__linq__0: '2932323' (Type = Int32, IsNullable = false)

查询2:

SELECT TOP (20) 
[Filter1].[Id] AS [Id], 
[Filter1].[Name] AS [Name]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
    FROM [dbo].[Table] AS [Extent1]
    WHERE 2932323 = [Extent1].[Id]
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[Id] DESC

是否有一种方法可以加快第一种或另一种方法?

Answer 1

从sql查询来看，这不仅是EF相关的性能问题，而且是sql服务器性能问题。

你应该考虑以下几点:

• 作为Id不是一个PK:创建一个索引;
• 使用EXEC sp_updatestats更新统计数据，帮助db服务器制定更好的执行计划。

btw:查询1在SSMS中需要多长时间?

Answer 2

我找到解决办法了。

我使用sp_recompile删除了所有的执行计划，现在一切正常。

谢谢你的帮助。

p .