我写的一个存储过程,怎么执行速度比在查询分析器的慢很多呀?
我写的一个存储过程,怎么执行速度比在查询分析器的慢很多呀?
存储过程如下:
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秒,高手来帮忙呀,急死了!
存储过程如下:
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秒,高手来帮忙呀,急死了!
...全文
相关推荐
sqlserver查询分析器 win10可用
在win10(x64)下测试好用的SQL server查询分析器
DB2性能调优资料,解决SQL执行慢的问题
从网上找的一些关于DB2存储过程调优的方法和例子,希望对大家有帮助
sql查询分析器sql查询分析器
sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器sql查询分析器
SQL Server中存储过程比直接运行SQL语句慢的原因
1. 存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2. 经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据 库的使用效率,减少程序的执行时间,这一点在较大数据量的数据库的操作中是非常重要的。在代码上看,SQL语句和程序代码语句的分离,可以提高程序代码的 可读性。
非常好用的SQL执行查询分析器
无须安装SQL SEVER客户端,直接运行,然后连接需要连接的SQL SERVER数据库,非常方便。特跟大家分享。


疑难问题
