而且在查询分析器里运行的语句就是存储过程里的内容,
只不过把@StubsDate定义了一个变量!大家快帮忙呀!!!!
只不过把@StubsDate定义了一个变量!大家快帮忙呀!!!!
解决方案 »
- sql事件探查器如何限制到数据库
- 如何匹配以下字符串:';,0,2,;'
- 求一SQL语句
- ******单列转多行
- 如何收缩TEMPDB数据文件
- 装了SQL SERVICE PACK3,客户端的QQ都不能用了,urgent!
- 用sql表设计器就可以删除,这是为什么?
- 我的日志文件丢失了,请问如何才能让数据库正常使用(在线等待)?
- 请问在MIS系统中怎样跟踪一款产品,要让客户知道他的每款产品当前的状态?
- 请教:算术函数中有平方根SQRT(),我想求多次方根,应该怎么办呢?谢谢!
- 用代码(api,sql)怎么判断SQL Server 已经启动并正在运行中......
- 请问各位高手,数据库出现置疑,有哪些原因及解决的办法
就只显示 存储过程总执行的时间,166秒,没有显示存储过程里面的各语句的执行时间呀!!!!
--->query----->show excution plan
选项打开.
多谢了,用表变量是可以提高执行时间了,只不过还要40多秒的,奇怪了,
我把上面的存储过程分成两个,把临时表变成表只执行时间只要2秒了
奇怪了,有哪位大侠遇到过这种情况!我的存储过程如下CREATE procedure [pr_StubsA_1]
@StubsDate DatetimeASSET 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 DatetimeASSET NOCOUNT ONInsert 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秒哪位大侠给解释一下?