存储过程执行比直接执行语句慢很多,请帮忙看看原因

文盲老顾
WEB应用领新星创作者
博客专家认证
2018-04-17 02:44:21

-- 首先建立一个存储过程
CREATE PROCEDURE [dbo].[GetHotCompany]
@dt date,@ca int
AS
BEGIN
SET NOCOUNT ON;

select top 10 co_id,c_name from (
select db,tb,pk,count(0) as clicks from caigou_tj.dbo.PageViewBase a with (nolock)
where db=15 and tb=1419152101 and datediff(d,dt,@dt) between 0 and 1
group by db,tb,pk
) a
inner join caigou_2017.dbo.pub_company pa with (nolock) on pa.co_id=a.pk
inner join caigou_2017_query.dbo.query q with (nolock) on q.db=a.db and q.tb=a.tb and pa.c_id=q.pk
where @ca=0 or exists(select top 1 1 from caigou_2017_query.dbo.query q1 with (nolock) where c_id=q.pk and db=15 and tb=907150277 and exists(select top 1 1 from caigou_2017_query.dbo.query_path with (nolock) where qid=q1.id and col='ca' and val=@ca))
order by a.clicks desc
END

-- 初始化变量
declare @dt date,@ca int
select @ca=2172,@dt = convert(date,(select top 1 dt from caigou_tj.dbo.PageViewBase with (nolock) where dt<=getdate() order by dt desc))

-- 执行刚建立的存储过程
exec caigou_2017_query.dbo.GetHotCompany @dt,@ca
-- 直接执行语句,该语句与存储过程中的语句一模一样
select top 10 co_id,c_name from (
select db,tb,pk,count(0) as clicks from caigou_tj.dbo.PageViewBase a with (nolock)
where db=15 and tb=1419152101 and datediff(d,dt,@dt) between 0 and 1
group by db,tb,pk
) a
inner join caigou_2017.dbo.pub_company pa with (nolock) on pa.co_id=a.pk
inner join caigou_2017_query.dbo.query q with (nolock) on q.db=a.db and q.tb=a.tb and pa.c_id=q.pk
where @ca=0 or exists(select top 1 1 from caigou_2017_query.dbo.query q1 with (nolock) where c_id=q.pk and db=15 and tb=907150277 and exists(select top 1 1 from caigou_2017_query.dbo.query_path with (nolock) where qid=q1.id and col='ca' and val=@ca))
order by a.clicks desc

--------
co_id c_name
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
202629 北京悠客网信息技术有限公司
204625 北京东鼎恒昆科技发展有限公司
55828 上海昕宝电子科技有限公司
58038 深圳市兴鼎业科技有限公司
216404 深圳市加瑞尔科技有限公司
206860 广州市汇百美信息科技有限公司
211794 立图电子科技有限公司
202865 广州兴烨数码科技有限公司
210568 深圳市尚品鼎尊科技有限公司
221411 北京光大远见技术有限公司

表 'PageViewBase'。扫描计数 16,逻辑读取 44033 次,物理读取 0 次,预读 10 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'query'。扫描计数 0,逻辑读取 75815637 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'query_path'。扫描计数 4018,逻辑读取 124196 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'pub_company'。扫描计数 144743,逻辑读取 320377 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 106860 毫秒,占用时间 = 8981 毫秒。

co_id c_name
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
202629 北京悠客网信息技术有限公司
204625 北京东鼎恒昆科技发展有限公司
55828 上海昕宝电子科技有限公司
58038 深圳市兴鼎业科技有限公司
216404 深圳市加瑞尔科技有限公司
206860 广州市汇百美信息科技有限公司
211794 立图电子科技有限公司
202865 广州兴烨数码科技有限公司
210568 深圳市尚品鼎尊科技有限公司
221411 北京光大远见技术有限公司

(10 行受影响)

表 'PageViewBase'。扫描计数 16,逻辑读取 44033 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'query_path'。扫描计数 0,逻辑读取 209277 次,物理读取 1 次,预读 16 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'query'。扫描计数 3753,逻辑读取 23096 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'pub_company'。扫描计数 135065,逻辑读取 298967 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 4713 毫秒,占用时间 = 429 毫秒。


