如何防止临时查询的重新编译

Question

当我第一次运行下面的查询时，重新编译发生了(7s)。如果我再次运行它，结果将在100毫秒内返回。但是，当我只更改数值时，它每次都坚持重新编译一次。

是否有一种方法可以防止重新编译，而无需使用SP包装它(已经验证可以工作)?

set statistics time on;
declare
@o bigint = 3374707
select * from ComplexTableValuedFunction(@o)
option (keep plan, use hint ('DISABLE_PARAMETER_SNIFFING')) -- has no effect in ad hoc?
set statistics time off;

附带问题:由于某种原因，统计消息只列出了短的执行时间(~100ms)，而没有列出编译时间。SQL哨兵验证编译时间为~7s。

Answer 1

任何时候临时批处理发生变化，即使只有一个字符，也会被重新编译。

为了防止重新编译，您需要使用sp_executesql传递批处理，并对其进行适当的参数化。此时，您将获得参数嗅探，除非您添加提示'DISABLE_PARAMETER_SNIFFING'

set statistics time on;
EXEC sp_executesql
N'
select * from ComplexTableValuedFunction(@o);
',
N'@o bigint',
@o = 3374707;
set statistics time off;

实际的EXEC语句没有查询计划，因此修改该值不会产生任何影响。

请注意，来自客户端应用程序(如c#/SqlClient)的参数化查询实际上无论如何都使用sp_executesql。