实体框架查询性能的不同极值与原始SQL执行 [英] Entity Framework query performance differs extrem with raw SQL execution

问题描述

我有一个关于实体框架查询的执行性能问题。

I have a question about Entity Framework query execution performance.

模式

我有一个这样的表结构：

I have a table structure like this:

CREATE TABLE [dbo].[DataLogger]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProjectID] [bigint] NULL,
    CONSTRAINT [PrimaryKey1] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

CREATE TABLE [dbo].[DCDistributionBox]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DataLoggerID] [bigint] NOT NULL,
    CONSTRAINT [PrimaryKey2] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCDistributionBox]
    ADD CONSTRAINT [FK_DCDistributionBox_DataLogger] 
    FOREIGN KEY([DataLoggerID]) REFERENCES [dbo].[DataLogger] ([ID])

CREATE TABLE [dbo].[DCString] 
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] 
    FOREIGN KEY([DCDistributionBoxID]) REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData]
(
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

CREATE NONCLUSTERED INDEX [TimeStamp_DCCurrent-NonClusteredIndex] 
ON [dbo].[StringData] ([DCStringID] ASC, [TimeStamp] ASC)
INCLUDE ([DCCurrent])

这是外键标准指标也存在（我不想一一列举了空间的原因）。

Standard indexes on the foreign keys also exist (I don't want to list them all for space reasons).

[StringData是] 表作为具有以下存储数据：

The [StringData] table as has following storage stats:

• 数据空间：26,901.86 MB


• 行数：131827749


• 分区：真


• 分区数：62

• Data space: 26,901.86 MB
• Row count: 131,827,749
• Partitioned: true
• Partition count: 62

用法

我现在想组中的 [StringData是] 表中的数据，并做一些聚集。

I now want to group the data in the [StringData] table and do some aggregation.

我创建了一个实体框架查询（详细的相关信息到查询，可以发现这里）：

I created an Entity Framework query (detailed infos to the query can be found here):

var compareData = model.StringDatas
    .AsNoTracking()
    .Where(p => p.DCString.DCDistributionBox.DataLogger.ProjectID == projectID && p.TimeStamp >= fromDate && p.TimeStamp < tillDate)
    .Select(d => new
    {
        TimeStamp = d.TimeStamp,
        DCCurrentMpp = d.DCCurrent / d.DCString.CurrentMPP
    })
    .GroupBy(d => DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, d.TimeStamp) / minuteInterval * minuteInterval))
    .Select(d => new
    {
        TimeStamp = d.Key,
        DCCurrentMppMin = d.Min(v => v.DCCurrentMpp),
        DCCurrentMppMax = d.Max(v => v.DCCurrentMpp),
        DCCurrentMppAvg = d.Average(v => v.DCCurrentMpp),
        DCCurrentMppStDev = DbFunctions.StandardDeviationP(d.Select(v => v.DCCurrentMpp))
    })
    .ToList();

的

excecution时间跨度是特别长！？

The excecution timespan is exceptional long!?

• 执行结果：92rows


• 执行时间：〜16000ms

尝试

我现在考虑到了实体框架生成的SQL查询，看起来像这样看：

I now took a look into the Entity Framework generated SQL query and looks like this:

