关于"游标会破坏整个系统的性能", 能证实吗？

set nocount ondeclare @COMPANY2 Char(1)
declare @BASEDATE datetime
declare @OPERATOR char(6)
set @COMPANY2  = (略)
set @OPERATOR  = (略)
set @BASEDATE  = getdate()
/*判断可行性*/
BEGIN TRANSACTION
....(略)
COMMIT TRANSACTION
/*revert to mtl*/
--临时表1
declare @TEMP table(PART char(12), LOCN char(4), Qty float, PRNT char(12), PQTY float, USAG float, CPNY char(1) )--临时表2
declare @BOM_TMP table(PART_BM char(12),YLD_BM float )declare BROWSE_EXP scroll cursor for
select part_eiv,
       locn_eiv,
       blce_eiv,
       cpny_eiv
  from __EXPIVN
    where ....(略)
     order by part_eivdeclare @PART char(12)
      , @LOCN char(4)
      , @Qty float
      , @PRNT char(12)
      , @PQTY float
      , @USAG float
      , @LDAT datetime
      , @CPNY char(1)
      , @PART2 char(12)
set @PART2='~'open BROWSE_EXP
fetch first from BROWSE_EXP into @PART,@LOCN,@QTY,@CPNY
while @@FETCH_STATUS = 0 begin  if @PART2<>@PART begin
    set @PART2=@PART
    delete from @BOM_TMP
      insert @BOM_TMP(PART_BM,YLD_BM) select PART_NB,YIELD from fCondenseBomMid(@COMPANY2,@PART,@BASEDATE,1)
  end  insert @TEMP(PART,LOCN,Qty,PRNT,PQTY,USAG,CPNY)
    select PART_BM,@LOCN,YLD_BM*@QTY,@PART,@QTY,YLD_BM,@CPNY
      from @BOM_TMP  fetch next from BROWSE_EXP into @PART,@LOCN,@QTY,@CPNYend
select * from @temp
Close  BROWSE_EXP Deallocate cursor BROWSE_EXP

另一段，用循环的方式替代了游标，原本以为这个执行起来会比上一段快，但结果却是恰恰相反。set nocount ondeclare @COMPANY2 Char(1)
declare @BASEDATE datetime
declare @OPERATOR char(6)
set @COMPANY2  = (略)
set @OPERATOR  = (略)
set @BASEDATE  = getdate()
/*判断可行性*/
BEGIN TRANSACTION
.....(略)
COMMIT TRANSACTION/*revert to mtl*/
--临时表1
declare @TEMP table(PART char(12), LOCN char(4), Qty float, PRNT char(12), PQTY float, USAG float, CPNY char(1) )--临时表2
declare @BOM_TMP table(PART_BM char(12),YLD_BM float )
select part_eiv,
       locn_eiv,
       blce_eiv,
       cpny_eiv
into #ivn_tmp     --将原游标的数据移入临时表
  from __EXPIVN
    where .....(略)
     order by part_eiv--搜索表中所有的货号,按顺序,唯一的记入临时表中,留待后面拆数用
