实体框架6 - 参数查询比内联参数查询慢11倍。为什么？ [英] Entity Framework 6 - Parameter query 11x slower than inline parameters query. Why?

问题描述

在我们的产品中分析一些查询，我发现使用Entity Framework 6参数会对这个查询的性能产生影响。这里有很多话题，不同意见。

在我的测试用例中，这两个查询是完全相同的，除了我已经将参数放在SQL中。

  DBCC DROPCLEANBUFFERS 
 DBCC FREEPROCCACHE 
 
 SET STATISTICS TIME ON 
 
 exec sp_executesql N'SELECT DISTINCT 
 [Extent1]。[POSTerminalID] AS [POSTerminalID] 
 FROM [dbo]。[POSDataEvents] AS [Extent1] 
 WHERE（[Extent1]。[DataTimeStamp]< = @ p__linq__0）AND（[ Extent1]。[DataTimeStamp]> = @ p__linq__1）AND（[Extent1]。[DataOwnerID] = @ p__linq__2）
'，N'@ p__linq__0 datetime2（7），@ p__linq__1 datetime2（7），@ p__linq__2 smallint '，@ p__linq__0 ='2017-06-22 16：16：01.3570000'，@ p__linq__1 ='2017-04-23 04:00:00'，@ p__linq__2 = 1 
 
 exec sp_executesql N' SELECT DISTINCT 
 [Extent1]。[POSTerminalID] AS [POSTerminalID] 
 FROM [dbo]。[POSDataEvents] AS [Extent1] 
 WHERE（[Extent1]。[DataTimeStamp] '2017-06-22 16:16:01''）AND（[Extent1]。[DataTimeSt amp]> =''2017-04-23 04:00:00''）AND（[Extent1]。[DataOwnerID] = 1）'
  

使用输出统计信息：

  SQL Server解析和编译时间：
 CPU时间= 0 ms，经过时间= 0 ms。 
 
（受影响的289行）
 
（受影响的1行）
 
 SQL Server执行时间：
 CPU时间= 11859毫秒，经过时间= 5827毫秒。 
 
 SQL Server执行时间：
 CPU时间= 11859 ms，已用时间= 5828 ms。 
 SQL Server解析和编译时间：
 CPU时间= 0 ms，已用时间= 0 ms。 
 
（受影响的289行）
 
（受影响的1行）
 
 SQL Server执行时间：
 CPU时间= 6221 ms，经过时间= 509 ms。 
 
 SQL Server执行时间：
 CPU时间= 6221 ms，经过时间= 509 ms。 
 SQL Server解析和编译时间：
 CPU时间= 0 ms，已用时间= 0 ms。 
 
 SQL Server执行时间：
 CPU时间= 0 ms，已用时间= 0 ms。 
  

生成的查询执行计划也略有不同，但仍然访问相同的表。

这带给我我的问题： / p>

1. 为什么使用参数表示的相同查询速度将会慢几十倍（约6秒至0.5秒）？相同的索引用于结果。




2. 如何在实体框架中强制内联参数？我已经看到另外一个用户在这个帖子几个月后问了这个问题，没有回应。我不知道这是否是正确的答案，但是想测试一下。我们不会在线编写原始SQL。它必须来自实体框架。

解决方案

问题是实体框架生成DateTime2类型的参数，而实际的数据库列被定义为DateTime。有两种解决方案：

将数据库列更改为DateTime2或者将Entity Framework改为使用DateTime（见这里）。

Profiling some queries in our product, I found that use of Entity Framework 6 parameters impact on performance with this one query. There are many topics on this, both with difference of opinions.

In my testing case, these two queries are identical, with the exception that I have placed the params inline the SQL.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS TIME ON 

exec sp_executesql N'SELECT DISTINCT 
    [Extent1].[POSTerminalID] AS [POSTerminalID]
    FROM [dbo].[POSDataEvents] AS [Extent1]
    WHERE ([Extent1].[DataTimeStamp] <= @p__linq__0) AND ([Extent1].[DataTimeStamp] >= @p__linq__1) AND ([Extent1].[DataOwnerID] = @p__linq__2)
',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 smallint',@p__linq__0='2017-06-22 16:16:01.3570000',@p__linq__1='2017-04-23 04:00:00',@p__linq__2=1

exec sp_executesql N'SELECT DISTINCT 
    [Extent1].[POSTerminalID] AS [POSTerminalID]
    FROM [dbo].[POSDataEvents] AS [Extent1]
    WHERE ([Extent1].[DataTimeStamp] <= ''2017-06-22 16:16:01'') AND ([Extent1].[DataTimeStamp] >= ''2017-04-23 04:00:00'') AND ([Extent1].[DataOwnerID] = 1)'

With the output stats:

 SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(289 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 11859 ms,  elapsed time = 5827 ms.

 SQL Server Execution Times:
   CPU time = 11859 ms,  elapsed time = 5828 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(289 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6221 ms,  elapsed time = 509 ms.

 SQL Server Execution Times:
   CPU time = 6221 ms,  elapsed time = 509 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The resulting query execution plan is also slightly different, but still accessing the same tables.

This brings me to my questions:

1. Why would the same query, just represented with parameters be nearly 11 times slower (~6 seconds to .5 seconds)? The same Index is used for the results.

2. How in entity Framework can I force inline parameters? I have seen another user ask on SO a few months back from this post with no response. I am not sure if that is even the right answer, but would like to test that. We wont be writing raw SQL inline. It has to come from Entity Framework.

解决方案

The problem is that Entity Framework generates parameters of type DateTime2 while the actual database columns are defined as DateTime. There are two solutions:

Either change your database columns to DateTime2 or tell Entity Framework to use DateTime instead (see here).

这篇关于实体框架6 - 参数查询比内联参数查询慢11倍。为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！