经查看,执行计划也是一模一样的,但是执行时间差别太大了,为什么存储过程的用时10秒左右,而直接执行只需要几百毫秒
...全文
909 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-04-17
  • 打赏
  • 举报
回复
参数嗅探 搜索一下到处都是, 就不重复说原理了, 没什么意思。

打个比方吧。
为了出门方便, 第一次出门就记下这次的行程, 比如需要多少钱, 需要坐什么交通工具。以后出门就不想了,直接按上次的来。
你第一次出门, 告诉你得去西藏, 你只好准备了两万块钱, 坐了飞机。
结果后面出门, 不需要去西藏了, 只是去家旁边的菜市场, 结果你还得准备一大笔钱, ……
但西藏可能你只是十年才去一趟,只占你出门次数的 万分之一, 每次出门都这样大费周折让人折腾得半死。
当然, 这只是比较极端的情况(如果你第一次出门正好要去菜市场就没这事了), 但是一旦碰到就会让人不知所措。

最简单的做法就是:每次出门都重新做计划, 虽然计划要花点时间, 但每次的行程都是精准的。

--方法一: 加 with recompile
CREATE PROCEDURE [dbo].[GetHotCompany]
@dt date,@ca INT
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;

select top 10 co_id,c_name from (
select db,tb,pk,count(0) as clicks from caigou_tj.dbo.PageViewBase a with (nolock)
where db=15 and tb=1419152101 and datediff(d,dt,@dt) between 0 and 1
group by db,tb,pk
) a
inner join caigou_2017.dbo.pub_company pa with (nolock) on pa.co_id=a.pk
inner join caigou_2017_query.dbo.query q with (nolock) on q.db=a.db and q.tb=a.tb and pa.c_id=q.pk
where @ca=0 or exists(select top 1 1 from caigou_2017_query.dbo.query q1 with (nolock) where c_id=q.pk and db=15 and tb=907150277 and exists(select top 1 1 from caigou_2017_query.dbo.query_path with (nolock) where qid=q1.id and col='ca' and val=@ca))
order by a.clicks desc
END
GO
--方法二:在语句最后面加 option(recompile)
CREATE PROCEDURE [dbo].[GetHotCompany]
@dt date,@ca INT
AS
BEGIN
SET NOCOUNT ON;

select top 10 co_id,c_name from (
select db,tb,pk,count(0) as clicks from caigou_tj.dbo.PageViewBase a with (nolock)
where db=15 and tb=1419152101 and datediff(d,dt,@dt) between 0 and 1
group by db,tb,pk
) a
inner join caigou_2017.dbo.pub_company pa with (nolock) on pa.co_id=a.pk
inner join caigou_2017_query.dbo.query q with (nolock) on q.db=a.db and q.tb=a.tb and pa.c_id=q.pk
where @ca=0 or exists(select top 1 1 from caigou_2017_query.dbo.query q1 with (nolock) where c_id=q.pk and db=15 and tb=907150277 and exists(select top 1 1 from caigou_2017_query.dbo.query_path with (nolock) where qid=q1.id and col='ca' and val=@ca))
order by a.clicks DESC option(recompile)
END
繁花尽流年 2018-04-17
  • 打赏
  • 举报
回复
引用 3 楼 superwfei 的回复:
换成在存储过程中声明的变量,速度和直接执行效率一样了,问题是为什么会这样?
记得有个特性Parameter sniffing你可以百度了解下,好像是直接用参数,存储过程的执行计划有时候会抽风。
文盲老顾 2018-04-17
  • 打赏
  • 举报
回复
换成在存储过程中声明的变量,速度和直接执行效率一样了,问题是为什么会这样?
繁花尽流年 2018-04-17
  • 打赏
  • 举报
回复
把参数换成变量 加上 declare @dt_1 date =@dt ,@ca_1 int=@ca 下面的代码里参数全换成申明的变量,再试试有没有变化
卖水果的net 2018-04-17
  • 打赏
  • 举报
回复
@fredrickhu 小 F ,你生意来了。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