提高MS在联合操作上的性能

Question

我有一个MS表T1，它有三个代码列：CodeA、CodeB1和CodeB2。我有一个表类型变量，具有相同的列。

我必须将表变量与T1表连接起来，以便在表变量中获得与CodeB1和/或CodeB2匹配的行，但不匹配CodeA，也不匹配CodeA，但不匹配CodeB1或CodeB2。

我最初做了一个类似于SELECT的声明：

SELECT *
(SELECT 
 CASE WHEN t.CodeA = v.CodeA then 1 else 0 end as [EqualCodeA],
 CASE WHEN t.CodeB1 = v.CodeB1 then 1 else 0 end as [EqualCodeB1],
 CASE WHEN t.CodeB2 = v.CodeB2 then 1 else 0 end as [EqualCodeB2]
FROM @tableVariable v
INNER JOIN [T1] t
ON t.CodeA = v.CodeA or
   t.CodeB1 = v.CodeB1 or
   t.CodeB2 = v.CodeB2
)
WHERE NOT(EqualCodeA = 1 AND (EqualCodeB1 = 1 OR EqualCodeB2 = 1)

但是这个查询的性能很差。因此，我将OR在谓词中转换为UNION，如下所示：

SELECT *
(SELECT 
 CASE WHEN t.CodeA = v.CodeA then 1 else 0 end as [EqualCodeA],
 CASE WHEN t.CodeB1 = v.CodeB1 then 1 else 0 end as [EqualCodeB1],
 CASE WHEN t.CodeB2 = v.CodeB2 then 1 else 0 end as [EqualCodeB2]
 FROM @tableVariable v
 INNER JOIN [T1] t
 ON t.CodeA = v.CodeA

 UNION
 SELECT 
 CASE WHEN t.CodeA = v.CodeA then 1 else 0 end as [EqualCodeA],
 CASE WHEN t.CodeB1 = v.CodeB1 then 1 else 0 end as [EqualCodeB1],
 CASE WHEN t.CodeB2 = v.CodeB2 then 1 else 0 end as [EqualCodeB2]
 FROM @tableVariable v
 INNER JOIN [T1] t
 ON t.CodeB1 = v.CodeB1

 UNION
 SELECT 
 CASE WHEN t.CodeA = v.CodeA then 1 else 0 end as [EqualCodeA],
 CASE WHEN t.CodeB1 = v.CodeB1 then 1 else 0 end as [EqualCodeB1],
 CASE WHEN t.CodeB2 = v.CodeB2 then 1 else 0 end as [EqualCodeB2]
 FROM @tableVariable v
 INNER JOIN [T1] t
 ON t.CodeB2 = v.CodeB2)
)
WHERE NOT(EqualCodeA = 1 AND (EqualCodeB1 = 1 OR EqualCodeB2 = 1)

现在的表现是现在的十倍，但它仍然是不可接受的。例如，对于表变量中的10K行和T1中的50K行，查询需要两分钟。

从实际的执行计划来看，我看到了两个Hash Match (Union)操作，每个业务占成本的46%。

我怎样才能提高这方面的表现？

注释1：有一个包含所有三列的非聚集索引，以及三个单独的非聚集索引，每列一个。

注释2：为了使优化器至少知道表变量的实际行数，我使用了OPTION(RECOMPILE);。

Answer 1

老实说，我不太明白示例代码如何与您的需求描述相匹配，但假设后者是正确的，这就是我得出的结论：

-- in order to get the lines in table variable that match for CodeB1 and/or CodeB2, but not CodeA, 
-- or match CodeA, but neither CodeB1 nor CodeB2.
SELECT v.*
  FROM @tableVariable v
  JOIN T1 t
    ON (v.CodeA <> t.CodeA AND (v.CodeB1 = t.CodeB1 OR v.CodeB2 = t.CodeB2))
    OR (v.codeA = t.CodeA  AND v.CodeB1 <> t.CodeB1 AND v.codeB2 <> t.codeB2)


GO

-- convert OR into UNION
SELECT v.*
  FROM @tableVariable v
  JOIN T1 t
    ON (v.CodeA <> t.CodeA AND (v.CodeB1 = t.CodeB1 OR v.CodeB2 = t.CodeB2))

UNION

SELECT *
  FROM @tableVariable v
  JOIN T1 t
    ON (v.codeA = t.CodeA  AND v.CodeB1 <> t.CodeB1 AND v.codeB2 <> t.codeB2)


GO

-- further convert OR into UNION
SELECT v.*
  FROM @tableVariable v
  JOIN T1 t
    ON v.CodeA <> t.CodeA 
   AND v.CodeB2 = t.CodeB2

UNION

SELECT v.*
  FROM @tableVariable v
  JOIN T1 t
    ON v.CodeA <> t.CodeA 
   AND v.CodeB1 = t.CodeB1

UNION

SELECT v.*
  FROM @tableVariable v
  JOIN T1 t
    ON v.codeA = t.CodeA  
   AND v.CodeB1 <> t.CodeB1 
   AND v.codeB2 <> t.codeB2

-- potentially helpfull indexes
CREATE INDEX idx1 ON T1 (CodeA) INCLUDE (CodeB1, CodeB2)
CREATE INDEX idx2 ON T1 (CodeB1) INCLUDE (CodeA)
CREATE INDEX idx3 ON T1 (CodeB2) INCLUDE (CodeA)

这很可能与您的解决方案的成本完全相同，优化器可能(在内部)能够将它们转换为完全相同的操作。看到查询计划和/或对手头的数据有更好的了解会很有趣。

PS:正如在其他地方已经提到的，尽量避免@tableVariable，当处理更多的记录时，#tempTables要好得多。(你可以放索引，统计等.如有需要)

Answer 2

在这种情况下，您需要在T1上使用三个单独的非聚集索引。此外，如果这三个部分是相互排斥的，则改为“联合所有”，而不是“联合”。