查询运行时间过长，如何优化

Question

查询结构：Helper select in"用"；子句-使用"top 1 transaction_date"选择最近的条目。然后是许多联接。跑步花了太多时间——我做错了什么？

CREATE VIEW [IRWSMCMaterialization].[FactInventoryItemOnHandDailyView] AS
WITH TempTBLFactIvnItmDaily AS (
SELECT TOP 20
ITEM_NUMBER AS [InventoryItemNumber]
,CAST(FORMAT(TRANSACTION_DATE, 'yyyyMMdd') AS INT) AS [DateKey]
,BRANCH_PLANT_FHK AS [BranchPlantKey]
,BRANCH_PLANT_CODE AS [BranchPlantCode]
,CAST(QUANTITY_ON_HAND AS BIGINT)  AS [QuantityOnHand]
,TRANSACTION_DATE AS [Date]
,WAREHOUSE_LOCATION_FHK AS [WarehouseLocationKey]
,WAREHOUSE_LOCATION_CODE AS [WarehouseLocationCode]
,WAREHOUSE_LOT_NUMBER_CODE  AS [WarehouseLotNumber]
,WAREHOUSE_LOT_NUMBER_FHK AS [WarehouseLotNumberKey]
,UNIT_OF_MEASURE AS [UnitOfMeasureName]
,UNIT_OF_MEASURE_PHK AS [UnitOfMeasureKey]

FROM dbo.RS_INV_ITEM_ON_HAND
-- below is where clause, choose only most recent entry
WHERE TRANSACTION_DATE = (SELECT TOP 1 TRANSACTION_DATE FROM dbo.RS_INV_ITEM_ON_HAND ORDER BY TRANSACTION_DATE DESC)
)
SELECT [InventoryItemNumber],
[DateKey],
[Date],
[BranchPlantCode] AS [BP],
[WarehouseLocationCode] AS [Location],
[QuantityOnHand],
[UnitOfMeasureName] AS [UoM],
CASE [WarehouseLotNumber]
WHEN 'Not Assigned' THEN NULL
ELSE [WarehouseLotNumber]
END
AS [Lot]
FROM TempTBLFactIvnItmDaily iioh
JOIN DWH.DimBranchPlant bp ON  iioh.BranchPlantKey = bp.BRANCH_PLANT_PHK
JOIN DWH.DimWarehouseLocation wloc ON iioh.WarehouseLocationKey = wloc.WAREHOUSE_LOCATION_PHK
JOIN DWH.DimWarehouseLotNumber wlot ON iioh.WarehouseLotNumberKey = wlot.WarehouseLotNumber_PHK
JOIN DWH.DimUnitOfMeasure uom ON CAST(iioh.UnitOfMeasureKey AS VARCHAR(100)) = uom.UNIT_OF_MEASURE_PHK
where bp.BRANCH_PLANT_CODE = '96100' 
AND iioh.QuantityOnHand > 0
AND (wloc.WAREHOUSE_LOCATION_CODE like '6000W01%' OR wloc.WAREHOUSE_LOCATION_CODE like 'BL%')
GO

Answer 1

有很多事情看起来并不好。首先，您的基本查询必须简单得多。类似这样的东西：

SELECT iioh.ITEM_NUMBER AS [InventoryItemNumber],
CAST(FORMAT(iioh.TRANSACTION_DATE, 'yyyyMMdd') AS INT) AS [DateKey],
iioh.TRANSACTION_DATE AS [Date],
iioh.BRANCH_PLANT_CODE AS [BP],
iioh.WAREHOUSE_LOCATION_CODE AS [Location],
CAST(iioh.QUANTITY_ON_HAND AS BIGINT) AS [QuantityOnHand],
iioh.UNIT_OF_MEASURE AS [UoM],
NULLIF(iioh.WAREHOUSE_LOT_NUMBER_CODE, 'Not Assigned') AS [Lot]        
FROM dbo.RS_INV_ITEM_ON_HAND iioh
JOIN DWH.DimBranchPlant bp 
ON  iioh.BranchPlantKey = bp.BRANCH_PLANT_PHK
JOIN DWH.DimWarehouseLocation wloc 
ON iioh.WarehouseLocationKey = wloc.WAREHOUSE_LOCATION_PHK
JOIN DWH.DimUnitOfMeasure uom 
ON CAST(iioh.UnitOfMeasureKey AS VARCHAR(100)) = uom.UNIT_OF_MEASURE_PHK
where bp.BRANCH_PLANT_CODE = '96100' 
AND iioh.QuantityOnHand > 0
AND (wloc.WAREHOUSE_LOCATION_CODE like '6000W01%' OR wloc.WAREHOUSE_LOCATION_CODE like 'BL%')
AND iioh.TRANSACTION_DATE = @TRANSACTION_DATE

例如，您正在加入DWH.DimWarehouseLotNumber，但没有提取列——您真的需要它吗？此外，还有其他列没有被视图返回——为什么要查询它们？

