SQL Server：存储过程在SSMS中执行很快，通过C#程序调用执行很慢

此文章发布于 38 个月前，部分信息可能已经过时，请自行斟酌确认。

问题描述

发现一个问题挺奇怪，用户反馈有个报表查询一年的数据查询很慢，要 1 分钟左右，让我优化下，于是在 ssms 中执行了下报表所调用的存储过程，发现只用4秒就查询完成，并且只有1996行记录。
于是我再打开程序试下发现确实卡住了很慢，要1分钟才出来结果。

一句话说明问题：存储过程在SSMS中执行很快，通过C#程序调用执行很慢。

原因分析

参考：https://blog.csdn.net/weixin_30527143/article/details/97863922

由于存储过程是预编译的, 在第一次执行的时候，会生成执行计划，以后执行的时候，会使用这个执行计划(除非存储过程侯或者显示指定重新编译)，而不是每次执行时都去生成执行计划。
当存储过程涉及的对象结构调整，或者相关的数据产生了很大变化，这可能导致原来的计划不适合当前的现状(执行计划过期)，这种情况下应该重新编译存储过程。

解决方案

1、手动重编译：

exec sp_recompile @objname='存储过程名称'

2、如果是执行频率不高的存储过程，可以在脚本中设置每次执行自动重编译，如：

ALTER PROC [dbo].[存储过程名称]
    @Date1 DATETIME,
    @Date2 DATETIME
WITH recompile --设置重编译避免执行计划过期导致程序调用时执行慢
AS
BEGIN
...
END

3、参数嗅探方式（此方法同事提供待验证是否有效）

注意：此方法同事提供待验证是否有效，观察一段时间再来更新...

把存储过程的参数赋值给存储过程中自定义的变量，整个存储过程中使用这新定义的变量来代替参数，并且在参数赋值的地方加上 OPTION (OPTIMIZE FOR UNKNOWN)

ALTER PROC [PM_SP_Ygqjtj] 
    @qsrq DATETIME,
    @zzrq DATETIME,
    @qjrGuid VARCHAR(2000) 
AS 

declare @start datetime, @end datetime, @ryGuid varchar(2000)
select @start = @qsrq, @end = @zzrq, @ryGuid = @qjrGuid OPTION (OPTIMIZE FOR UNKNOWN)

--后面用 @start @end @ryGuid 代替输入的参数来使用

扩展阅读

如果这个存储过程执行次数不是太频繁，建议直接在 exec 时加上 with recompile，在每次调用都重新编译。
我觉得这个还是存储过程的执行计划导致的，存储过程的好处是编译一次，运行多次，执行计划被保留下来，减少了每次执行的编译时间，但坏处是执行计划相对是固定的。
当你的数据不断变化的时候，实际上也需要执行计划不断变化，而这一点又和存储过程本身固定的执行计划相互冲突，所以这个时候建议每次运行都编译，虽然编译会消耗时间，但是至少消耗的时间要比最慢的执行时间几十秒要小的多。