SQL Server-外部带有WHERE子句的UNION在简单联接时速度非常慢

Question

我有一个简单的查询，它运行速度很快(<1sec(：

;WITH JointIncomingData AS
(
SELECT A, B, C, D FROM dbo.table1
UNION ALL
SELECT A, B, C, D FROM dbo.table2
)
SELECT * 
FROM JointIncomingData D 
WHERE a = '1/1/2020'

然而，如果我在最后的SELECT语句中加入另一个小表，则非常慢(>30秒(

DECLARE @anotherTable TABLE (A DATE, B INT) 
INSERT INTO @anotherTable (AsOfDate, FundId) 
VALUES ('1/1/2020', 1)
;WITH JointIncomingData AS
(
SELECT A, B, C, D FROM dbo.table1
UNION ALL
SELECT A, B, C, D FROM dbo.table2
)
SELECT * 
FROM JointIncomingData D 
JOIN @anotherTable T ON T.A = D.A AND T.B = D.B

在实际应用程序中，我有一个复杂的UPDATE作为最终语句，所以我尽量避免复制粘贴，并引入UNION来合并代码。

但现在经历了一个意想不到的缓慢问题。

我尝试使用UNION ALL而不是UNION，结果相同。

看起来SQL Server向每个UNION语句推送了简单的条件，但当我将它与另一个表连接时，它不会发生，而是会进行表扫描。

有什么建议吗？

更新：这是预计的计划对于第一个简单条件查询：https://www.brentozar.com/pastetheplan/?id=SJ5fynTgP

对于带有联接表的查询：https://www.brentozar.com/pastetheplan/?id=H1eny3pxP

请记住，估计的计划并不完全适用于上述查询，而是更真实的计划，存在完全相同的问题。

Answer 1

当我进行复杂的更新时，我通常会声明一个临时表，并将要更新的行插入其中。这种方法有两个好处，一个是通过显式地收集要更新的行，可以简化逻辑，使更新本身变得非常简单(只需更新主键在临时表中的行(。它的另一大好处是，在实际运行更新之前，您可以进行一些健全性检查；抛出错误"；通过返回不同的值。我认为将查询分解为这样的简单步骤通常是一种很好的做法，因为这会使它们在未来更容易进行故障排除。

Answer 2

基于"类似的"；你分享的执行计划。最好有一个实际的计划，知道你的估计和内存分配是否可以

密钥查找

索引IX_dperf_date_fund应扩展到以下列nav、equity的INCLUDE

为什么？索引返回的每一行，都在clusterd索引中创建一个查找，以检索nav、equity的列值。

只有当这对应用程序来说是合理的，如果其他查询也可能受益

CTE

将CTE更改为临时表。

示例：

SELECT *
INTO #JointIncomingData 
FROM (
SELECT AsOfDate, FundId, DataSourceId, ShareClass, NetAssetsBase, SharesOutstanding 
FROM 
ETL.tblIncomingData
UNION ALL
SELECT AsOfDate, FundId, DataSourceId, ShareClass, NetAssetsBase, SharesOutstanding 
FROM ETL.vIncomingDataDPerf
) x

为什么？CTE没有具体化。这个答案是

奖励：参数嗅探

如果传入参数，您可能会受到参数嗅探的影响。