替换"Or"以避免两次条件评估并提高查询性能

Question

"Or"会降低查询性能，因为优化器必须评估这两个条件。在复杂的查询中，由于联接和繁重的表，计算两个条件变得非常昂贵。那个么"或"操作符的替代品是什么呢。我们每次都使用UNION吗？那么，将同一个复杂查询运行两次怎么样？相反，它也很贵。

编辑：

在下面的示例中，使用了六个表，每个表中都有超过100000条记录。或运算符介于ccm.cID=1001和（ma.coID=2 and ma.cID=1001）之间。为了获取记录，优化器必须评估这两个条件

SELECT dep.*
FROM dep with (NOLOCK)
JOIN ma with (NOLOCK) ON dep.mID = ma.mID  
LEFT JOIN ccm with (NOLOCK) ON ccm.cID = dep.cID  
LEFT JOIN ctm with (NOLOCK) ON ctm.cID = dep.cID  
LEFT JOIN cptgm with (NOLOCK) ON cptgm.cID = dep.cID  
WHERE ma.mtID = 3
AND dep.del = 0 AND dep.pub = 1   
AND (ccm.cID = 1001 OR (ma.coID = 2 AND ma.cID = 1001))  
AND ctm.tID = 2  
AND cptgm.ptgID IN (SELECT ptgID FROM psptgm WHERE psID = 145214
AND ib = 1)

如果我们将查询一分为二，每次只有一个条件，然后将其与UNION组合，则由于执行两次，性能会降低。

Answer 1

如果不了解或更改数据模型，您将无能为力

让我们一步一步来，在这个例子中找到一个小的优化：

首先将子查询提取到联接中，我相信这一更改将在某些版本的SQL上为您提供更好的性能。它还使查询更加清晰。

SELECT dep.*
FROM dep with (NOLOCK)
JOIN ma with (NOLOCK) ON dep.mID = ma.mID  
LEFT JOIN ccm with (NOLOCK) ON ccm.cID = dep.cID  
LEFT JOIN ctm with (NOLOCK) ON ctm.cID = dep.cID  
LEFT JOIN cptgm with (NOLOCK) ON cptgm.cID = dep.cID  
JOIN psptgm with (NOLOCK) ON cptgm.ptgID = psptgm.ptgID and psptgm.ib = 1 and psptgm.psID = 145214
WHERE ma.mtID = 3
  AND dep.del = 0 
  AND dep.pub = 1   
  AND (ccm.cID = 1001 OR (ma.coID = 2 AND ma.cID = 1001))  
  AND ctm.tID = 2

我也更喜欢这样写（我认为它更清晰）：

SELECT dep.*
FROM dep with (NOLOCK)
JOIN ma with (NOLOCK) ON dep.mID = ma.mID and  ma.mtID = 3
LEFT JOIN ccm with (NOLOCK) ON ccm.cID = dep.cID  
LEFT JOIN ctm with (NOLOCK) ON ctm.cID = dep.cID AND ctm.tID = 2
LEFT JOIN cptgm with (NOLOCK) ON cptgm.cID = dep.cID  
JOIN psptgm with (NOLOCK) ON cptgm.ptgID = psptgm.ptgID and psptgm.ib = 1 and psptgm.psID = 145214
WHERE dep.del = 0 
  AND dep.pub = 1   
  AND (ccm.cID = 1001 OR (ma.coID = 2 AND ma.cID = 1001))  

这使得仅联接修饰符与where子句相比更加清晰。

现在很容易看到或的常见部分，在主选项中选择并提取这些部分（从而减少或子句进行的检查次数并提高性能）：

WITH prequery AS
(
  SELECT dep.*
  FROM dep with (NOLOCK)
  LEFT JOIN ctm with (NOLOCK) ON ctm.cID = dep.cID AND ctm.tID = 2
  LEFT JOIN cptgm with (NOLOCK) ON cptgm.cID = dep.cID  
  JOIN psptgm with (NOLOCK) ON cptgm.ptgID = psptgm.ptgID and psptgm.ib = 1 and psptgm.psID = 145214
  WHERE dep.del = 0 AND dep.pub = 1
)
SELECT dep.*
FROM prequery with (NOLOCK)
JOIN ma with (NOLOCK) ON dep.mID = ma.mID and  ma.mtID = 3
LEFT JOIN ccm with (NOLOCK) ON ccm.cID = dep.cID  
LEFT JOIN cptgm with (NOLOCK) ON cptgm.cID = dep.cID  
WHERE ISNULL(ccm.cID,0) = 1001 OR (ma.coID = 2 AND ma.cID = 1001)