存儲過程執行慢

Taidy_Du 2008-10-14 11:22:38
各位,我下面的語法寫使用
declare @dStartDate as datetime
declare @dendDate as datetime

set @dStartDate ='2008.10.01'
set @dEndDate ='2008.10.14'

Select ISnull(G.Rs_Gh,H.Rs_Gh) as Rs_Gh,IsNull(G.Kq_JbSj,0) as Kq_JbSj ,IsNull(H.CqJs,0) as CqJS
From (Select Rs_Gh, Sum(Kq_JbSj/60) as Kq_JbSj
From T_Kq_OkkqMx Where Kq_KqRq Between @dStartDate and @dEndDate
Group By Rs_Gh )G
Full join ---Full 前為已經計算的加班時間。
(Select E.Rs_Gh,Sum(CqJs) as CqJs
From (Select A.Jb_Rq ,Sum( CqJs/60) as CqJs,A.Rs_Gh From T_Kq_JbDj A
Left Join T_Kq_JbUp B on A.Up_Id=B.Id Left Join T_Kq_Bc C on A.Kq_Jb=C.Kq_Bc
Where DelSign=0 and sqr_Audit =1 and Jb_rq Between @dStartDate and @dEndDate
Group By A.Jb_rq,A.Rs_Gh ) E
Left Join
(Select Kq_KqRq ,Kq_JbSj/60 as Kq_JbSj ,Rs_Gh
From T_Kq_OkkqMx Where Kq_KqRq Between @dStartDate and @dEndDate) F
ON E.Rs_Gh=F.Rs_Gh and E.Jb_rq=F.Kq_Kqrq
Where Kq_KqRq Is Null Group by E.Rs_Gh )H
on G.Rs_Gh=H.Rs_Gh WHere IsNull(G.Kq_JbSj,0)+IsNull(H.CqJs,0)>0

需要差不多3-4秒時間,而把它變為存儲過程,則需要1分鐘多鐘的時間,
請各位幫忙分析一下,問題在那?在線等。
...全文
94 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
Garnett_KG 2008-10-14
  • 打赏
  • 举报
回复

--先试下这个:

DROP PROC SPName --先删掉你的sp
go

CREATE PROC spNAME
@dStartDate as datetime,
@dendDate as datetime
WITH RECOMPILE
AS

Select ISnull(G.Rs_Gh,H.Rs_Gh) as Rs_Gh,IsNull(G.Kq_JbSj,0) as Kq_JbSj ,IsNull(H.CqJs,0) as CqJS
From (Select Rs_Gh, Sum(Kq_JbSj/60) as Kq_JbSj
From T_Kq_OkkqMx Where Kq_KqRq Between @dStartDate and @dEndDate
Group By Rs_Gh )G
Full join ---Full 前為已經計算的加班時間。
(Select E.Rs_Gh,Sum(CqJs) as CqJs
From (Select A.Jb_Rq ,Sum( CqJs/60) as CqJs,A.Rs_Gh From T_Kq_JbDj A
Left Join T_Kq_JbUp B on A.Up_Id=B.Id Left Join T_Kq_Bc C on A.Kq_Jb=C.Kq_Bc
Where DelSign=0 and sqr_Audit =1 and Jb_rq Between @dStartDate and @dEndDate
Group By A.Jb_rq,A.Rs_Gh ) E
Left Join
(Select Kq_KqRq ,Kq_JbSj/60 as Kq_JbSj ,Rs_Gh
From T_Kq_OkkqMx Where Kq_KqRq Between @dStartDate and @dEndDate) F
ON E.Rs_Gh=F.Rs_Gh and E.Jb_rq=F.Kq_Kqrq
Where Kq_KqRq Is Null Group by E.Rs_Gh )H
on G.Rs_Gh=H.Rs_Gh WHere IsNull(G.Kq_JbSj,0)+IsNull(H.CqJs,0)>0

Taidy_Du 2008-10-14
  • 打赏
  • 举报
回复
是啊。
pt1314917 2008-10-14
  • 打赏
  • 举报
回复
把它变为存储过程。没有加任何东西?

22,301

社区成员

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

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