从中，您首先按date进行筛选，然后按其他字段进行筛选，因此您的前20条记录可能会按下一个条件进行筛选-这是您想要的行为吗？

另外，你真的想要这个演员阵容吗？

ON CAST(iioh.UnitOfMeasureKey AS VARCHAR(100)) = uom.UNIT_OF_MEASURE_PHK

在性能方面最好使用CONVERT，而不是FORMAT。此外，为什么不将TRANSACTION_DATE保存/具体化为INT(例如，使用持久化计算列或仅在CRUD上(，而不是在每次读取时计算此值？

使用LIKE子句的location code过滤也可以提高性能。为什么不添加一个新列WareHouseLocationCodeType，并为满足此条件的所有位置设置相同的值：

(wloc.WAREHOUSE_LOCATION_CODE like '6000W01%' OR wloc.WAREHOUSE_LOCATION_CODE like 'BL%')

然后您可以在视图中按此列进行筛选，因为这对您来说非常重要。此外，您还可以在此列上创建filter index以进一步提高性能。

此外，您可能希望创建一个内联函数，而不是一个视图，并将日期作为参数传递：

CREATE OR ALTER FUNCTION [IRWSMCMaterialization].[FactInventoryItemOnHandDailyView] 
(
@TRANSACTION_DATE datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT iioh.ITEM_NUMBER AS [InventoryItemNumber],
CAST(FORMAT(iioh.TRANSACTION_DATE, 'yyyyMMdd') AS INT) AS [DateKey],
iioh.TRANSACTION_DATE AS [Date],
iioh.BRANCH_PLANT_CODE AS [BP],
iioh.WAREHOUSE_LOCATION_CODE AS [Location],
CAST(iioh.QUANTITY_ON_HAND AS BIGINT) AS [QuantityOnHand],
iioh.UNIT_OF_MEASURE AS [UoM],
NULLIF(iioh.WAREHOUSE_LOT_NUMBER_CODE, 'Not Assigned') AS [Lot]  
,iioh.TRANSACTION_DATE 
FROM dbo.RS_INV_ITEM_ON_HAND iioh
JOIN DWH.DimBranchPlant bp 
ON  iioh.BranchPlantKey = bp.BRANCH_PLANT_PHK
JOIN DWH.DimWarehouseLocation wloc 
ON iioh.WarehouseLocationKey = wloc.WAREHOUSE_LOCATION_PHK
JOIN DWH.DimUnitOfMeasure uom 
ON CAST(iioh.UnitOfMeasureKey AS VARCHAR(100)) = uom.UNIT_OF_MEASURE_PHK
where bp.BRANCH_PLANT_CODE = '96100' 
AND iioh.QuantityOnHand > 0
AND (wloc.WAREHOUSE_LOCATION_CODE like '6000W01%' OR wloc.WAREHOUSE_LOCATION_CODE like 'BL%')
AND iioh.TRANSACTION_DATE = @TRANSACTION_DATE
)

然后这样称呼它：

SELECT TOP 20 *
FROM [IRWSMCMaterialization].[FactInventoryItemOnHandDailyView] ('2020-12-04')
ORDER BY @TRANSACTION_DATE DESC

Answer 2

查询优化是当今的科学。如果你想在查询中找到瓶颈，你可以遵循以下步骤：

作为第一步，使用以下命令启用统计：

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

在同一窗口中的某些查询窗口中执行这些命令后，执行查询。当您的查询被执行时，切换到Messages选项卡，您将看到许多有用的信息，如TIME执行、解析和编译时间，以及可能最有趣的I/O读取。

作为第二步，试着了解哪个表有很多读取，例如，如果您希望查询中有10行，但在某些表中，您有10k或100k的逻辑读取，则会出现错误。这意味着从一个表中执行10行查询可以读取10k页。很明显，您在这个表上缺少了一些索引，请尝试找到您需要的索引。

如果您在where子句中有一些静态值，如以下所示，那么请考虑Filtered Index:

bp.BRANCH_PLANT_CODE = '96100' AND iioh.QuantityOnHand > 0

并非总是如此，但在某些情况下，如果您对索引进行强制转换或在where子句中使用其他函数(如下面的子句(，则转换可能会破坏索引，即使您在该列上有索引，查询优化器也不会在查询执行中使用它：

CAST(iioh.UnitOfMeasureKey AS VARCHAR(100))

最后一个，如果您的查询中有OR逻辑运算符，请尝试逐个执行OR逻辑运算符的一部分，以查看性能。这个逻辑运算符真的可以杀死你的查询，这是一个例子：

AND (wloc.WAREHOUSE_LOCATION_CODE like '6000W01%' OR wloc.WAREHOUSE_LOCATION_CODE like 'BL%')

一旦你在这里确定你没有任何问题，你就可以走得更远。