优化使用多个 CTE 的复杂视图

Question

我正在寻找一些关于我在这个视图的性能方面所犯的任何明显错误的指导，因为目前它需要超过 1m 46 秒来查询视图，如果我在期间和年份中添加 WHERE 子句，则需要超过 3m。

该视图的目的是整理数周的财务信息，并按期间和年份对这些信息进行分组。然后，在整个财务系统中引用此视图，以报告订单并将其生成到其他财务系统中。

查询太长，我无法在此处发布，因此我提供了查询的链接，并且可以根据要求提供有关表的任何信息： https://pastebin.com/x8dV8KLJ

下面只是查询的常用表表达式，因为 StackOverflow 坚持要求我发布一些代码来伴随链接：

ALTER VIEW [dbo].[UnitFinancialFigures] AS
WITH
ctePeriodWeekCount AS (
SELECT Count(Week.WeekNo) AS WeekCount, PrdNo AS Period, PrdYear AS Year
FROM Week
INNER JOIN Period on Week.WeekEndDate BETWEEN Period.StartDate AND Period.EndDate
GROUP BY PrdNo, PrdYear),
cteLabourFigures AS (
SELECT Labour.LabUnitId, Labour.LabPeriod, Labour.LabYear,
SUM(CASE WHEN LabourTypes.LTypeIsPayrollLabour = 1 THEN LabourBreakdown.LBrkAmount ELSE 0 END) AS PayRollLabour,
SUM(CASE WHEN LabourTypes.LTypeIsPayrollLabour = 0 THEN LabourBreakdown.LBrkAmount ELSE 0 END) AS OtherLabour
FROM Labour
INNER JOIN LabourBreakdown on LabourBreakDown.LBrkLabId = Labour.LabId
INNER JOIN LabourTypes on LabourTypes.LTypeId = LabourBreakdown.LBrkLTypeId
GROUP BY Labour.LabUnitId, Labour.LabPeriod, Labour.LabYear),
cteMobileRelief AS (
SELECT McDtUnitId, McHdPeriod, McHdYear, SUM(McDtAmount) AS MobileReliefTotal
FROM MobileCostHeader
INNER JOIN MobileCostDetail on MobileCostDetail.McDtHdId = MobileCostHeader.MCHdId
GROUP BY McDtUnitId, McHdPeriod, McHdYear),
cteInvoices AS (
SELECT Period.PrdNo, Period.PrdYear, Invoice.AccountID, SUM(Invoice.InvTotalInclVat) AS TotalInvoices,
SUM(Invoice.InvVatAmount) AS TotalInvoicesVat
FROM [SOP].Invoice
INNER JOIN Period on Invoice.InvoiceDate BETWEEN Period.StartDate AND Period.EndDate
GROUP By Period.PrdNo, Period.PrdYear, Invoice.AccountID),
cteSundryCosts AS (
SELECT ScDtAccountId, ScDtPeriodNo, ScDtYearNo, SUM(ScdtTotal) AS SundryTotal,
SUM(ScdtAmount) AS SundryNetTotal,
SUM(ScdtVatAmount) AS SundryVatAmount
FROM SundryCostDetail
WHERE ScdtRepeat = 0
GROUP BY ScDtAccountId, ScDtPeriodNo, ScDtYearNo),
cteAdditionalProfit AS (
SELECT ScDtAccountId, ScDtPeriodNo, ScDtYearNo, SUM(ScdtTotal) AS AdditionalProfitTotal,
SUM(ScdtAmount) AS AdditionalProfitNetTotal,
SUM(ScdtVatAmount) AS AdditionalProfitVatAmount
FROM SundryCostDetail
WHERE ScdtRepeat = 0 AND SCdtExcludeFromGP = 0
GROUP BY ScDtAccountId, ScDtPeriodNo, ScDtYearNo),
cteCashReceived AS (
SELECT CtUnitId, CtPeriod, CtYear,
SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total,
SUM(VATFree.CanaAmount - VATFree.CanaAgentVatAmount) AS VATFreeGoodsAmount,
SUM(Catering.CanaAmount - Catering.CanaAgentVatAmount) AS CateringGoodsAmount,
SUM(Vending.CanaAmount - Vending.CanaAgentVatAmount) AS VendingGoodsAmount,
SUM(CansConf.CanaAmount - CansConf.CanaAgentVatAmount) AS CansConfGoodsAmount
FROM CashTran
LEFT JOIN CashAnalysis AS VATFree ON CashTran.CtId = VATFree.CanaTranId AND VATFree.CanaCode = 'G201'
LEFT JOIN CashAnalysis AS Catering ON CashTran.CtId = Catering.CanaTranId AND Catering.CanaCode = 'G205'
LEFT JOIN CashAnalysis AS Vending ON CashTran.CtId = Vending.CanaTranId AND Vending.CanaCode = 'G202'
LEFT JOIN CashAnalysis AS CansConf ON CashTran.CtId = CansConf.CanaTranId AND CansConf.CanaCode = 'G203'
WHERE CtCategoryId = 3 AND CtApproved = 1 ANd CtTranType = 'R'
GROUP BY CtUnitId, CtPeriod, CtYear),
cteFreeIssues AS (
SELECT CtUnitId, CtPeriod, CtYear,
SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total,
SUM(Catering.CanaAmount - Catering.CanaAgentVatAmount) AS CateringGoodsAmount,
SUM(Vending.CanaAmount - Vending.CanaAgentVatAmount) AS VendingGoodsAmount,
SUM(CansConf.CanaAmount - CansConf.CanaAgentVatAmount) AS CansConfGoodsAmount,
SUM(Labour.CanaAmount - Labour.CanaAgentVatAmount) AS LabourGoodsAmount
FROM CashTran
INNER JOIN [SOP].Accounts ON Accounts.AcUniqueId = CtUnitId
LEFT JOIN CashAnalysis AS Catering ON CashTran.CtId = Catering.CanaTranId AND Catering.CanaCode = 'G204'
LEFT JOIN CashAnalysis AS Vending ON CashTran.CtId = Vending.CanaTranId AND Vending.CanaCode = 'G202'
LEFT JOIN CashAnalysis AS CansConf ON CashTran.CtId = CansConf.CanaTranId AND CansConf.CanaCode = 'G203'
LEFT JOIN CashAnalysis AS Labour ON CashTran.CtId = Labour.CanaTranId AND Labour.CanaCode = 'H995'
WHERE CtCategoryId = 2
GROUP BY CtUnitId, CtPeriod, CtYear),
cteExternalFreeIssues AS (
SELECT CtUnitId, CtPeriod, CtYear, SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total
FROM CashTran
INNER JOIN [SOP].Accounts on Accounts.AcUniqueId = CtUnitId
WHERE CtCategoryId = 4
GROUP BY CtUnitId, CtPeriod, CtYear),
cteAgencyLabour AS (
SELECT PtUnitId, PtYear, PtPeriod, SUM(CASE WHEN PtTranType = 'I' THEN PanaGoodsAmount - PanaAgentVatAmount ELSE -(PanaGoodsAmount - PanaAgentVatAmount) END) Total
FROM PurchaseTran
INNER Join PurchaseAnalysis on PurchaseAnalysis.PanaTranId = PurchaseTran.PtId
INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PtUnitId
INNER JOIN UnitAnalysisConfig on UnitAnalysisConfig.UACfgAnalysisCode = PurchaseAnalysis.PanaPCode AND UnitAnalysisConfig.UACfgType = 1
WHERE PurchaseAnalysis.PanaPCode = 'H995' AND (PtTranType = 'I' Or PtTranType = 'C') AND PtApproved =1
GROUP BY PtUnitId, PtPeriod, PtYear),
ctePurchases AS (
SELECT PurchaseTran.PtUnitId, PurchaseTran.PtPeriod, PurchaseTran.PtYear,
SUM(CASE WHEN PtTranType = 'I' THEN PtTotal WHEN PtTranType = 'C' THEN - PtTotal ELSE 0 END) AS PurchaseTotal,
SUM(CASE WHEN PtTranType = 'I' THEN PtAmount - PtAgentVatAmount WHEN PtTranType = 'C' THEN -(PtAmount - PtAgentVatAmount) ELSE 0 END) AS PurchaseNetTotal,
SUM(CASE WHEN PtTranType = 'I' THEN PtVat + PtAgentVatAmount WHEN PtTranType = 'C' THEN -(PtVat + PtAgentVatAmount) ELSE 0 END) AS VatTotal,
SUM(CASE WHEN PtSupplierId = CmpCashAccount THEN PtTotal ELSE 0 END) AS TotalCashPaid,
SUM(CASE WHEN PtTranType = 'I' THEN Catering.PanaGoodsAmount - Catering.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(Catering.PanaGoodsAmount - Catering.PanaAgentVatAmount) ELSE 0 END) CateringTotal,
SUM(CASE WHEN PtTranType = 'I' THEN Catering.PanaAgentVatAmount + Catering.PanaVatAmount WHEN PtTranType = 'C' THEN -(Catering.PanaAgentVatAmount + Catering.PanaVatAmount) ELSE 0 END) AS CateringVatAmount,
SUM(CASE WHEN PtTranType = 'I' THEN Vending.PanaGoodsAmount - Vending.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(Vending.PanaGoodsAmount - Vending.PanaAgentVatAmount) ELSE 0 END) VendingTotal,
SUM(CASE WHEN PtTranType = 'I' THEN Vending.PanaAgentVatAmount + Vending.PanaVatAmount WHEN PtTranType = 'C' THEN -(Vending.PanaAgentVatAmount + Vending.PanaVatAmount) ELSE 0 END) AS VendingVatAmount,
SUM(CASE WHEN PtTranType = 'I' THEN CansConf.PanaGoodsAmount - CansConf.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(CansConf.PanaGoodsAmount - CansConf.PanaAgentVatAmount) ELSE 0 END) CansConfTotal,
SUM(CASE WHEN PtTranType = 'I' THEN CansConf.PanaAgentVatAmount + CansConf.PanaVatAmount WHEN PtTranType = 'C' THEN -(CansConf.PanaAgentVatAmount + CansConf.PanaVatAmount) ELSE 0 END) AS CansConfVatAmount
FROM PurchaseTran
INNER JOIN CompParam on Compparam.CmpDefault = 1
INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PurchaseTran.PtUnitId
LEFT JOIN PurchaseAnalysis AS Catering on Catering.PanaTranId = PurchaseTran.PtId AND (Catering.PanaPCode = 'H201' OR Catering.PanaPCode = 'H401')
LEFT JOIN PurchaseAnalysis AS Vending on Vending.PanaTranId = PurchaseTran.PtId AND Vending.PanaPCode = 'H202'
LEFT JOIN PurchaseAnalysis AS CansConf on CansConf.PanaTranId = PurchaseTran.PtId AND CansConf.PanaPCode = 'H211'
WHERE (PtTranType = 'I' OR PtTranType = 'C') AND PtApproved = 1
GROUP BY PtUnitId, PtPeriod, PtYear),
cteSundryPurchases AS (
SELECT PtUnitId, PtYear, PtPeriod,
SUM(CASE WHEN PtTranType = 'I' THEN PanaGoodsAmount - PanaAgentVatAmount ELSE -(PanaGoodsAmount - PanaAgentVatAmount) END) Total,
SUM(CASE WHEN PtTranType = 'I' THEN PanaAgentVatAmount + PanaVatAmount WHEN PtTranType = 'C' THEN -(PanaAgentVatAmount + PanaVatAmount) ELSE 0 END) AS VatAmount
FROM PurchaseTran
INNER Join PurchaseAnalysis on PurchaseAnalysis.PanaTranId = PurchaseTran.PtId
INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PurchaseTran.PtUnitId
INNER JOIN UnitAnalysisConfig on UnitAnalysisConfig.UACfgAnalysisCode = PurchaseAnalysis.PanaPCode AND (UnitAnalysisConfig.UACfgType = 1 OR UnitAnalysisConfig.UACfgType = 3)
WHere UnitAnalysisConfig.UACfgTandOLabel = 'Sundry' AND (PtTranType = 'I' Or PtTranType = 'C') AND PtApproved = 1
GROUP BY PtUnitId, PtPeriod, PtYear),
cteDiscount AS (
SELECT PurchaseTran.PtUnitId, SUM(CASE WHEN PtTRanType = 'I' THEN PtDiscountAmount ELSE -PtDiscountAmount END) AS DiscountTotal, PtPeriod, PtYear
FROM PurchaseTran
WHERE PurchaseTran.PtApproved = 1 And (PurchaseTran.PtTranType = 'I' OR PurchaseTran.PtTranType = 'C')
GROUP BY PurchaseTran.PtUnitId, PtPeriod, PtYear),
cteUnitPeriodOpeningStock AS (
SELECT UStkId, UStkOpening, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo, SUM(OpeningCatering.USAnaAmount) AS OpeningCateringStock, SUM(OpeningBeverage.USAnaAmount) AS OpeningBeverageStock, SUM(OpeningSundry.USAnaAmount) AS OpeningSundryStock, SUM(OpeningCansConf.USAnaAmount) AS OpeningCansConfStock
FROM UnitStock
LEFT JOIN UnitStockAnalysis AS OpeningCatering ON UnitStock.UStkId = OpeningCatering.USAnaTranId AND OpeningCatering.USanaType = 'O' AND OpeningCatering.USAnaCode = 'CAT001'
LEFT JOIN UnitStockAnalysis AS OpeningBeverage ON UnitStock.UStkId = OpeningBeverage.USAnaTranId AND OpeningBeverage.USanaType = 'O' AND OpeningBeverage.USAnaCode = 'BEV001'
LEFT JOIN UnitStockAnalysis AS OpeningSundry ON UnitStock.UStkId = OpeningSundry.USAnaTranId AND OpeningSundry.USanaType = 'O' AND OpeningSundry.USAnaCode = 'SUN001'
LEFT JOIN UnitStockAnalysis AS OpeningCansConf ON UnitStock.UStkId = OpeningCansConf.USAnaTranId AND OpeningCansConf.USanaType = 'O' AND OpeningCansConf.USAnaCode = 'CAN001'
INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
INNER JOIN (
SELECT UStkUnitId, UStkPeriod, MIN(Week.WeekEndDate) OpeningDate
FROM UnitStock
INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
GROUP BY UStkUnitId, UStkPeriod) OpeningData on OpeningData.OpeningDate = Week.WeekEndDate AND OpeningData.UStkUnitId = UnitStock.UStkUnitId
GROUP BY UStkId, UStkOpening, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo),
cteUnitPeriodClosingStock AS (
SELECT UStkId, UStkClosing, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo, SUM(ClosingCatering.USAnaAmount) AS ClosingCateringStock, SUM(ClosingBeverage.USAnaAmount) AS ClosingBeverageStock, SUM(ClosingSundry.USAnaAmount) AS ClosingSundryStock, SUM(ClosingCansConf.USAnaAmount) AS ClosingCansConfStock
FROM UnitStock
LEFT JOIN UnitStockAnalysis AS ClosingCatering ON UnitStock.UStkId = ClosingCatering.USAnaTranId AND ClosingCatering.USanaType = 'C' AND ClosingCatering.USAnaCode = 'CAT001'
LEFT JOIN UnitStockAnalysis AS ClosingBeverage ON UnitStock.UStkId = ClosingBeverage.USAnaTranId AND ClosingBeverage.USanaType = 'C' AND ClosingBeverage.USAnaCode = 'BEV001'
LEFT JOIN UnitStockAnalysis AS ClosingSundry ON UnitStock.UStkId = ClosingSundry.USAnaTranId AND ClosingSundry.USanaType = 'C' AND ClosingSundry.USAnaCode = 'SUN001'
LEFT JOIN UnitStockAnalysis AS ClosingCansConf ON UnitStock.UStkId = ClosingCansConf.USAnaTranId AND ClosingCansConf.USanaType = 'C' AND ClosingCansConf.USAnaCode = 'CAN001'
INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
INNER JOIN (
SELECT UStkUnitId, UStkPeriod, MAX(Week.WeekEndDate) ClosingDate
FROM UnitStock
INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
GROUP BY UStkUnitId, UStkPeriod) ClosingData on ClosingData.ClosingDate = Week.WeekEndDate AND ClosingData.UStkUnitId = UnitStock.UStkUnitId
GROUP BY UStkId, UStkClosing, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo)

