sql - 性能: multiple conditional WHERE clauses,还是动态查询？

Question

我遇到以下问题。我必须对带有多个过滤器的非常大的表进行查询。这些过滤器不是强制性的:也就是说，有时查询必须使用某个过滤器(例如，在 ColumnA 上)，有时则不需要。这些过滤器作为参数传递给存储过程(例如，@filterA，对于 ColumnA)。

假设可能的过滤器位于 ColumnA、ColumnB、ColumnC 和 ColumnD 上(过滤它们的值是 @filterA、...、@filterD)。如果我不需要应用特定的过滤器，则参数将作为 NULL 传递。目前，我正在考虑使用“条件”WHERE 子句做这样的事情:

SELECT *
FROM LargeTable
WHERE (@filterA IS NULL OR ColumnA = @filterA)
  AND (@filterB IS NULL OR ColumnB = @filterB)
  AND (@filterC IS NULL OR ColumnC = @filterC)
  AND (@filterD IS NULL OR ColumnD = @filterD)

这是一种可能性，但我想知道它的性能如何，因为我将在每种情况下应用所有这些 WHERE 子句，并且该表非常大。

使用动态查询是否更好，只有当我实际上必须使用特定的WHERE子句时才应用它？像这样的事情:

DECLARE @query varchar(MAX)
SET @query = 'SELECT * FROM LargeTable WHERE ';
IF @filterA IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'ColumnA = ', @filterA)
END
IF @filterB IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnB = ', @filterB')
END
IF @filterC IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnC = ', @filterC')
END
IF @filterD IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnD = ', @filterD')
END

EXEC sp_executesql @query 

更好的选择是什么？当我基本上执行“WHERE true”时，WHERE 子句如何影响这里的性能？动态查询是否会带来性能损失？

谢谢

Answer 1

What's the better option?

就您而言，我更喜欢动态 SQL。

How do the WHERE clauses affect performance here when I'm basically doing WHERE true?

无论您是否提供参数，都会对每一行评估 where 子句中的所有条件。

Does the dynamic query carry a performance penalty?

如果您在过滤列上有索引，那么您的动态性能会更好。