为什么在某些情况下CTE(公共表达式)和SQL Server与临时表相比，查询会变慢

清泉水

在几种情况下，我的complex CTE（Common Table Expressions）查询比使用中的临时表慢10倍SQLServer。
我的问题是关于查询的SQLServer处理CTE方法，它看起来像是试图连接所有分离的查询，而不是存储每个查询结果，然后试图操作以下查询。因此，这可能就是为什么临时表速度如此之快。
例如：
6 ]9 x, C! `) F, Y; D查询1    ：使用Common Table Expression：
;WITH Orders AS(    SELECT        ma.MasterAccountId,       IIF(r.FinalisedDate IS NULL,1,0)) [Status]    FROM         MasterAccount ma    INNER JOIN         task.tblAccounts a ON a.AccountNumber = ma.TaskAccountId                            AND a.IsActive = 1    LEFT OUTER JOIN         task.tblRequisitions r ON r.AccountNumber = a.AccountNumber     WHERE         ma.IsActive =                 AND CAST(r.BatchDateTime AS DATE) BETWEEN @fromDate AND @toDate        AND r.BatchNumber > 0),StockAvailability AS(    SELECT sa.AccountNumber,          sa.RequisitionNumber,          sa.RequisitionDate,          sa.Lines,          sa.HasStock,          sa.NoStock,          CASE WHEN sa.Lines = 0 THEN 'Empty         WHEN sa.HasStock = 0 THEN 'None         WHEN (sa.Lines > 0 AND sa.Lines > sa.HasStock) THEN 'Partial         WHEN (sa.Lines > 0 AND sa.Lines = ISNULL(rl.Quantity,1、0) AS HasStock,               SUM(IIF(ISNULL(psoh.AvailableStock,0) 查询2    :使用临时表:
DROP TABLE IF EXISTS #OrdersCREATE TABLE #Orders (MasterAccountId int,[Status] int);INSERT INTO #OrdersSELECT    ma.MasterAccountId,   dbo.fn_GetBatchPickingStatus(ma.BatchPickingOnHold,                                   iif(r.GroupNumber >                      iif(r.FinalisedDate is null,1,0)) [Status]FROM MasterAccount ma (nolock)INNER JOIN wh3.dbo.tblAccounts a (nolock) on a.AccountNumber = dbo.fn_RemoveUnitPrefix(ma.TaskAccountId) and a.IsActive = 1LEFT OUTER JOIN wh3.dbo.tblRequisitions r (nolock) on r.AccountNumber = a.AccountNumber WHERE cast(r.BatchDateTime as date) between @fromDate and @toDate    AND r.BatchNumber > 0    AND ma.IsActive = 1DROP TABLE IF EXISTS #StockAvailabilityCreate Table #StockAvailability (AccountNumber int,RequisitionNumber int,RequisitionDate datetime,Lines int,HasStock int,NoStock int);Insert Into #StockAvailabilitySELECT        r.AccountNumber,       r.RequisitionNumber,       r.RequisitionDate,       COUNT(rl.ProductNumber) Lines,       SUM(IIF(ISNULL(psoh.AvailableStock,0) >= ISNULL(rl.Quantity,1、0) AS HasStock,       SUM(IIF(ISNULL(psoh.AvailableStock,0)  0 AND sa.Lines > sa.HasStock) THEN 'Partial      WHEN (sa.Lines > 0 AND sa.Lines 答案很简单。
6 x/ d: c& l9 u  r, }- y. m! xSQL Server没有实现CTE。从执行计划中可以看出，它可以内联。
, }2 S. {# K- C4 Z3 M- ?! k其他DBMS它可能以不同的方式实现，一个著名的例子是Postgres，它确实实现了CTE(其实是幕后的CTE创建临时表)。
显式临时表中中中介显式的具体化是否更快，取决于查询。
在复杂的查询中，通过优化程序生成更有效、更简单的执行计划可以抵消将中间数据写入和读取临时表的费用。
/ ~  `! W2 k; A' U1 ^  r" A" i另一方面，在Postgres中，CTE是“优化围栏”，引擎无法将谓词推入CTE边界。
有时一种方法更好，有时另一种方法。一旦查询复杂度超过特定阈值，优化器将无法分析所有可能的数据处理方法，因此必须依靠某种方法。例如，连接表的顺序。排列的数量和要选择的表的数量成指数增加。Optimizer生成计划的时间有限，所以当内联所有CTE它可能是一个糟糕的选择。当您手动将复杂的查询分解为较小的简单查询时，您需要了解您的操作，但优化程序有更好的机会为每个简单的查询生成一个好的计划。