回复后编辑感谢您的反馈。我遵循了这些建议，并分别优化了每个 CTE 查询。我的错误是期望执行计划告诉我在运行视图时是否缺少建议的索引;直到我打破了查询，我才真正得到了任何建议。知道我没有做任何明显损害性能的明显事情也很有帮助。添加各种索引后，我得到了大约 30 秒的视图。

Answer 1

根据 Tim 的评论，(非具体化(视图仅执行与基础查询一样好。因此，首先分析查询以进行优化，例如在频繁使用的列上添加非聚集索引(在连接中或作为外键(。

除此之外，您还可以查看索引视图以进一步提高性能。但是索引视图也有一些问题，因为它们也不是提高视图性能的万无一失的方法，例如：

在对大量索引视图

1. 引用的表或较少但非常复杂的索引视图执行 DML 时，还必须更新这些引用的索引视图。因此，DML 查询性能可能会显著降低，或者在某些情况下，甚至无法生成查询计划。 在这种情况下，请在生产使用之前测试 DML 查询，分析查询计划并调整/简化 DML 语句，例如更新、删除或插入操作。
索引
2. 视图的成本在于维护聚集索引(以及您可以选择添加的任何非聚集索引(。必须权衡维护索引的成本与索引提供的查询优化的好处。

欲了解更多信息，请参阅：

https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-indexed-views-the-basics/

https://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer

Answer 2

如果所有 CTE 查询都以可接受的水平执行，但整个语句没有(通用(，则应将 CTE 转换为如下所示的临时查询：

--WITH
--ctePeriodWeekCount AS (...
--becomes
SELECT Count(Week.WeekNo) AS WeekCount, PrdNo AS Period, PrdYear AS Year
INTO #ctePeriodWeekCount FROM Week
INNER JOIN Period on Week.WeekEndDate BETWEEN Period.StartDate AND Period.EndDate
GROUP BY PrdNo, PrdYear

然后按照 CTE 顺序创建和使用适当的临时表(只需在名称前面添加一个 #(。不要忘记在上次使用时丢弃临时工以尽快释放资源。最后，如果在末尾使用了筛选器，请确保在临时创建 SELECT 语句时使用它。

即使最终的 CTE 性能接近单个 CTE，使用温度对性能也有好处 - 当将来发生更改时，它不太可能处理性能问题。