实体框架Linq查询的问题:在SSMS中立即运行，在EF LINQ中运行8-10

Question

我在SQL中获得了以下查询(变量名称被混淆)，该查询试图获取值(Ch、Wa、Bu、Hi)，从而得到最多的Pi条目(cnt)。

select top 1 Pi.Ch, Pi.Wa, Pi.Bu, Pi.Hi, COUNT(1) as cnt 
from Product, Si, Pi
where Product.Id = Si.ProductId
and Si.Id = Pi.SiId
and Product.Code = @CodeParameter
group by Pi.Ch, Pi.Wa, Pi.Bu, Pi.Hi
order by cnt desc

它在我们生产数据库上的SQL管理工作室中立即运行。我已经成功地用C# LINQ和实体框架编写了几种方式的代码，但是每种方式代码运行时间都是8-10秒。一次尝试是以下代码(在没有打印的情况下执行，因为一个调用会产生相同的性能结果)：

using(var context = new MyEntities()){
    var query = context.Products
        .Where(p => p.Code == codeFromFunctionArgument)
        .Join(context.Sis, p => p.Id, s => s.ProductId, (p, s) => new { sId = s.Id })
        .Join(context.Pis, ps => ps.sId, pi => pi.SiId, (ps, pi) => new {pi.Ch, pic.Wa, pic.Bu, pic.Hi})
        .GroupBy(
            pi => pi,
            (k, g) => new MostPisResult()
            {
                Ch = k.Ch,
                Wa = k.Wa,
                Bu = k.Bu,
                Hi = k.Hi,
                Count = g.Count()
            }
        )
        .OrderByDescending(x => x.Count);
        Console.WriteLine(query.ToString());
        return query.First();
    }
}

它输出以下SQL语句：

SELECT 
    [Project1].[C2] AS [C1], 
    [Project1].[Ch] AS [Ch], 
    [Project1].[Wa] AS [Wa], 
    [Project1].[Bu] AS [Bu], 
    [Project1].[Hi] AS [Hi], 
    [Project1].[C1] AS [C2]
    FROM ( SELECT 
        [GroupBy1].[A1] AS [C1], 
        [GroupBy1].[K1] AS [Ch], 
        [GroupBy1].[K2] AS [Wa], 
        [GroupBy1].[K3] AS [Bu], 
        [GroupBy1].[K4] AS [Hi], 
        1 AS [C2]
        FROM ( SELECT 
            [Extent3].[Ch] AS [K1], 
            [Extent3].[Wa] AS [K2], 
            [Extent3].[Bu] AS [K3], 
            [Extent3].[Hi] AS [K4], 
            COUNT(1) AS [A1]
            FROM   [dbo].[Product] AS [Extent1]
            INNER JOIN [dbo].[Si] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ProductId]
            INNER JOIN [dbo].[Pi] AS [Extent3] ON [Extent2].[Id] = [Extent3].[SiId]
            WHERE ([Extent1].[Code] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)
            GROUP BY [Extent3].[Ch], [Extent3].[Wa], [Extent3].[Bu], [Extent3].[Hi]
        )  AS [GroupBy1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC

我也尝试过使用查询语法，结果大致相同。我还使用EF直接尝试了(但不太长时间) 执行原始SQL查询，但无法很快让它正常工作。

我在将查询翻译到LINQ时是否犯了什么错误？有明显的改进查询的方法吗？是否可以使用与SQL语句相同的性能用EF / LINQ编写查询？

======更新======

在SQL分析器中，原始查询的输出完全相同。对于LINQ查询，它非常类似于我上面发布的内容。

exec sp_executesql N'SELECT TOP (1) 
    [Project1].[C2] AS [C1], 
    [Project1].[Ch] AS [Ch], 
    [Project1].[Wa] AS [Wa], 
    [Project1].[Bu] AS [Bu], 
    [Project1].[Hi] AS [Hi], 
    [Project1].[C1] AS [C2]
    FROM ( SELECT 
        [GroupBy1].[A1] AS [C1], 
        [GroupBy1].[K1] AS [Ch], 
        [GroupBy1].[K2] AS [Wa], 
        [GroupBy1].[K3] AS [Bu], 
        [GroupBy1].[K4] AS [Hi], 
        1 AS [C2]
        FROM ( SELECT 
            [Extent3].[Ch] AS [K1], 
            [Extent3].[Wa] AS [K2], 
            [Extent3].[Bu] AS [K3], 
            [Extent3].[Hi] AS [K4], 
            COUNT(1) AS [A1]
            FROM   [dbo].[Product] AS [Extent1]
            INNER JOIN [dbo].[Si] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ProductId]
            INNER JOIN [dbo].[Pi] AS [Extent3] ON [Extent2].[Id] = [Extent3].[SiId]
            WHERE ([Extent1].[Code] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)
            GROUP BY [Extent3].[Ch], [Extent3].[Wa], [Extent3].[Bu], [Extent3].[Hi]
        )  AS [GroupBy1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'109579'

======更新2 ======

下面是在XML输出上查询执行计划的模糊XML输出。注意，这里讨论的变量在输出中名为"MagicalCode“，两个值"109579”和"2449-268-550“都是有效的(C#中的字符串)，就像在XML的最后一行中一样。

<ParameterList>
    <ColumnReference
        Column="@p__linq__0"
        ParameterCompiledValue="N'109579'"
        ParameterRuntimeValue="N'2449-268-550'" />
</ParameterList>

显示实际行数的计划图像

​

​

======更新3 ======

(隐藏在注释中)我运行SSMS中的实体框架生成的SQL EF，它立即运行。因此，我可能受到了这个问题暗示的某种形式的参数嗅探的折磨。我不知道如何在实体框架中处理它。

======更新4 ======

更新的实体框架SQL执行计划和SQL查询执行计划，可以用计划探索者打开。

======更新5 ======

一些变通的尝试

• 使用context.Database.SqlQuery<ReturnObject>(...)运行原始查询的时间为4-5秒。
• 使用SqlCommand运行原始查询，从EF上下文获得的连接字符串大约需要3秒(上下文初始化开销)。
• 使用SqlCommand使用硬编码连接字符串运行原始查询大约需要1.5秒。所以我现在用了最后一个。我所能想到的最后一件事是编写一个存储过程，以接近在SSMS中运行查询的“即时”性能。

Answer 1

您可以尝试使用IQueryable.AsNoTracking()参见http://msdn.microsoft.com/en-us/library/gg679352(v=vs.103).aspx。在不编辑结果并将其保存回数据库的情况下，使用AsNoTracking()是安全的。通常，当查询返回大量行时，会产生很大的不同。如果要使用System.Data.Entity ()，请确保将.AsNoTracking放入您的使用中。

Answer 2

这可能是缓存执行计划的一个问题。尝试执行存储过程以清除查询执行计划：

DBCC FREEPROCCACHE

另外，这个线程可能会有帮助：实体框架缓存查询计划性能因参数不同而降低