我写的一个存储过程,怎么执行速度比在查询分析器的慢很多呀?

meng_1226 江苏常州 开发部  2006-03-09 10:02:02
我写的一个存储过程,怎么执行速度比在查询分析器的慢很多呀?

存储过程如下:

CREATE procedure [pr_StubsA]
@StubsDate Datetime

AS

--SET NOCOUNT ON

--如果已有结存记录,则删除重新结存
if ((Select count(*) From StubsA Where Datediff(m,SDate,@StubsDate)=0)>0)
Delete From StubsA Where Datediff(m,SDate,@StubsDate)=0


--清空临时表
--Truncate Table StubsTemp1
CREATE TABLE #StubsTemp1 (GoodID INT PRIMARY KEY)


--添加上月结存表里的GoodID
Insert into #StubsTemp1 Select GoodID From StubsA Where Datediff(m,SDate,@StubsDate)=1 and (SNum<>0)

--添加当月入库的GoodID
Insert into #StubsTemp1 Select Distinct GoodID From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and StorID=1 and GoodID not in (Select GoodID From #StubsTemp1)

Insert into StubsA Select
@StubsDate,GoodID,

--上月库存
isnull((Select SNum From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LNum,
isnull((Select SWeight From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LWeight,
isnull((Select SMoney From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LMoney,
--当月正常入库(包括退库的方材)
(Select isnull(Sum(GNum*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InNum,
(Select isnull(Sum(GWeight*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InWeight,
(Select isnull(Sum(GMoney*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InMoney,
--当月其他入库
(Select isnull(Sum(GNum),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InMoney1,
--当月正常出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutNum,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutWeight,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutMoney,
--当月其他出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutMoney1,
--当月库存
0,0,0
From #StubsTemp1


--SET NOCOUNT OFF
GO

我在查询分析器里运行exec pr_stubsA '2006-3-1' ,执行时间要1分20多少秒,
但我执行下面的语句只要3秒,高手来帮忙呀,急死了!

declare @StubsDate datetime
set @stubsdate='2006-3-1'
--SET NOCOUNT ON

--如果已有结存记录,则删除重新结存
if ((Select count(*) From StubsA Where Datediff(m,SDate,@StubsDate)=0)>0)
Delete From StubsA Where Datediff(m,SDate,@StubsDate)=0


--清空临时表
--Truncate Table StubsTemp1
CREATE TABLE #StubsTemp1 (GoodID INT PRIMARY KEY)


--添加上月结存表里的GoodID
Insert into #StubsTemp1 Select GoodID From StubsA Where Datediff(m,SDate,@StubsDate)=1 and (SNum<>0)

--添加当月入库的GoodID
Insert into #StubsTemp1 Select Distinct GoodID From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and StorID=1 and GoodID not in (Select GoodID From #StubsTemp1)

Insert into StubsA Select
@StubsDate,GoodID,

--上月库存
isnull((Select SNum From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LNum,
isnull((Select SWeight From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LWeight,
isnull((Select SMoney From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=#StubsTemp1.GoodID),0) as LMoney,
--当月正常入库(包括退库的方材)
(Select isnull(Sum(GNum*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InNum,
(Select isnull(Sum(GWeight*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InWeight,
(Select isnull(Sum(GMoney*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InMoney,
--当月其他入库
(Select isnull(Sum(GNum),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=#StubsTemp1.GoodID) as InMoney1,
--当月正常出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutNum,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutWeight,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutMoney,
--当月其他出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=#StubsTemp1.GoodID) as OutMoney1,
--当月库存
0,0,0
From #StubsTemp1


我在查询分析器里运行exec pr_stubsA '2006-3-1' ,执行时间要1分20多少秒,
但我执行下面的语句只要3秒,高手来帮忙呀,急死了!




...全文
126 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
bugchen888 2006-03-09
把菜单里那个
--->query----->show excution plan
选项打开.
 回复 赞
子陌红尘 2006-03-09
把临时表换成表变量，再试试二者之间的差别。
 回复 赞
OracleRoob 2006-03-09
在查询分析器中分别执行存储过程和SQL语句，查看执行计划
 回复 赞
meng_1226 2006-03-09
to wangtiecheng(cappuccino)

我在事件探查器中监视,
就只显示 存储过程总执行的时间,166秒,没有显示存储过程里面的各语句的执行时间呀!!!!
 回复 赞
OracleRoob 2006-03-09
在事件探查器中监视一下，分析一下执行的瓶颈在哪个环节
 回复 赞
meng_1226 2006-03-09
而且在查询分析器里运行的语句就是存储过程里的内容,
只不过把@StubsDate定义了一个变量!

大家快帮忙呀!!!!
 回复 赞
meng_1226 2006-03-09
搞不懂了,
还是先完成任务再说吧!

谢谢各位了!
 回复 赞
子陌红尘 2006-03-09
一个猜测：因为楼主之前的存储过程里创建了临时表，而存储过程内部创建的临时表的索引无法被预编译的存储过程引用，从而导致了查询效率的低下。
 回复 赞
meng_1226 2006-03-09
to libin_ftsafe(子陌红尘) :
多谢了,用表变量是可以提高执行时间了,只不过还要40多秒的,

奇怪了,
我把上面的存储过程分成两个,把临时表变成表只执行时间只要2秒了
奇怪了,有哪位大侠遇到过这种情况!我的存储过程如下

CREATE procedure [pr_StubsA_1]
@StubsDate Datetime

AS

SET NOCOUNT ON

--如果已有结存记录,则删除重新结存
if ((Select count(*) From StubsA Where Datediff(m,SDate,@StubsDate)=0)>0)
Delete From StubsA Where Datediff(m,SDate,@StubsDate)=0


--清空临时表
Truncate Table StubsTemp1
--CREATE TABLE #StubsTemp1 (GoodID INT PRIMARY KEY)


--添加上月结存表里的GoodID
Insert into StubsTemp1 Select GoodID From StubsA Where Datediff(m,SDate,@StubsDate)=1 and (SNum<>0)

--添加当月入库的GoodID
Insert into StubsTemp1 Select Distinct GoodID From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and StorID=1 and GoodID not in (Select GoodID From StubsTemp1)

SET NOCOUNT OFF
GO


CREATE procedure [pr_StubsA_2]
@StubsDate Datetime

AS

SET NOCOUNT ON

Insert into StubsA Select
@StubsDate,GoodID,

--上月库存
isnull((Select SNum From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=StubsTemp1.GoodID),0) as LNum,
isnull((Select SWeight From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=StubsTemp1.GoodID),0) as LWeight,
isnull((Select SMoney From StubsA Where Datediff(m,SDate,@StubsDate)=1 and StubsA.GoodID=StubsTemp1.GoodID),0) as LMoney,
--当月正常入库(包括退库的方材)
(Select isnull(Sum(GNum*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=StubsTemp1.GoodID) as InNum,
(Select isnull(Sum(GWeight*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=StubsTemp1.GoodID) as InWeight,
(Select isnull(Sum(GMoney*(case when typeid=1 then 1 else -1 end)),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID in (1,10) and DetsAViewI.GoodID=StubsTemp1.GoodID) as InMoney,
--当月其他入库
(Select isnull(Sum(GNum),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=StubsTemp1.GoodID) as InNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=StubsTemp1.GoodID) as InWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewI Where Datediff(m,BDate,@StubsDate)=0 and TypeID=3 and DetsAViewI.GoodID=StubsTemp1.GoodID) as InMoney1,
--当月正常出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutNum,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutWeight,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=2 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutMoney,
--当月其他出库
(Select isnull(Sum(GNum),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutNum1,
(Select isnull(Sum(GWeight),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutWeight1,
(Select isnull(Sum(GMoney),0) From DetsAViewO Where Datediff(m,BDate,@StubsDate)=0 and TypeID=4 and DetsAViewO.GoodID=StubsTemp1.GoodID) as OutMoney1,
--当月库存
0,0,0
From StubsTemp1

SET NOCOUNT OFF
GO

然后在查询分析器里执行
exec pr_stubsa_1 '2006-3-1'
exec pr_stubsa_2 '2006-3-1'
只要2秒

哪位大侠给解释一下?

 回复 赞
相关推荐
「风险评估」支持国产密码算法的OpenSSL设计实现及应用 - 金融安全.zip 「风险评估」支持国产密码算法的OpenSSL设计实现及应用 - 金融安全 WAF 基础架构安全 WAF 网络安全 APT
erlang-hipe-19.3.6.4-1.el7.x86_64.rpm erlang-hipe-19.3.6.4-1.el7.x86_64.rpm
authd-1.4.3-42.el7.x86_64.rpm 离线安装包，亲测可用
狂神说Linux01：概述及环境搭建.md 狂神说Linux01：概述及环境搭建.md
erlang-edoc-19.3.6.4-1.el7.x86_64.rpm erlang-edoc-19.3.6.4-1.el7.x86_64.rpm
四年级语文下册周测试题.pdf 四年级语文下册周测试题.pdf
erlang-cosTime-19.3.6.4-1.el7.x86_64.rpm erlang-cosTime-19.3.6.4-1.el7.x86_64.rpm
第一个编程.sln hello 今日开启自学之旅，荣幸加入大家庭
2021-2022平衡发展的AI伦理观——AI可持续发展报告.pdf 2021-2022平衡发展的AI伦理观——AI可持续发展报告.pdf
jdk-8u311-linux-x64 jdk-8u311-linux-x64.tar.gz
exabgp-4.0.1-3.el7.noarch.rpm exabgp-4.0.1-3.el7.noarch.rpm
hex_bin_trans.py python 二进制文件和十六进制文件转换 hex bin 文件转换脚本 须用python2运行
kernel-plus-headers-3.10.0-1160.6.1.el7.centos.plus.x86_64.rpm kernel-plus-headers-3.10.0-1160.6.1.el7.centos.plus.x86_64.rpm
黑龙江省齐齐哈尔市高中化学第二章化学物质及其变化第三节氧化还原学案2无答案新人教版必修1.doc 黑龙江省齐齐哈尔市高中化学第二章化学物质及其变化第三节氧化还原学案2无答案新人教版必修1.doc
boost159-atomic-1.59.0-2.el7.1.x86_64.rpm boost159-atomic-1.59.0-2.el7.1.x86_64.rpm
fuse-sshfs-2.5-1.el7.x86_64.rpm fuse-sshfs-2.5-1.el7.x86_64.rpm
matlab 时间序列分析.docx matlab 时间序列分析.docx
设备名称、技术参数及功能要求.docx 设备名称、技术参数及功能要求.docx
bpftool-3.10.0-1160.31.1.el7.centos.plus.x86_64.rpm bpftool-3.10.0-1160.31.1.el7.centos.plus.x86_64.rpm
androidterm安卓终端模拟器.rar androidterm安卓终端模拟器，用于在安卓手机上运行shell命令
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-03-09 10:02
社区公告
暂无公告