DECLARE @p__linq__4 DATETIME = 0;
DECLARE @p__linq__3 DATETIME = 0;
DECLARE @p__linq__5 INT = 15;
DECLARE @p__linq__6 INT = 15;
DECLARE @p__linq__0 BIGINT = 20827;
DECLARE @p__linq__1 DATETIME = '06.02.2016 00:00:00';
DECLARE @p__linq__2 DATETIME = '07.02.2016 00:00:00';

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [C2], 
[GroupBy1].[A1] AS [C3], 
[GroupBy1].[A2] AS [C4], 
[GroupBy1].[A3] AS [C5], 
[GroupBy1].[A4] AS [C6]
FROM ( SELECT 
    [Project1].[K1] AS [K1], 
    MIN([Project1].[A1]) AS [A1], 
    MAX([Project1].[A2]) AS [A2], 
    AVG([Project1].[A3]) AS [A3], 
    STDEVP([Project1].[A4]) AS [A4]
    FROM ( SELECT 
        DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [K1], 
        [Project1].[C1] AS [A1], 
        [Project1].[C1] AS [A2], 
        [Project1].[C1] AS [A3], 
        [Project1].[C1] AS [A4]
        FROM ( SELECT 
            [Extent1].[TimeStamp] AS [TimeStamp], 
            [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1]
            FROM    [dbo].[StringData] AS [Extent1]
            INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
            INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
            INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
            WHERE (([Extent4].[ProjectID] = @p__linq__0) OR (([Extent4].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2)
        )  AS [Project1]
    )  AS [Project1]
    GROUP BY [K1]
)  AS [GroupBy1]

我复制这个SQL查询到SSMS在同一台机器上，用相同的连接字符串的实体框架连接。

I copied this SQL query into SSMS on the same machine, connected with same connection string as the Entity Framework.

其结果是一个非常大的改善的性能：

The result is a very much improved performance:

• 执行结果：92rows


• 执行时间：517ms

我也做一些循环乳宁测试，结果很奇怪。该测试看起来像这样

I also do some loop runing test and the result is strange. The test looks like this

for (int i = 0; i < 50; i++)
{
    DateTime begin = DateTime.UtcNow;

    [...query...]

    TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin;
    Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToString());
}

结果是非常不同的，看起来是随机的（？）：

The result is very different and looks random(?):

0th run: 00:00:11.0618580
1th run: 00:00:11.3339467
2th run: 00:00:10.0000676
3th run: 00:00:10.1508140
4th run: 00:00:09.2041939
5th run: 00:00:07.6710321
6th run: 00:00:10.3386312
7th run: 00:00:17.3422765
8th run: 00:00:13.8620557
9th run: 00:00:14.9041528
10th run: 00:00:12.7772906
11th run: 00:00:17.0170235
12th run: 00:00:14.7773750

问

为什么实体框架查询执行的这么慢？所得到的行数实在是低，原始SQL查询显示了一个非常快速的性能。

Why is Entity Framework query execution so slow? The resulting row count is really low and the raw SQL query shows a very fast performance.

更新1

我要小心，它不是一个MetaContext或建立模型的延迟。一些其他的查询在同一个模型实例的权利之前，具有良好的性能执行

I take care that its not a MetaContext or Model creation delay. Some other queries are executed on the same Model instance right before with good performance.

更新2 （相关@ x0007me的答案）：

Update 2 (related to the answer of @x0007me):

谢谢你的提示，但这可以通过改变这样的模式设置来消除：

Thanks for the hint but this can be eliminated by changing the model settings like this:

modelContext.Configuration.UseDatabaseNullSemantics = true;

的

EF生成的SQL现在是：

The EF generated SQL is now:

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [C2], 
[GroupBy1].[A1] AS [C3], 
[GroupBy1].[A2] AS [C4], 
[GroupBy1].[A3] AS [C5], 
[GroupBy1].[A4] AS [C6]
FROM ( SELECT 
    [Project1].[K1] AS [K1], 
    MIN([Project1].[A1]) AS [A1], 
    MAX([Project1].[A2]) AS [A2], 
    AVG([Project1].[A3]) AS [A3], 
    STDEVP([Project1].[A4]) AS [A4]
    FROM ( SELECT 
        DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [K1], 
        [Project1].[C1] AS [A1], 
        [Project1].[C1] AS [A2], 
        [Project1].[C1] AS [A3], 
        [Project1].[C1] AS [A4]
        FROM ( SELECT 
            [Extent1].[TimeStamp] AS [TimeStamp], 
            [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1]
            FROM    [dbo].[StringData] AS [Extent1]
            INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
            INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
            INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
            WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2)
        )  AS [Project1]
    )  AS [Project1]
    GROUP BY [K1]
)  AS [GroupBy1]

所以，你可以看到你所描述的问题现在已经解决了，但执行时间不会改变。

So you can see the problem you described is now solved, but the execution time does not change.

此外，你可以在架构和原始的执行时间看，我用优化的高优化的索引结构。

Also, as you can see in the schema and the raw execution time, I used optimized structure with high optimized indexer.

更新3 （与@Vladimir巴拉诺夫的答案）：

Update 3 (related to the answer of @Vladimir Baranov):

我不明白为什么这会查询计划高速缓存有关。因为在MSDN显然descripted的EF6利用查询计划缓存。

I don't see why this can be related to query plan caching. Because in the MSDN is clearly descripted that the EF6 make use of query plan caching.

一个简单的测试证明，巨大的excecution时间differenz是不相关的查询计划缓存（phseudo代码）：

A simple test proof that the huge excecution time differenz is not related to the query plan caching (phseudo code):

