sql - 动态生成 where 子句的最佳方法是什么？

Question

我正在编写一个与大量 select 语句一起使用的存储过程。该存储过程接受大约 15 个参数来充当过滤器，所有这些参数都可以为 NULL。

参数通常做两件事 - 检查 x 是否在高值和低值之间或检查列值是否在 y 中。

我主要关心的是如何编写 where 子句。

示例:动态 SQL 非常慢，因此我不想编写 where 子句然后将其传递给 exec。

我不想做if High = null then High = max因为那样我仍然会有一个占用处理能力并且没有任何用处的 Between 语句。

我不想做 (if High = null or X <= High)因为空检查仍然会对每一行进行处理，而且我听到有传言说这会扰乱索引。

简而言之，我正在寻找考虑性能的最佳实践指导。

Answer 1

动态 SQL 过去很慢，因为动态生成的 SQL 的执行计划没有被缓存。现在情况不再是这样，只要查询文本相同，动态 SQL 查询的执行计划就会被缓存。这意味着您应该:

• 使用参数以避免参数值改变查询文本
• 尝试对 where 子句进行排序，以便它们以相同的顺序出现

只要您这样做，您的查询计划就应该被缓存(每个可能的查询变体都有一个)，并且动态 SQL 不会比任何正常查询慢。

<小时/>

要避免您的其他建议(将各种参数设置为 NULL)，并且实际上可能执行得非常糟糕 - 语句只能有一个缓存的查询计划，但是最佳查询计划将取决于参数，并且根据提供的值可能会有很大不同。

例如，一组参数可能会导致表的大部分内容被返回，在这种情况下，表扫描可能是最佳选择。另一组参数可能会导致返回单行，在这种情况下，行查找可能是最佳的。 SQL Server 必须选择缓存这两个计划中的一个(根据第一次运行查询时提供的参数，可能是最佳的一个)，但是无论它选择哪个计划，查询在相反的情况下都可能执行得很差。 (这是一种过度简化，但是我已经看到这种情况发生的变化，并且它可能会对性能产生非常显着的影响)。

最终这意味着:

• 大多数时候，选择的查询计划可能不是最佳的，或者
• 您可以强制 SQL Server 为每次执行生成一个新计划，这始终会产生合理的执行计划，但会消除计划缓存的优势

替代方法的另一个缺点是，它将导致更复杂的查询，这可能使查询优化器难以正确优化查询。

出于这些原因，我认为动态 SQL 绝对是更好的选择。