求救高手: 为什么这个储存过程会运行得很慢? 运行一次要几分钟时间?

[tangweikai]

这是我的一个储存过程中的一段代码,表记录有12000,运行起来,可要花几分钟时间: 思路是
建了一个临时表.
再据这引临时表建立一个可更新的 cursor
然后据 cursor 来循环..并在[临时表]中插入记录,更新结余字段等..


如下:
-- 临时表
Create Table #BalnkAll(
[Page] integer NULL, -- 用以帐页页码
[PageRow] integer NULL, -- 用以帐页行数：用以作最后的排序
[zp_zdrq] [nvarchar] (10) NULL ,
[zp_bh] [nvarchar] (13) NULL,
[zp_pzbh] [nvarchar] (14) NULL ,
[zp_pjbh] [nvarchar] (18) NULL ,
[zp_kjkm] [nvarchar] (18) NULL ,
[zp_zy] [nvarchar] (200) NULL ,
[zp_skje] [numeric](15, 2) not NULL,
[zp_fkje] [numeric](15, 2) not NULL,
[zp_jcje] [numeric](15, 2) not NULL,
[zp_fdjsl] [integer] NULL ,
[zp_jsr] [nvarchar] (10) NULL ,
[zp_zd] [nvarchar] (10) NULL ,
[zp_sh] [nvarchar] (10) NULL ,
[zp_sm] [nvarchar] (500)NULL ,
[utimestamp] datetime NULL )

-- 插入“初始余额”
Insert Into #BalnkAll (zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje,zp_fdjsl,utimestamp)
Select initdate,'初始余额',initje, 0, initje,0,utimestamp from cwbankjl where zxid=@CurZxId
-- 插入收支记录
Insert Into #BalnkAll (zp_zdrq,zp_bh,zp_pzbh,zp_pjbh,zp_kjkm,zp_zy,zp_skje,zp_fkje,zp_jcje,zp_fdjsl,zp_jsr,zp_zd,zp_sh,zp_sm,utimestamp)
Select sk_zdrq,sk_bh,sk_pzbh,sk_pjbh,sk_kjkm,sk_zy,sk_skje, 0, 0,sk_fdjsl,sk_jsr,sk_zd,sk_sh,sk_sm,utimestamp from cwskjl
union all
Select fk_zdrq,fk_bh,fk_pzbh,fk_pjbh,fk_kjkm,fk_zy, 0,fk_fkje, 0,fk_fdjsl,fk_jsr,fk_zd,fk_sh,fk_sm,utimestamp from cwfkjl order by sk_zdrq,utimestamp
--
Declare @zpzdrq nvarchar (10) ,
@zpbh nvarchar (13) ,
@zpskje numeric(15, 2),
@zpfkje numeric(15, 2),
@IsFetched bit , -- 计数器：是否提取行
@nRow integer , -- 计数器：行数
@nPageNo integer , -- 计数器：页数
@ZPDate nvarchar(10), -- 计数器：日期
@ZPDateSRje numeric(15, 2), -- 本月收入合计
@ZPDateZCje numeric(15, 2), -- 本月支出合计
@ZPMonth nvarchar(7), -- 计数器：月份
@ZPMonthSRje numeric(15, 2), -- 本月收入合计
@ZPMonthZCje numeric(15, 2), -- 本月支出合计
@ZPyear nvarchar(4), -- 计数器：年份
@ZPYearSRje numeric(15, 2), -- 本年收入合计
@ZPYearZCje numeric(15, 2), -- 本年支出合计
@ZPTotalJCje numeric(15, 2) -- 累计结存金额
--
Update #BalnkAll set zp_skje=0 where zp_skje is null
Update #BalnkAll set zp_fkje=0 where zp_fkje is null
--
Set @IsFetched=0
Set @nRow = 0
Set @nPageNo = 1
Set @ZPTotalJCje = 0
--Declare #ScanRow SCROLL Cursor FOR Select zp_zdrq,zp_bh,zp_skje,zp_fkje FROM #BalnkAll
Declare #ScanRow SCROLL Cursor FOR Select zp_zdrq,zp_bh,zp_skje,zp_fkje FROM #BalnkAll --FOR UPDATE of Page,PageRow,zp_jcje
Open #ScanRow
fetch next from #ScanRow into @zpzdrq,@zpbh,@zpskje,@zpfkje
while @@fetch_status=0
begin --1
-- if @nRow=@CurPageRow
-- @nPageNo=@nPageNo+1
-- else

