SQL查询使用Entity Framework的运行速度较慢，用坏的查询计划 [英] SQL Query Using Entity Framework Runs Slower, uses bad query plan
本文介绍了SQL查询使用Entity Framework的运行速度较慢，用坏的查询计划的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用实体框架的总体成功，但一查询显着运行缓慢。 （由EF生成）查询如下：
EXEC sp_executesql的N'SELECT
[PROJECT1] [downtimeId ] AS [downtimeId]，
CASE WHEN（[Extent12] [downtimeStart> @ p__linq__7）。然后按[Extent13] [downtimeStart] ELSE @ p__linq__8 END AS [C1]，
CASE WHEN（[ Extent14]。[equipmentID] IS NULL）THEN ELSE 0 [Extent15]。[equipmentID] END AS [C2]，
CASE WHEN（[Extent16]。[equipmentID] IS NULL）THEN N''Unit架空'' 。ELSE [Extent18] [equipmentCode] END AS [C3]，
CASE WHEN（CAST（[PROJECT1] [downtimeEquipmentStart] AS DATETIME2）方式> @ p__linq__9）。然后抹上（[PROJECT1] [downtimeEquipmentStart] AS DATETIME2 ）ELSE @ p__linq__10 END AS [4]，
CASE WHEN（CAST（[PROJECT1] [downtimeEquipmentEnd] AS DATETIME2。）LT; @ p__linq__11）再投（[PROJECT1] [downtimeEquipmentEnd] AS DATETIME2）ELSE @ p__linq__12 END AS [C5]，
CASE WHEN（[Extent19]。[standardHourRate] IS NULL）再投（0为十进制（18））ELSE [Extent20]。[standardHourRate] END AS [6]，
CASE WHEN（[Extent21]。[equipmentID] IS NULL）THEN 0 ELSE [过滤器2]。[reportingSequence] END AS [C7]
FROM（SELECT
@ p__linq__0 AS [p__linq__0]，
〔Extent1]。[downtimeId] AS [downtimeId]，
[Extent1]。[equipmentID] AS [equipmentID]，
[Extent1]。[downtimeEquipmentStart] AS [downtimeEquipmentStart]，
[ Extent1]。[downtimeEquipmentEnd] AS [downtimeEquipmentEnd]
从[DBO]。[DowntimeEquipment] AS [Extent1]）AS [PROJECT1]
OUTER APPLY（SELECT [Extent2]。[reportingSequence] AS [reportingSequence]
FROM [DBO]。[ProcessUnitEquipment] AS [Extent2]
INNER JOIN [DBO]。[停机] AS [Extent3] ON [Extent3]。[equipmentID] = [Extent2]。[equipmentID]
LEFT OUTER JOIN（SELECT
[Extent4]。[downtimeId] AS [downtimeId]
从[DBO]。[停机] AS [Extent4]
WHERE [PROJECT1]。[downtimeId ] = [Extent4]。[downtimeId]）AS [Project2中] ON 1 = 1
WHERE（[PROJECT1]。[downtimeId] = [Extent3]。[downtimeId]）AND（[Extent2]。[processUnitID] = @ p__linq__0）AND（@ p__linq__0 IS NOT NULL））AS [过滤器2]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent5] ON [PROJECT1]。[downtimeId] = [Extent5]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent6] ON [PROJECT1]。[downtimeId] = [Extent6]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [ Extent7] ON [PROJECT1]。[downtimeId] = [Extent7]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent8] ON [PROJECT1]。[downtimeId] = [Extent8]。[ downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent9] ON [PROJECT1]。[downtimeId] = [Extent9]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent10] ON [PROJECT1]。[downtimeId] = [Extent10]。[downtimeId]
LEFT OUTER JOIN [DBO]。[DownTimeType] AS [Extent11] ON [Extent10]。[downTimeTypeId] = [Extent11] [downTimeTypeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent12] ON [PROJECT1]。[downtimeId] = [Extent12]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent13] ON [PROJECT1]。[downtimeId] = [Extent13]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent14] ON [PROJECT1]。[downtimeId] = [ Extent14]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent15] ON [PROJECT1]。[downtimeId] = [Extent15]。[downtimeId]
LEFT OUTER JOIN [DBO] [停机] AS [Extent16] ON [PROJECT1]。[downtimeId] = [Extent16]。[downtimeId]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent17] ON [PROJECT1]。[downtimeId] = [Extent17]。[downtimeId]
LEFT OUTER JOIN [DBO]。[装备] AS [Extent18] ON [Extent17]。[equipmentID] = [Extent18]。[equipmentID]
LEFT OUTER JOIN [ DBO]。[装备] AS [Extent19] ON [PROJECT1]。[equipmentID] = [Extent19]。[equipmentID]
LEFT OUTER JOIN [DBO]。[装备] AS [Extent20] ON [PROJECT1]。[ equipmentID] = [Extent20]。[equipmentID]
LEFT OUTER JOIN [DBO]。[停机] AS [Extent21] ON [PROJECT1]。[downtimeId] = [Extent21]。[downtimeId]
WHERE（ [Extent5] [downtimeEnd]≥= @ p__linq__1）与（[Extent6] [downtimeStart]所述。 @ p__linq__2）AND（[PROJECT1] [downtimeEquipmentStart< @ p__linq__3）AND（[PROJECT1] [downtimeEquipmentEnd> @ p__linq__4）AND（（（[Extent7] [processUnitID] = @ p__linq__5）AND（NOT（。 [Extent8]。[processUnitID]为空或@ p__linq__5 IS NULL）））OR（（[Extent9]。[processUnitID] IS NULL）AND（@ p__linq__5 IS NULL）））AND（@ p__linq__6 = 1或[Extent11。 [includeInDowntimeAnalysis] = 1）'，N@ p__linq__0整型，@ p__linq__1 DATETIME2（7），@ p__linq__2 DATETIME2（7），@ p__linq__3 DATETIME2（7），@ p__linq__4 DATETIME2（7），@ p__linq__5整型，@ p__linq__6位@ p__linq__7 DATETIME2（7），@ p__linq__8 DATETIME2（7），@ p__linq__9 DATETIME2（7），@ p__linq__10 DATETIME2（7），@ p__linq__11 DATETIME2（7），@ p__linq__12 DATETIME2（7）'，@ p__linq__0 = 1，@ p__linq__1 ='2015年3月2日00:00:00，@ p__linq__2 ='2015年5月9日00:00:00，@ p__linq__3 ='2015年5月9日00:00:00，@ p__linq__4 =' 2015年3月2日00:00:00，@ p__linq__5 = 1，@ p__linq__6 = 1，@ p__linq__7 ='2015年3月2日00:00:00，@ p__linq__8 ='2015年3月2日00:00 ：00，@ p__linq__9 ='2015年3月2日00:00:00，@ p__linq__10 ='2015年3月2日00:00:00，@ p__linq__11 ='2015年5月9日00:00:00 '，@ p__linq__12 ='2015年5月9日00:00:00'
这是使用抓获在SQL Server事件探查器。当我运行这个使用SSMS查询窗口，我得到了在2秒8000+行。随着的组统计IO ON 的，我看到它约16000逻辑读取。
当我看到相同的查询使用EF我的网站来，超时30秒后，已经做了超过140万个逻辑读
使用分析器，我看到两个会话登录时有以下设置：
- 网络协议：TCP /于
组IP
SET QUOTED_IDENTIFIER ARITHABORT关闭
组NUMERIC_ROUNDABORT关闭在
组
SET ANSI_WARNINGS上
组的
组CONCAT_NULL_YIELDS_NULL在
SET ANSI_NULLS ANSI_PADDING CURSOR_CLOSE_ON_COMMIT关闭
组IMPLICIT_TRANSACTIONS关闭
设置语言美国英语
设置日期格式MDY
组DATEFIRST 7
组的事务隔离级别读取承诺
这两个查询都使用相同的用户名和密码完成的。
我看到SSMS是发送一些的设置的s到服务器，所以我下面的代码添加到我的C＃应用EF我查询之前右：
_context.Database.ExecuteSqlCommand（
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET LOCK_TIMEOUT -1
设置QUERY_GOVERNOR_COST_LIMIT 0
设置DEADLOCK_PRIORITY师范大学
设置事务隔离级别READ COMMITTED
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
设置IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON）;
的（加回车的可读性）的
我推测，作为参数在查询年底通过的日期正在被不同的解释，从EF到来时强制隐式数据类型转换，但我不知道该怎么办才好[从后编辑注：这是不正确，而不是问题的根源的]
请不要告诉我，使它。一个存储过程
中的C＃代码如下：
_context.Database.ExecuteSqlCommand（
SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY师范大学SET事务隔离级别READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON）;
的DateTime SDATE =的startDate;
VAR downtimeQueryRaw =从德在_context.DowntimeEquipments
在sequenceQuery
加入P于de.Downtime.equipmentID在sequenceEquipments.DefaultIfEmpty等于p.equipmentID到sequenceEquipments
从序列（）
，其中de.Downtime.downtimeEnd> = SDATE和放大器;&安培;
de.Downtime.downtimeStart< workingEnd&功放;&安培;
de.downtimeEquipmentStart< workingEnd&功放;&安培;
de.downtimeEquipmentEnd> SDATE&功放;&安培;
de.Downtime.processUnitID == processUnitId&放大器;&安培;
（includeUncontrollable ||
de.Downtime.DownTimeType.includeInDowntimeAnalysis）
选择新DowntimeCostByEquipmentRaw
{
DowntimeStart =（（de.Downtime.downtimeStart> SDATE）
de.Downtime.downtimeStart
：SDATE），
EquipmentId = de.Downtime.equipmentID？ 0，
EquipmentCode =
（de.Downtime.equipmentID == NULL
股开销
：de.Downtime.Equipment.equipmentCode），
开始= （（（（DATETIME）de.downtimeEquipmentStart）GT; SDATE）
（（DATETIME）de.downtimeEquipmentStart）
：SDATE），
端=（（（（DATETIME）de.downtimeEquipmentEnd ）LT; workingEnd）
（（DATETIME）de.downtimeEquipmentEnd）
：workingEnd），
StandardHourRate = de.Equipment.standardHourRate？ 0，
ReportingSequence =（de.Downtime.equipmentID == NULL 0：sequence.reportingSequence？）
};
VAR downtimeList = downtimeQueryRaw.ToList（）;
解决方案
问题是过时或不正确的查询计划我查询。
我解决了删除此查询现有的查询计划的问题。
感谢弗拉基米尔·巴拉诺夫指点我在sommarskog.se/query-plan-mysteries.html。 。也感谢tschmit007和annemartijn
我不得不确定使用以下查询我的数据库查询的查询计划：
SELECT qs.plan_handle，a.attrlist，est.dbid，文字
FROM sys.dm_exec_query_stats动态管理QS
CROSS APPLY sys.dm_exec_sql_text（qs.sql_handle ）共青
CROSS APPLY（选择epa.attribute +=+转换（为nvarchar（127），epa.value）+''
从sys.dm_exec_plan_attributes（qs.plan_handle）环保局
WHERE epa.is_cache_key = 1
ORDER BY epa.attribute
FOR XML PATH（''））AS A（attrlist）
，其中est.text LIKE'％standardHourRate％'和est.text LIKE'％q__7％'和est.text LIKE'％单位开销％
和est.text NOT LIKE'％sys.dm_exec_plan_attributes％'
这是查询从sommarskog的纸轻轻修改后的版本。请注意，你必须把自己的代码中类似的语句来找到你的查询。该查询的属性列表，并为我的每个查询的查询计划的计划句柄响应。
我试图找出哪些计划从SSMS来了，从EF，所以我删除了所有的人，使用的语法如下：
DBCC FREEPROCCACHE（[你的计划处理这里]）
我的EF查询创建新的计划完美。显然，EF计划没有考虑到我已经在数据库上最近更新的统计数据。不幸的是，我不知道该怎么办了一个EF查询sp_recompile。
am using entity framework generally successfully, but one query is running remarkably slowly. The query (generated by EF) is as follows:
exec sp_executesql N'SELECT
[Project1].[downtimeId] AS [downtimeId],
CASE WHEN ([Extent12].[downtimeStart] > @p__linq__7) THEN [Extent13].[downtimeStart] ELSE @p__linq__8 END AS [C1],
CASE WHEN ([Extent14].[equipmentID] IS NULL) THEN 0 ELSE [Extent15].[equipmentID] END AS [C2],
CASE WHEN ([Extent16].[equipmentID] IS NULL) THEN N''Unit Overhead'' ELSE [Extent18].[equipmentCode] END AS [C3],
CASE WHEN ( CAST( [Project1].[downtimeEquipmentStart] AS datetime2) > @p__linq__9) THEN CAST( [Project1].[downtimeEquipmentStart] AS datetime2) ELSE @p__linq__10 END AS [C4],
CASE WHEN ( CAST( [Project1].[downtimeEquipmentEnd] AS datetime2) < @p__linq__11) THEN CAST( [Project1].[downtimeEquipmentEnd] AS datetime2) ELSE @p__linq__12 END AS [C5],
CASE WHEN ([Extent19].[standardHourRate] IS NULL) THEN cast(0 as decimal(18)) ELSE [Extent20].[standardHourRate] END AS [C6],
CASE WHEN ([Extent21].[equipmentID] IS NULL) THEN 0 ELSE [Filter2].[reportingSequence] END AS [C7]
FROM (SELECT
@p__linq__0 AS [p__linq__0],
[Extent1].[downtimeId] AS [downtimeId],
[Extent1].[equipmentID] AS [equipmentID],
[Extent1].[downtimeEquipmentStart] AS [downtimeEquipmentStart],
[Extent1].[downtimeEquipmentEnd] AS [downtimeEquipmentEnd]
FROM [dbo].[DowntimeEquipment] AS [Extent1] ) AS [Project1]
OUTER APPLY (SELECT [Extent2].[reportingSequence] AS [reportingSequence]
FROM [dbo].[ProcessUnitEquipment] AS [Extent2]
INNER JOIN [dbo].[Downtime] AS [Extent3] ON [Extent3].[equipmentID] = [Extent2].[equipmentID]
LEFT OUTER JOIN (SELECT
[Extent4].[downtimeId] AS [downtimeId]
FROM [dbo].[Downtime] AS [Extent4]
WHERE [Project1].[downtimeId] = [Extent4].[downtimeId] ) AS [Project2] ON 1 = 1
WHERE ([Project1].[downtimeId] = [Extent3].[downtimeId]) AND ([Extent2].[processUnitID] = @p__linq__0) AND (@p__linq__0 IS NOT NULL) ) AS [Filter2]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent5] ON [Project1].[downtimeId] = [Extent5].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent6] ON [Project1].[downtimeId] = [Extent6].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent7] ON [Project1].[downtimeId] = [Extent7].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent8] ON [Project1].[downtimeId] = [Extent8].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent9] ON [Project1].[downtimeId] = [Extent9].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent10] ON [Project1].[downtimeId] = [Extent10].[downtimeId]
LEFT OUTER JOIN [dbo].[DownTimeType] AS [Extent11] ON [Extent10].[downTimeTypeId] = [Extent11].[downTimeTypeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent12] ON [Project1].[downtimeId] = [Extent12].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent13] ON [Project1].[downtimeId] = [Extent13].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent14] ON [Project1].[downtimeId] = [Extent14].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent15] ON [Project1].[downtimeId] = [Extent15].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent16] ON [Project1].[downtimeId] = [Extent16].[downtimeId]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent17] ON [Project1].[downtimeId] = [Extent17].[downtimeId]
LEFT OUTER JOIN [dbo].[Equipment] AS [Extent18] ON [Extent17].[equipmentID] = [Extent18].[equipmentID]
LEFT OUTER JOIN [dbo].[Equipment] AS [Extent19] ON [Project1].[equipmentID] = [Extent19].[equipmentID]
LEFT OUTER JOIN [dbo].[Equipment] AS [Extent20] ON [Project1].[equipmentID] = [Extent20].[equipmentID]
LEFT OUTER JOIN [dbo].[Downtime] AS [Extent21] ON [Project1].[downtimeId] = [Extent21].[downtimeId]
WHERE ([Extent5].[downtimeEnd] >= @p__linq__1) AND ([Extent6].[downtimeStart] < @p__linq__2) AND ([Project1].[downtimeEquipmentStart] < @p__linq__3) AND ([Project1].[downtimeEquipmentEnd] > @p__linq__4) AND ((([Extent7].[processUnitID] = @p__linq__5) AND ( NOT ([Extent8].[processUnitID] IS NULL OR @p__linq__5 IS NULL))) OR (([Extent9].[processUnitID] IS NULL) AND (@p__linq__5 IS NULL))) AND (@p__linq__6 = 1 OR [Extent11].[includeInDowntimeAnalysis] = 1)',N'@p__linq__0 int,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 int,@p__linq__6 bit,@p__linq__7 datetime2(7),@p__linq__8 datetime2(7),@p__linq__9 datetime2(7),@p__linq__10 datetime2(7),@p__linq__11 datetime2(7),@p__linq__12 datetime2(7)',@p__linq__0=1,@p__linq__1='2015-03-02 00:00:00',@p__linq__2='2015-05-09 00:00:00',@p__linq__3='2015-05-09 00:00:00',@p__linq__4='2015-03-02 00:00:00',@p__linq__5=1,@p__linq__6=1,@p__linq__7='2015-03-02 00:00:00',@p__linq__8='2015-03-02 00:00:00',@p__linq__9='2015-03-02 00:00:00',@p__linq__10='2015-03-02 00:00:00',@p__linq__11='2015-05-09 00:00:00',@p__linq__12='2015-05-09 00:00:00'
This was captured using the SQL Server Profiler. When I run this using SSMS query window, I get 8000+ rows in under 2 seconds. With set statistics io on, I see that it does about 16,000 logical reads.
When I see the same query coming from my website using EF, it times out after 30 seconds, having done over 1.4 million logical reads.
Using the profiler, I saw that both sessions had the following settings during login:
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Both queries are done using the same login and password.
I saw that SSMS was sending some sets to the server, so I added the following code to my C# EF application right before my query:
_context.Database.ExecuteSqlCommand(
"SET ROWCOUNT 0
SET TEXTSIZE 2147483647
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET LOCK_TIMEOUT -1
SET QUERY_GOVERNOR_COST_LIMIT 0
SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON");
(carriage returns added for readability)
I hypothesize that the dates passed as arguments at the end of the query are being interpreted differently, forcing an implicit data type conversion when coming from the EF, but I don't know what to do about it.[Note from later edit: This is incorrect, and not the root of the problem]
Please don't tell me to make it a stored procedure.
The C# code is as follows:
_context.Database.ExecuteSqlCommand(
"SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON");
DateTime sdate = startDate;
var downtimeQueryRaw = from de in _context.DowntimeEquipments
join p in sequenceQuery
on de.Downtime.equipmentID equals p.equipmentID into sequenceEquipments
from sequence in sequenceEquipments.DefaultIfEmpty()
where de.Downtime.downtimeEnd >= sdate &&
de.Downtime.downtimeStart < workingEnd &&
de.downtimeEquipmentStart < workingEnd &&
de.downtimeEquipmentEnd > sdate &&
de.Downtime.processUnitID == processUnitId &&
(includeUncontrollable ||
de.Downtime.DownTimeType.includeInDowntimeAnalysis)
select new DowntimeCostByEquipmentRaw
{
DowntimeStart = ((de.Downtime.downtimeStart>sdate)
? de.Downtime.downtimeStart
: sdate),
EquipmentId = de.Downtime.equipmentID ?? 0,
EquipmentCode =
(de.Downtime.equipmentID == null
? "Unit Overhead"
: de.Downtime.Equipment.equipmentCode),
Start = ((((DateTime)de.downtimeEquipmentStart)>sdate)
?((DateTime)de.downtimeEquipmentStart)
: sdate),
End = ((((DateTime)de.downtimeEquipmentEnd) < workingEnd)
?((DateTime)de.downtimeEquipmentEnd)
: workingEnd),
StandardHourRate = de.Equipment.standardHourRate ?? 0,
ReportingSequence = (de.Downtime.equipmentID == null ? 0 : sequence.reportingSequence)
};
var downtimeList = downtimeQueryRaw.ToList();
解决方案
The problem was a stale or incorrect query plan for my query.
I solved the problem for deleting the existing query plans for this query.
Thanks to Vladimir Baranov for pointing me at sommarskog.se/query-plan-mysteries.html. Thanks also to tschmit007 and annemartijn.
I had to identify the query plans for my query in the database using the following query:
SELECT qs.plan_handle, a.attrlist, est.dbid, text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + ' '
FROM sys.dm_exec_plan_attributes(qs.plan_handle) epa
WHERE epa.is_cache_key = 1
ORDER BY epa.attribute
FOR XML PATH('')) AS a(attrlist)
WHERE est.text LIKE '%standardHourRate%' and est.text like '%q__7%'and est.text like '%Unit Overhead%'
AND est.text NOT LIKE '%sys.dm_exec_plan_attributes%'
This is a lightly modified version of the query from sommarskog's paper. Note that you have to put your own code in the like statements to find your query. This query responds with the attribute list and the plan handle for each query plan for my query.
I tried to figure out which plan came from SSMS and which from EF, so I deleted all of them, using the following syntax:
dbcc freeproccache([your plan handle here])
The new plan created for my EF query worked perfectly. Apparently, the EF plan did not take into consideration that I had updated statistics on the database recently. Unfortunately, I don't know how to do a sp_recompile for an EF query.
这篇关于SQL查询使用Entity Framework的运行速度较慢，用坏的查询计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
