提问人：napsebefya 提问时间：11/13/2023 最后编辑：napsebefya 更新时间：11/13/2023 访问量：101
为什么此 Microsoft SQL Server 查询中的排序成本为 94%？
Why does the sort in this Microsoft SQL server query have 94% cost?
问：
我有这个 Linq to SQL 查询，它很慢（返回 2 行需要 1000 秒）
var listReceptacle = await((STTEntities)_unitOfWork.Context).IRTReceptacles
.Include(r => r.Product)
.Include(r => r.IRTEvents)
.Where(r => r.IRTEvents.OrderByDescending(re => re.Timestamp)
.Take(1)
.Any(re => re.IRTEventTypeId == IRTEventType.InternalReceptacleCreated.Id
|| re.IRTEventTypeId == IRTEventType.InternalReceptacleReOpened.Id
|| re.IRTEventTypeId == IRTEventType.InternalReceptacleClosed.Id
|| re.IRTEventTypeId == IRTEventType.InternalReceptacleIsPendingDispatch.Id
)
)
.ToListAsync();
下表如下。
- 有一个插座
- 可以有一个或多个与之关联的事件（例如，容器创建、容器关闭、容器发送、交付等）
- 有一个事件类型表（事件类型少于 20 个）
- 添加一个插座可以与一个产品相关联。
这些表在这些列上有索引
容器
ID
Barcode
事件
ID
EventTypeID
ReceptacleID
Timestamp
事件类型
ID
Code - must be unique
粘贴箱中的实际执行计划：https://www.brentozar.com/pastetheplan/?id=H1dRIdkN6
SQL 查询的实际执行计划显示了一个具有 94% 成本的排序块。
这是实际执行计划的摘录（我已经将整个计划粘贴在底部）
将鼠标悬停在排序块上显示
我在事件表中添加了以下索引，但排序块仍有 94% 的成本。
- 列的复合索引 EventTypeId + Timestamp
- 列 Id + Timestamp 上的复合索引
- 包含列 EventTypeId 的时间戳索引
法典：
CREATE NONCLUSTERED INDEX [IX_IRTEvents_IRTEventTypeId_TimeStamp]
ON [dbo].[IRTEvents] ([IRTEventTypeId] ASC, [TimeStamp] ASC)
CREATE NONCLUSTERED INDEX [IX_IRTEvents_Id_TimeStamp]
ON [dbo].[IRTEvents] ([Id] ASC, [TimeStamp] ASC)
CREATE NONCLUSTERED INDEX [IX_IRTEvents_TimeStamp_2]
ON [dbo].[IRTEvents] ([TimeStamp] DESC)
INCLUDE (IRTEventTypeId)
这是查询（由 Linq 生成）
USE SwiftTrackAndTrace
DECLARE @p__linq__0 INT = 1
DECLARE @p__linq__1 INT = 3
DECLARE @p__linq__2 INT = 2
DECLARE @p__linq__3 INT = 6
SELECT
[Project3].[Id] AS [Id] ,
[Project3].[Barcode] AS [Barcode] ,
[Project3].[AllowedProductId] AS [AllowedProductId] ,
[Project3].[DestinationLocationId] AS [DestinationLocationId] ,
[Project3].[Id1] AS [Id1] ,
[Project3].[ProductGroupId] AS [ProductGroupId] ,
[Project3].[Code] AS [Code] ,
[Project3].[Description] AS [Description] ,
[Project3].[Rowversion] AS [Rowversion] ,
[Project3].[ValidateCheckDigit] AS [ValidateCheckDigit] ,
[Project3].[AllowCustom] AS [AllowCustom] ,
[Project3].[ImportInfo] AS [ImportInfo],
[Project3].[NewItem] AS [NewItem] ,
[Project3].[IsActive] AS [IsActive] ,
[Project3].[C1] AS [C1] ,
[Project3].[Id2] AS [Id2] ,
[Project3].[IRTReceptacleId] AS [IRTReceptacleId] ,
[Project3].[IRTEventTypeId] AS [IRTEventTypeId] ,
[Project3].[TIMESTAMP] AS [TIMESTAMP] ,
[Project3].[LocationId] AS [LocationId]
--[Project3].[UserId] AS [UserId]
FROM
(
SELECT
[Extent1].[Id] AS [Id] ,
[Extent1].[Barcode] AS [Barcode] ,
[Extent1].[AllowedProductId] AS [AllowedProductId] ,
[Extent1].[DestinationLocationId] AS [DestinationLocationId] ,
[Extent2].[Id] AS [Id1] ,
[Extent2].[ProductGroupId] AS [ProductGroupId] ,
[Extent2].[Code] AS [Code] ,
[Extent2].[Description] AS [Description] ,
[Extent2].[Rowversion] AS [Rowversion] ,
[Extent2].[ValidateCheckDigit] AS [ValidateCheckDigit] ,
[Extent2].[AllowCustom] AS [AllowCustom] ,
[Extent2].[ImportInfo] AS [ImportInfo],
[Extent2].[NewItem] AS [NewItem] ,
[Extent2].[IsActive] AS [IsActive] ,
[Extent3].[Id] AS [Id2] ,
[Extent3].[IRTReceptacleId] AS [IRTReceptacleId] ,
[Extent3].[IRTEventTypeId] AS [IRTEventTypeId] ,
[Extent3].[TIMESTAMP] AS [TIMESTAMP] ,
[Extent3].[LocationId] AS [LocationId] ,
--[Extent3].[UserId] AS [UserId]
CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS INT) ELSE 1 END AS [C1]
FROM
[dbo].[IRTReceptacles] AS [Extent1]
LEFT OUTER JOIN
[dbo].[Products] AS [Extent2]
ON
[Extent1].[AllowedProductId] = [Extent2].[Id]
LEFT OUTER JOIN
[dbo].[IRTEvents] AS [Extent3]
ON
[Extent1].[Id] = [Extent3].[IRTReceptacleId]
WHERE
EXISTS
(
SELECT
1 AS [C1]
FROM
(
SELECT
TOP (1) [Project1].[IRTEventTypeId] AS [IRTEventTypeId]
FROM
(
SELECT
[Extent4].[IRTEventTypeId] AS [IRTEventTypeId],
[Extent4].[TIMESTAMP] AS [TIMESTAMP]
FROM
[dbo].[IRTEvents] AS [Extent4]
WHERE
[Extent1].[Id] = [Extent4].[IRTReceptacleId] ) AS [Project1]
ORDER BY
[Project1].[TIMESTAMP] DESC ) AS [Limit1]
WHERE
[Limit1].[IRTEventTypeId] IN (@p__linq__0,
@p__linq__1,
@p__linq__2,
@p__linq__3) ) ) AS [Project3]
ORDER BY
[Project3].[Id] ASC ,
[Project3].[Id1] ASC,
[Project3].[C1] ASC
答：
0赞
Mohamad Mehdi Rajaei
11/26/2023
#1
你最好使用而不是那个复杂的条件：joinany
- 首先，从 IRTEvent 创建一个按 IRTReceptacleId 分组的可查询对象，然后选择 IRTReceptacleId 和 max（Timestamp）。
- 然后，可查询到 IRTReceptacle（在 IRTReceptacleId 上）和 IRTEvent（在 IRTReceptacleId 和 Timestamp 上）以检查 IRTEventTypeId 上的条件。
join - 使用 消除具有相同 IRTReceptacleId 和 Timestamp 的记录。
distinct
评论
LAST_VALUE([Extent3].[IRTEventTypeId]) OVER (PARTITION BY [Extent3].[IRTReceptacleId] ORDER BY [Extent3].[TIMESTAMP])