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

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

问题描述

发现一个问题挺奇怪,用户反馈有个报表查询一年的数据查询很慢,要 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,在每次调用都重新编译。
我觉得这个还是存储过程的执行计划导致的,存储过程的好处是编译一次,运行多次,执行计划被保留下来,减少了每次执行的编译时间,但坏处是执行计划相对是固定的。
当你的数据不断变化的时候,实际上也需要执行计划不断变化,而这一点又和存储过程本身固定的执行计划相互冲突,所以这个时候建议每次运行都编译,虽然编译会消耗时间,但是至少消耗的时间要比最慢的执行时间几十秒要小的多。

最后修改:2021 年 03 月 06 日 03 : 47 PM
如果觉得我的文章对你有用,请随意赞赏

发表评论