select distinct part_eiv into #part_tb
  from #ivn_tmp
   order by part_eivdeclare @PART char(12)while exists(select part_eiv from #part_tb) begin   --循环的方式不变, 仅是取消游标做法  set @PART=(select top 1 part_eiv from #part_tb)  delete from @BOM_TMP    --计算单级用量
  insert @BOM_TMP(PART_BM,YLD_BM) select PART_NB,YIELD from fCondenseBomMid(@COMPANY2,@PART,@BASEDATE,1)     insert @TEMP(PART,LOCN,Qty,PRNT,PQTY,USAG,CPNY)  --返回表中相关的反拆量,并存入临时拆数表
    select A.PART_BM,B.LOCN_EIV,A.YLD_BM*B.BLCE_EIV,B.PART_EIV,B.BLCE_EIV,A.YLD_BM,B.CPNY_EIV
      from @BOM_TMP A, __EXPIVN B
        where B.PART_EIV=@PART and B.CPNY_EIV=@COMPANY2  delete #part_tb where part_eiv=@PARTendselect * from @TEMP

开销很大是说的夸张了跟踪一下使用游标后产生了多少锁，SqlServer是要维护这些锁的，这都是对性能有影响的，
当然我说的比较片面，大家一起讨论
我的观点是根据实际情况，能够不用就不用

    *   hb_gx
    * 高升
    * 等级：
发表于：2007-11-22 15:31:43 14 楼  得分:
开销很大是说的夸张了跟踪一下使用游标后产生了多少锁，SqlServer是要维护这些锁的，这都是对性能有影响的，
当然我说的比较片面，大家一起讨论
我的观点是根据实际情况，能够不用就不用
—————————————————————————————————————————
现在明摆着是用游标的速度快，你说怎样取舍？
对于客户来说，肯定是速度越快越好。

游标的好处我觉得就是做多少保存多少，如果一个n万的数据update一下，万一出现死锁之类的全部回滚完

游标的开销的确是很大。我以前写过一个脚本用游标的，在有6万条记录的表上，每条记录都要处理一下，要运行几十分钟，CPU和IO都很忙，甚至导致我们生产环境中的应用不能shut down，大汗啊.....没有办法改用另外一种方法，同样的表上，只有不到2分钟就完成了！用循环和用游标的脚本在小的表上看不出区别，数据量一大，SQL再忙着点其他事情，差别就出来了。大概看了一下你的代码，可能有两个原因导致你的循环的版本很慢：
1）使用了临时表（两个版本使用临时表的方法是否一致？？）
2）循环使用得不好。下面的方法你可以参考一下。但前提是你的表里面必须有一个字段的值可以排序，并且每个值在表里面是唯一的。否则的话，就只能用游标一个一个地爬了。下面的代码你可以参考一下：（假定在Accounts表中的ID字段符合上面的要求）
-- Initialize variables
SELECT @LoopFlag = 1
SELECT @NextRowId = MIN(Id) FROM Accounts-- Make sure the Accounts table has data.
IF ISNULL(@NextRowID,0) = 0
BEGIN
SELECT 'No record in found in Account table!'
RETURN
END-- Retrieve the first row
SELECT  @currID = id, @currAccount = account
FROM Accounts
WHERE Id = @NextRowIdWHILE @LoopFlag = 1
BEGIN    -- do you own stuff here
    ----------------------------    -- Reset looping variable
    SELECT   @NextRowId = NULL                -- Get the next row Id   这里是关键！
SELECT @NextRowId = MIN(Id)
FROM Accounts
WHERE Id > @currID    -- Verify if it's a valid Id  如果没有下一个最小ID就退出循环
IF ISNULL(@NextRowId,0) = 0
BEGIN
BREAK
END    -- get the next row  取下一行记录
SELECT  @currID = id, @currAccount = account
FROM Accounts
WHERE Id = @NextRowId
END

补充，上面的代码很快的原因是，
没有使用任何临时表。
SQL每次加锁表上的一条记录。不会影响对该表上其他记录的访问。如果用游标的话，根据isolation的级别，SQL需要在游标扫过的每条记录上放记录锁，才能保证事务完整性，所以很慢，而且可能因为不能放锁到某条记录上而等待很长时间，甚至超时而出错。

根据isolation的级别，SQL需要在游标扫过的每条记录上放记录锁
--------------------------------------------------
静态游标和键集驱动游标会将结果集或者键集拷贝到tempdb中再遍历，所以只会在拷贝的过程中对表加锁。
动态游标在默认的隔离级别下只是在fetch的一瞬间会对行加锁，fetch完毕就释放了。所以游标也并不是那么可怕。

我覺的用游標慢的意思是,關鍵是一條一條處理慢.
所以,建議用批量處理來代替 while 循環.
eg. 求和.你可以
declare @num int
set @num = 0
while (...)
begin
set @num = @num + @colvalue
end
而批量處理
select @num = sum(colvalue) from tb
就明顯的比上面的快.

大概看了一下你的代码，可能有两个原因导致你的循环的版本很慢：
1）使用了临时表（两个版本使用临时表的方法是否一致？？）
2）循环使用得不好。------------------------------------------------------------谢谢，两段程序使用临时表的方法基本一致。整个运算过程比较曲折，需要先将库存的记录逐条拿出来,再到BOM中计算出所需要的原材料,然后把库存的记录化为原材料进行一系列的运算。
后来用循环替游标的那一段，使用的临时表多了，我再试试减少临时表，改善循环那一段的做法，并且加大测试量来试一试。感谢上面各位给我的指导，你们继续讨论，我先得消化一下，测试后再将结果告诉大家。

自然就好罢。集合操作自然不用游标，不是集合操作非不用游标性能自然不行。使用游标性能好不好只能看你的应用了，ado分页其实就是用游标实现。但是什么都用游标，sql 就退化成3gl了。像争论什么临时表、表变量、游标性能好不好最没意思，能得出答案也不要当真，其实问题只是：我这个需求用它好不好，而从来不是什么东西好不好。

自然就好罢。集合操作自然不用游标，不是集合操作非不用游标性能自然不行。使用游标性能好不好只能看你的应用了，ado分页其实就是用游标实现。但是什么都用游标，sql 就退化成3gl了。像争论什么临时表、表变量、游标性能好不好最没意思，能得出答案也不要当真，其实问题只是：我这个需求用它好不好，而从来不是什么东西好不好。
--------------------------------------------------------------------嗯，说得有道理。但这并不防碍我的求知欲望。
说真，我是半路出家的，好多东西都不懂，上来提问并非哗众取宠，确实是应用过程中存在着疑惑。可能标题有点太过，但实质我关不是要让不同泒别的支持者出来争辩，而是希望借助这个平台了解大家的见解，学习人家的所长。不是争论，是学习！
谢谢你的意见。

先比较循环方式哪一种快, 等选定后再加大数据量比较用游标与不用游标的区别.
修改后的循环版本,减少了临时表, 并且给表增加了以PART排序的索引：（一）....../*revert to mtl*/
--临时表1
declare @TEMP table(PART char(12), LOCN char(4), Qty float, PRNT char(12), PQTY float, USAG float, CPNY char(1) )--临时表2
declare @BOM_TMP table(PART_BM char(12),YLD_BM float )drop table #part_tb--搜索表中所有的货号,按顺序,唯一的记入临时表中,留待后面拆数用
select distinct part_eiv
into #part_tb    --将原游标的数据移入临时表
  from EXPIVN
    where ......
     order by part_eivdeclare @PART char(12)--用之前的老模式进行循环
while exists(select part_eiv from #part_tb) begin
  set @PART=(select top 1 part_eiv from #part_tb)
  delete #part_tb where part_eiv=@PART
end--以上代码执行, 7332条记录,6秒的时间
(二)
......./*revert to mtl*/
--临时表1
declare @TEMP table(PART char(12), LOCN char(4), Qty float, PRNT char(12), PQTY float, USAG float, CPNY char(1) )--临时表2
declare @BOM_TMP table(PART_BM char(12),YLD_BM float )drop table #part_tb--搜索表中所有的货号,按顺序,唯一的记入临时表中,留待后面拆数用
select distinct part_eiv
into #part_tb    --将原游标的数据移入临时表
  from EXPIVN
    where ......
     order by part_eiv
declare @PART char(12)
declare @PART_NEXT char(12)
declare @LoopFlag intselect @LoopFlag = 1
select @PART_NEXT=MIN(PART_eiv) from #part_tb
set @PART='~'while @LoopFlag=1 begin   if @PART<>@PART_NEXT begin
     set @PART=@PART_NEXT
   end   select @PART_NEXT=MIN(PART_EIV) FROM #PART_tb WHERE PART_EIV>@PART
   IF ISNULL(@PART_NEXT,'')=''  breakend--以上代码执行, 7332条记录,18秒的时间

写漏了, 不可以修改, 上面"并且给表EXPIVN增加了以PART为排序的索引"

while exists(select part_eiv from #part_tb) begin --循环的方式不变, 仅是取消游标做法
set @PART=(select top 1 part_eiv from #part_tb)
...
delete #part_tb where part_eiv=@PART
-------------------
这三句就是导致比游标慢的原因

谢谢小楼! 上次回贴太冒失了，真抱歉。
:)能帮我看看上面的第二段,改变了
while exists(select part_eiv from #part_tb) begin --循环的方式不变, 仅是取消游标做法
set @PART=(select top 1 part_eiv from #part_tb)
...
delete #part_tb where part_eiv=@PART
这三句不再使用, 改成了从小到大取值的方法,但为什么速度还是快不起来呢,

在通过设置一个循环变量就能替代游标的情况下，用循环变量比游标快一点点，唯一区别是open游标需要耗时。如果需要通过select/delete表来达到游标的效果，肯定比游标慢，否则MS就没必要提供游标这样的功能了。游标必要的时候使用，如果不必要时使用，导致性能下降，那是程序员的水平问题。所以，研究游标的性能，没有太大的意义，不如研究一下，什么情况下必须使用游标。

看了，第二个用了聚合函数MIN，所以比第一种更糟糕：select @PART_NEXT=MIN(PART_eiv) from #part_tb

    while exists(select part_eiv from #part_tb) begin --循环的方式不变, 仅是取消游标做法
      set @PART=(select top 1 part_eiv from #part_tb)
      ...
      delete #part_tb where part_eiv=@PART
这三句就是导致比游标慢的原因————————————————————————
还有更好的方法吗？

在通过设置一个循环变量就能替代游标的情况下，用循环变量比游标快一点点，唯一区别是open游标需要耗时。
---------
首先，这句话不对，起码属于片面的，游标可以update/delete current of...，又会比update/delete where ?=循环变量快。所以游标是不能替代的。其次，对于楼主的需求，貌似没有更好的方法。

在通过设置一个循环变量就能替代游标的情况下，用循环变量比游标快一点点，唯一区别是open游标需要耗时。
---------
首先，这句话不对，起码属于片面的，游标可以update/delete current of...，又会比update/delete where ?=循环变量快。所以游标是不能替代的。其次，对于楼主的需求，貌似没有更好的方法。-------------------------------------------------------------
谢谢小楼！我在SQL2000以及SQL2005里做测试的时候，就感觉到，单是进行循环，游标比一般的while要快。
对于什么情况适合用游标，什么情况可以用其他方法替代，还得多学习，多比较。

2005看以看看CTE能不能实现
---------------------------
看不懂呢，太多专业名词了。
2005我是刚刚装好试好，不太了解。

WITH Common_Table_Expression (Transact-SQL) 指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

具体查 SQL Server 2005 联机丛书索引 --> CTE

看了你29楼的代码，
第一种方法，我仔细想了一下，还是比较不错的。每次循环之后，就删除了一条记录，所以随着循环次数的增加，临时表里面的记录越来越少，速度就会越来越快。第二种方法，每次都从固定数量的记录中选一个，到后面肯定没有第一种方法快。也许你可以试着在这个临时表上对PART_EIV建索引，应该能更快一些。我原来的代码中，要操作的表有6万多条记录，而且每条记录都很大，所以不想建临时表，太慢了，而且那个表上对ID有个主键索引，所以就非常快。你的第一种方法，如果每条记录不大的话，也算是个好方法的，越到后面会越快，再加上SELECT TOP 1也加快查询返回的速度。

WITH Common_Table_Expression (Transact-SQL) 指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。-----------------------------------------------------------------
粗略看了一下说明，说明中介绍“递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。”
抽时间试一试，看除了简化了语句之外，对性能有没有提高。另外，对于什么情况下必须使用游标，兄弟姐妹们请继续发表高见哦！

请教一下CTE的问题：
使用递归公用表表达式时，我想每拆一次就把父级的数据清掉，保留子级的数据又往下拆,如此循环下去，直到不能再往下拆，就保留那一级的数据。
有办法做到吗？跑题了。：D

请教一下CTE的问题：
使用递归公用表表达式时，我想每拆一次就把父级的数据清掉，保留子级的数据又往下拆,如此循环下去，直到不能再往下拆，就保留那一级的数据。
有办法做到吗？跑题了。
-----------------------------------------
已经解决了，下一步试试性能上有多大的提高。

小楼提到的CTE, 试过了,觉得很方便,尤其是用在BOM计料,查询方面.
只是有些情况非常特殊的,在里头要做太多的判断时,那还是要用旧的递归办法解决.

强悍，还没结贴，我也发表一下看法
也学习了，游标有这优点，速度快那为什么不多用游标，不过我实在还没感觉到游标的用途！dotnetbar 看到了这控件有谁用过吗？

调试易

关于"游标会破坏整个系统的性能", 能证实吗？

解决方案 »