sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？

Question

我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用 DSQL 时，他说:

Static is preferred when it is not going to prevent cache reuse and dynamic is preferred when static will prevent cache reuse and reuse is desirable.

我问为什么静态 SQL 会阻止缓存重用，他说:

Apparently, when variables are passed to statement predicates it may prevent cache reuse of that execution plan, where DSQL will allow cache reuse in a stored procedure.

因此，例如:

select * from mytable where myvar = @myvar

我不是 SQL Server 执行计划方面的专家，但这对我来说似乎不合理。为什么引擎会在存储过程中的 DSQL 语句中保留统计信息，而不是静态 SQL 语句？

Answer 1

动态 SQL 的优点是每次运行时都会重新编译查询。这样做的好处是执行计划可以利用表上的最新统计信息和任何参数的值。

除了更具可读性之外，静态 SQL 还具有不需要重新编译的优点——节省了运行查询的步骤(好吧，如果算上解析-->编译-->执行，实际上是两个步骤)。这可能是也可能不是一件好事。

您可以使用 with (recompile) 强制重新编译静态计划。选项。

有时，您需要使用动态 SQL。不过，作为一般规则，在依赖动态 SQL 之前，我会使用编译器提示和其他努力来管理性能。