using(var modelContext = new ModelContext())
{
    modelContext.Query(); //1th run activates caching

    modelContext.Query(); //2th used cached plan
}

结果，这两个查询使用相同的运行。excecution时间

As the result, both queries run with the same excecution time.

更新4 （与@bubi的答案）：

Update 4 (related to the answer of @bubi):

我试图运行一个由EF你descripted它生成的查询：

I tried to run the query that is generated by the EF as you descripted it:

int result = model.Database.ExecuteSqlCommand(@"SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [C2], 
    [GroupBy1].[A1] AS [C3], 
    [GroupBy1].[A2] AS [C4], 
    [GroupBy1].[A3] AS [C5], 
    [GroupBy1].[A4] AS [C6]
    FROM ( SELECT 
        [Project1].[K1] AS [K1], 
        MIN([Project1].[A1]) AS [A1], 
        MAX([Project1].[A2]) AS [A2], 
        AVG([Project1].[A3]) AS [A3], 
        STDEVP([Project1].[A4]) AS [A4]
        FROM ( SELECT 
            DATEADD (minute, ((DATEDIFF (minute, 0, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, 0) AS [K1], 
            [Project1].[C1] AS [A1], 
            [Project1].[C1] AS [A2], 
            [Project1].[C1] AS [A3], 
            [Project1].[C1] AS [A4]
            FROM ( SELECT 
                [Extent1].[TimeStamp] AS [TimeStamp], 
                [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1]
                FROM    [dbo].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
                INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
                WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2)
            )  AS [Project1]
        )  AS [Project1]
        GROUP BY [K1]
    )  AS [GroupBy1]",
    new SqlParameter("p__linq__0", 20827),
    new SqlParameter("p__linq__1", fromDate),
    new SqlParameter("p__linq__2", tillDate),
    new SqlParameter("p__linq__5", 15),
    new SqlParameter("p__linq__6", 15));

• 执行结果：92


• 执行时间：〜16000ms



据确切了，只要正常EF查询

It took exact as long as the normal EF query!?

更新5 （与@vittore的答案）：！？

Update 5 (related to the answer of @vittore):

我创建了一个追踪调用树，也许它可以帮助：

I create a traced call tree, maybe it helps:

的

更新6 （与@usr的答案）：

Update 6 (related to the answer of @usr):

我通过SQL Server设置创建了两个显示计划XML

I created two showplan XML via SQL Server Profiler.

跑得快（SSMS）.SQLPlan

运行缓慢（EF）.SQLPlan

更新7 （与@VladimirBaranov的评论）：

Update 7 (related to the comments of @VladimirBaranov):

我现在运行相关的一些更多的测试情况下，你注释。

I now run some more test case related to your comments.

首先，我eleminate时间通过使用新的计算列和匹配INDEXER采取订单操作。这减少与演出]滞后DATEADD（分，DATEDIFF（分，0，[时间戳]）/ 15 * 15，0）。详细的如何以及为什么你可以找到 href=\"http://stackoverflow.com/questions/35436442\">。

First I eleminate time taking order operations by using a new computed column and a matching INDEXER. This reduce the perfomance lag related to DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15* 15, 0). Detail for how and why you can find here.

结果看就像这样：

纯的EntityFramework查询：

Pure EntityFramework query:

for (int i = 0; i < 3; i++)
{
    DateTime begin = DateTime.UtcNow;
    var result = model.StringDatas
        .AsNoTracking()
        .Where(p => p.DCString.DCDistributionBox.DataLogger.ProjectID == projectID && p.TimeStamp15Minutes >= fromDate && p.TimeStamp15Minutes < tillDate)
        .Select(d => new
        {
            TimeStamp = d.TimeStamp15Minutes,
            DCCurrentMpp = d.DCCurrent / d.DCString.CurrentMPP
        })
        .GroupBy(d => d.TimeStamp)
        .Select(d => new
        {
            TimeStamp = d.Key,
            DCCurrentMppMin = d.Min(v => v.DCCurrentMpp),
            DCCurrentMppMax = d.Max(v => v.DCCurrentMpp),
            DCCurrentMppAvg = d.Average(v => v.DCCurrentMpp),
            DCCurrentMppStDev = DbFunctions.StandardDeviationP(d.Select(v => v.DCCurrentMpp))
        })
        .ToList();

        TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin;
        Debug.WriteLine("{0}th run pure EF: {1}", i, excecutionTimeSpan.ToString());
}





