性能问题：多个条件 WHERE 子句，还是动态查询？

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子句如何影响性能？动态查询是否会带来性能损失？

谢谢

- Heathcliff

---

如果您在列上有索引，则动态SQL更好，因为它可以利用这些索引。 - Gordon Linoff

http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql - granadaCoder

虽然我留了一个网址来“消除恐惧”，但我讨厌动态SQL。它是一个很滑的坡道，也是一个维护噩梦。最终，我会设置两种方式，并测试它们。索引是一回事，计划重复使用是另一回事（请阅读文章）。如果你最终选择了动态方式，请确保注释为什么选择这种方式。 - granadaCoder

基准测试和比较。 - sstan

请查看此篇文章，其中涵盖了与此类查询完全相符的内容。http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ - Sean Lange

Answer 1

哪个选项更好？

在您的情况下，我更喜欢使用动态SQL。

当我基本上执行“WHERE true”时，这些WHERE子句如何影响性能？

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

动态查询是否会影响性能？

如果您在过滤列上有索引，则动态查询的性能更好。

- StackUser