SQL查询使用实体框架运行速度较慢，使用错误的查询计划
我通常使用实体框架成功，但一个查询运行速度非常缓慢。 查询（由EF生成）如下所示：
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'
这是使用SQL Server Profiler捕获的。 当我使用SSMS查询窗口运行这个时，我在2秒内得到了8000多行。 通过设置统计数据，我发现它可以处理大约16,000个逻辑读取。
当我使用EF查看来自我网站的相同查询时，它在30秒后超时，完成了140多万次逻辑读取。
使用Profiler，我看到两个会话在登录时都有以下设置：
-- 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
两个查询都使用相同的登录名和密码完成。
我看到SSMS正在向服务器发送一些集合，所以我在查询之前将以下代码添加到我的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
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");
（为了便于阅读，添加了回车符）
我推测，在查询结束时作为参数传递的日期被解释为不同，迫使来自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 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();
问题是我的查询陈旧或不正确的查询计划。
我解决了删除此查询的现有查询计划的问题。
感谢弗拉基米尔巴拉诺夫指出我在sommarskog.se/query-plan-mysteries.html。 还要感谢tschmit007和annemartijn。
我必须使用以下查询在数据库中为我的查询确定查询计划：
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%'
这是来自sommarskog论文的查询的轻微修改版本。 请注意，您必须将自己的代码放入类似的语句中以查找您的查询。 此查询以我查询的每个查询计划的属性列表和计划句柄作为响应。
我试图找出哪些计划来自SSMS，哪些计划来自EF，因此我使用以下语法删除了所有计划：
dbcc freeproccache([your plan handle here])
为我的EF查询创建的新计划完美运作。 显然，EF计划没有考虑到我最近更新了数据库的统计数据。 不幸的是，我不知道如何为EF查询做一个sp_recompile。
链接地址: http://www.djcxy.com/p/85391.html上一篇: SQL Query Using Entity Framework Runs Slower, uses bad query plan