0号运行纯EF 00：00：12.6460624

1号运行纯EF 00：00：11.0258393

二路运行纯EF 00：00：08.4171044

我现在使用的EF生成的SQL一个SQL查询语句：

I now used the EF generated SQL as a SQL query:

for (int i = 0; i < 3; i++)
{
    DateTime begin = DateTime.UtcNow;
    int result = model.Database.ExecuteSqlCommand(@"SELECT 
        1 AS [C1], 
        [GroupBy1].[K1] AS [TimeStamp15Minutes], 
        [GroupBy1].[A1] AS [C2], 
        [GroupBy1].[A2] AS [C3], 
        [GroupBy1].[A3] AS [C4], 
        [GroupBy1].[A4] AS [C5]
        FROM ( SELECT 
            [Project1].[TimeStamp15Minutes] AS [K1], 
            MIN([Project1].[C1]) AS [A1], 
            MAX([Project1].[C1]) AS [A2], 
            AVG([Project1].[C1]) AS [A3], 
            STDEVP([Project1].[C1]) AS [A4]
            FROM ( SELECT 
                [Extent1].[TimeStamp15Minutes] AS [TimeStamp15Minutes], 
                [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1]
                FROM    [dbo].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
                INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
                WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp15Minutes] >= @p__linq__1) AND ([Extent1].[TimeStamp15Minutes] < @p__linq__2)
            )  AS [Project1]
            GROUP BY [Project1].[TimeStamp15Minutes]
        )  AS [GroupBy1];",
    new SqlParameter("p__linq__0", 20827),
    new SqlParameter("p__linq__1", fromDate),
    new SqlParameter("p__linq__2", tillDate));

    TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin;
    Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToString());
}

0号运行： 00：00：00.8381200

1号运行： 00:00： 00.6920736

帖后运行： 00：00：00.7081006

和 OPTION（RECOMPILE）：

for (int i = 0; i < 3; i++)
{
    DateTime begin = DateTime.UtcNow;
    int result = model.Database.ExecuteSqlCommand(@"SELECT 
        1 AS [C1], 
        [GroupBy1].[K1] AS [TimeStamp15Minutes], 
        [GroupBy1].[A1] AS [C2], 
        [GroupBy1].[A2] AS [C3], 
        [GroupBy1].[A3] AS [C4], 
        [GroupBy1].[A4] AS [C5]
        FROM ( SELECT 
            [Project1].[TimeStamp15Minutes] AS [K1], 
            MIN([Project1].[C1]) AS [A1], 
            MAX([Project1].[C1]) AS [A2], 
            AVG([Project1].[C1]) AS [A3], 
            STDEVP([Project1].[C1]) AS [A4]
            FROM ( SELECT 
                [Extent1].[TimeStamp15Minutes] AS [TimeStamp15Minutes], 
                [Extent1].[DCCurrent] / [Extent2].[CurrentMPP] AS [C1]
                FROM    [dbo].[StringData] AS [Extent1]
                INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
                INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
                INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
                WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp15Minutes] >= @p__linq__1) AND ([Extent1].[TimeStamp15Minutes] < @p__linq__2)
            )  AS [Project1]
            GROUP BY [Project1].[TimeStamp15Minutes]
        )  AS [GroupBy1]
        OPTION(RECOMPILE);",
    new SqlParameter("p__linq__0", 20827),
    new SqlParameter("p__linq__1", fromDate),
    new SqlParameter("p__linq__2", tillDate));

    TimeSpan excecutionTimeSpan = DateTime.UtcNow - begin;
    Debug.WriteLine("{0}th run: {1}", i, excecutionTimeSpan.ToString());
}

与RECOMPILE第0运行： 00：00：00.8260932

0th run with RECOMPILE: 00:00:00.8260932

与RECOMPILE 1号运行： 00：00：00.9139730

1th run with RECOMPILE: 00:00:00.9139730

与RECOMPILE帖后运行： 00：00：01.0680665

2th run with RECOMPILE: 00:00:01.0680665

在SSMS excecuted同一个SQL查询（不重新编译）：

Same SQL query excecuted in SSMS (without RECOMPILE):

00：00：01.105

同一个SQL查询中SSMS（与重新编译）excecuted：

Same SQL query excecuted in SSMS (with RECOMPILE):

00:00： 00.902

我希望这是你所需要的所有值。