set @nRow = @nRow + 1
set @ZPTotalJCje = @ZPTotalJCje + @zpskje - @zpfkje
Update #BalnkAll set Page=@nPageNo,PageRow=@nRow,zp_jcje=@ZPTotalJCje WHERE CURRENT OF #ScanRow -- 出现错误:游标是READ ONLY 的
-- Update #BalnkAll set Page=@nPageNo,PageRow=@nRow,zp_jcje=@ZPTotalJCje WHERE zp_bh=@zpbh
if @nRow = 1 -- 初始余额
Begin
set @ZPDate = @zpzdrq
set @ZPDateSRje = 0
set @ZPDateZCje = 0
set @ZPMonth = left(@zpzdrq,7)
set @ZPMonthSRje = 0
set @ZPMonthZCje = 0
set @ZPyear = left(@zpzdrq,4) -- 初始年份
set @ZPYearSRje = 0
set @ZPYearZCje = 0
End
else -- 帐项记录
Begin --2
if @ZPDate = @zpzdrq --
Begin
set @ZPDateSRje = @ZPDateSRje+@zpskje
set @ZPDateZCje = @ZPDateZCje+@zpfkje
End
else --
Begin --3
set @nRow = @nRow + 1 -- 写入本日合计
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPDate,'本日合计',@ZPDateSRje,@ZPDateZCje,0)
if @ZPMonth = left(@zpzdrq,7)
Begin
set @ZPMonthSRje = @ZPMonthSRje + @ZPDateSRje -- 加本月累计
set @ZPMonthZCje = @ZPMonthZCje + @ZPDateZCje
End
else
Begin --4
set @nRow = @nRow + 1 -- 写入本月合计
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPDate,'本月合计',@ZPMonthSRje,@ZPMonthZCje,0)
if @ZPyear = left(@zpzdrq,4) -- 年结
Begin
set @ZPYearSRje = @ZPYearSRje + @ZPMonthSRje
set @ZPYearZCje = @ZPYearZCje + @ZPMonthZCje
End
else
Begin
set @nRow = @nRow + 1
-- Set @IsFetched=1
fetch next from #ScanRow into @zpzdrq,@zpbh,@zpskje,@zpfkje -- 下一行:判断是不是已结帐年份:
if @@fetch_status=0
Begin
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPyear + '12-31','本年合计',@ZPYearSRje,@ZPYearZCje,0)
End
else
Begin
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPDate,'本年合计',@ZPYearSRje,@ZPYearZCje,0)
End
fetch PRIOR from #ScanRow into @zpzdrq,@zpbh,@zpskje,@zpfkje --退一行:
set @ZPyear = left(@zpzdrq,4)
set @ZPYearSRje = 0
set @ZPYearZCje = 0
End
set @ZPMonth = left(@zpzdrq,7)
set @ZPMonthSRje = 0
set @ZPMonthZCje = 0
End --4
set @ZPDate = @zpzdrq
set @ZPDateSRje = @zpskje
set @ZPDateZCje = @zpfkje
End -- 3
End -- 2
-- if @IsFetched=0
fetch next from #ScanRow into @zpzdrq,@zpbh,@zpskje,@zpfkje
-- else
-- set @IsFetched=0
End -- 1
-- 写入最后一行

--
Close #ScanRow
Deallocate #ScanRow
--返回结果
Select * from #BalnkAll --where left(zp_zdrq,4)=@CurYear order by PageRow
GO

[rouqu]

太长了 具体处理没看明白 游标里面主要是对临时表进行插入 外部参数处理之后对表的插入看能不能集中点

[dichun]

第一: 可以为临时表创建索引;
如果你用的SQL 2005 ,可以改为WITH 语句来写,避免I/O ,
第二,建议最好不要用游标,改为批处理,

[rouqu]

Insert Into #BalnkAll (zp_zdrq,zp_bh,zp_pzbh,zp_pjbh,zp_kjkm,zp_zy,zp_skje,zp_fkje,zp_jcje,zp_fdjsl,zp_jsr,zp_zd,zp_sh,zp_sm,utimestamp)
Select sk_zdrq,sk_bh,sk_pzbh,sk_pjbh,sk_kjkm,sk_zy,sk_skje,0,0,sk_fdjsl,sk_jsr,sk_zd,sk_sh,sk_sm,utimestamp from cwskjl
union all
Select fk_zdrq,fk_bh,fk_pzbh,fk_pjbh,fk_kjkm,fk_zy,0,fk_fkje,0,fk_fdjsl,fk_jsr,fk_zd,fk_sh,fk_sm,utimestamp from cwfkjl
order by sk_zdrq,utimestamp --插入两遍？这里的order by也没必要吧

[rouqu]

sorry 看错了 原来还是两张表

如果变量不同 插入的个别列有区别 看能不能 一次判断 批量插入 里面游标好像多次循环 实在不是高效的处理方式

[rouqu]

还有 你中途的

if @@fetch_status=0
Begin
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPyear + '12-31','本年合计',@ZPYearSRje,@ZPYearZCje,0)
End
else --else这步走不到啊 外面已经有@@fetch_status的判断了
Begin
Insert Into #BalnkAll(Page,PageRow,zp_zdrq,zp_zy,zp_skje,zp_fkje,zp_jcje)
Values (@nPageNo,@nRow,@ZPDate,'本年合计',@ZPYearSRje,@ZPYearZCje,0)
End

[Limpire]

优化算法

[tangweikai]

谢谢大家..现在找到一此原因:我将其它语句一个一个地删除来测试的:
最主要的浪费时的两句就在:
fetch next from #ScanRow into @zpzdrq,@zpbh,@zpskje,@zpfkje
Update #BalnkAll set Page=@nPageNo,PageRow=@nRow,zp_jcje=@ZPTotalJCje WHERE CURRENT OF #ScanRow
查询2043条记录,它们要运行4000多次..浪费了大量时间.
总结:得出一条原则:不知对不对,请大家发表意见.
凡是涉及大量表的循环渐行操作,不要用游标来实现,
最后我将10006记录(注意不是测试的2043条),取到vs2005客户端,下载不到2秒,逐行计算,得出结果1秒左右,搞定..真是让我大开眼界...也希望大家引以为戒..