I hope this are all values you needed.

推荐答案

在该答案我着重于原始观测：由EF生成查询是缓慢的，但是当相同的查询中SSMS运行它是快速

In this answer I'm focusing on the original observation: the query generated by EF is slow, but when the same query is run in SSMS it is fast.

此行为的一个可能的解释是的参数嗅探。

One possible explanation of this behaviour is Parameter sniffing.

SQL Server使用一个名为参数时，则执行$嗅探程序b $ b存储有参数的过程。当
过程被编译或重新编译，通入
参数的值进行评估，并用于创建执行计划。那么
值存储在计划缓存的执行计划。在
后续执行，同样的价值 - 同样的计划 - 用

SQL Server uses a process called parameter sniffing when it executes stored procedures that have parameters. When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan. That value is then stored with the execution plan in the plan cache. On subsequent executions, that same value – and same plan – is used.

所以，EF生成具有查询一些参数。当您第一次运行此查询，服务器会使用的，共有在第一次运行效果参数值这个查询的执行计划。该计划通常是相当不错的。但是，以后你使用其他值参数运行相同的EF查询。这可能是对的参数的新值的先前生成的计划不是最佳的，该查询会变慢。该服务器将使用以前的计划，因为它仍然是相同的查询，只是参数值是不同的。

So, EF generates a query that has few parameters. The first time you run this query the server creates an execution plan for this query using values of parameters that were in effect in the first run. That plan is usually pretty good. But, later on you run the same EF query using other values for parameters. It is possible that for new values of parameters the previously generated plan is not optimal and the query becomes slow. The server keeps using the previous plan, because it is still the same query, just values of parameters are different.

如果在这一刻你把查询文本，并尝试运行它直接在SSMS服务器将创建一个新的执行计划，因为在技术上它不是由EF应用程序发出相同的查询。即使是一个字符的区别是不够的，在会话设置的任何变化也足以让服务器来处理查询作为一个新的。其结果是服务器有两个计划在其缓存看似相同的查询。第一个慢计划是参数的新值慢，因为它最初是专为不同的参数值。第二个快的计划，是为当前参数值，所以它是快速

If at this moment you take the query text and try to run it directly in SSMS the server will create a new execution plan, because technically it is not the same query that is issued by EF application. Even one character difference is enough, any change in the session settings is also enough for the server to treat the query as a new one. As a result the server has two plans for the seemingly same query in its cache. The first "slow" plan is slow for the new values of parameters, because it was originally built for different parameter values. The second "fast" plan is built for the current parameter values, so it is fast.

的制品的慢的应用程序，在快速通过SSMS厄兰Sommarskog 的解释这个问题和其他相关领域的许多其他细节内容。

The article Slow in the Application, Fast in SSMS by Erland Sommarskog explains this and other related areas in much more details.

有几种方式来丢弃缓存计划和强制服务器再生它们。更改表或改变表索引应该做的 - 它应该丢弃与此相关的表中的所有计划，包括慢与快。然后运行在随着参数的新值EF申请查询，并得到一个新的快的计划。您运行SSMS查询，并获得与参数的新值第二个快的计划。服务器仍然会产生两个计划，但两者的计划现在已经快。

There are several ways to discard cached plans and force the server to regenerate them. Changing the table or changing the table indexes should do it - it should discard all plans that are related to this table, both "slow" and "fast". Then you run the query in EF application with new values of parameters and get a new "fast" plan. You run the query in SSMS and get a second "fast" plan with new values of parameters. The server still generates two plans, but both plans are fast now.

另一个变体加入 OPTION（RECOMPILE）到查询中。有了这个选项，服务器将不存储在其缓存中生成的计划。这样，每一次查询运行服务器将使用实际的参数值来生成（其认为）将是最适合于给定的参数值的方案。不足之处是该计划产生的额外开销。

Another variant is adding OPTION(RECOMPILE) to the query. With this option the server would not store the generated plan in its cache. So, every time the query runs the server would use actual parameter values to generate the plan that (it thinks) would be optimal for the given parameter values. The downside is an added overhead of the plan generation.

你要知道，服务器仍然可以选择使用此选项坏的计划，由于过时的统计，例如。但是，至少，参数嗅探不会成为一个问题。

Mind you, the server still could choose a "bad" plan with this option due to outdated statistics, for example. But, at least, parameter sniffing would not be a problem.

这篇关于实体框架查询性能的不同极值与原始SQL执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！