游标代码优化

jxqn_liu 2009-11-24 11:10:11
以下这段代码在两个数据库不在同一机器上而建立的链接服务器使用，执行的速度超慢，而如果两数据库在同一机器使用的话，速度也不太怎么样，执行40000多条的情况下，在同一机器上的时候大概6、7分钟，而使用链接服务器的，执行了10多分钟也才上传不过万条数据，这样的速度怎么可能接受。有没有高手能帮我优化，或者提提意见该怎么优化。



--先截出可以导入的内容，以加快游标速度

   Select a.*,convert(datetime,convert(varchar(10),dDate,111)+' '+convert(varchar(10),dCreate,108),120) dNewCreate  into #tmp09060501 from CA_UploadTmp a inner join  ems_001..C b on a.ccricode=b.bh 

     where cUserId=@cUserId and iExId>=0 and bSelect=1  --and  --and iSoType in ('CA','CD')



  Update CA_UploadTmp set iExId=(case when iExId=0 or iExId=-1 then -1 else -2 end)

    where cUserId=@cUserId and (not iId in (Select iId from #tmp09060501)) and dVaudit>=@dBDate and dVaudit<=@dEDate



  declare tItem cursor for

        SELECT iId,iSoType,cCriCode,iLinkId,cDeed,iExId,dNewCreate FROM #tmp09060501 --where cUserId=@cUserId and isnull(iExId,0)=0 



  open tItem

  FETCH NEXT FROM tItem into @iId,@cVtype,@CriCode,@iLinkId,@cDeed,@iExId,@dNewCreate

  WHILE (@@FETCH_STATUS <> -1) and  (@@FETCH_STATUS <> -2)

  BEGIN  

      --    if exists(Select bh from vw_jg_jbxx where bh=@CriCode)

      --    begin

      if @iExId=0 ----新的上传

      begin

        if @cVtype='CA' or @cVtype='CD' 

        begin

          insert into ems_001..A(bh,rq,jf,kf,ljf,db,tk,tkmc,gj,spr,yhbh,lrsj) 

           Select cCriCode,dbo.uf_dt_GetYMDStr(isnull(dVAudit,dVDate)),0

              ,isnull(fScore,0)

              ,isnull(cast(dbo.uf_CA_GetAssAch(1,cCriCode,@dDate

              ,isnull(dVAudit,dVDate),'','') as numeric),0)

              ,dbo.uf_UI_GetCode2Ex('部门代码',cDeptCode),cRuleCode,cRuleName,cVPersonName,cVAudit

              ,dbo.uf_GetUserN2C2(cMarker),@dNewCreate from #tmp09060501 Where iId=@iId 



          --select @iNewId=id from ems_001..A where bh=@CriCode and lrsj=@dNewCreate

          set @iNewId=@@IDENTITY

 

          --写备注字段

          Update ems_001..A set bz=@cDeed where Id=@iNewId

            --(select top 1 isnull(cRuleName,'')+char(13)+cDeed from CA_UploadTmp where iId=@iId) where Id=@iNewId

           --写ID回本系统的表中

          update CA_UploadTmp set iUploadState=2,iExId=@iNewId where iId=@iId

          if @cVtype='CA'

            update CA_DailyVouchs set dUpLoadTime=GetDate(),iUpLoadState=2,iExId=@iNewId where iId=@iLinkId  --(select iLinkId from CA_UploadTmp Where iId=@iId )

          else

            update Ca_Daily set dUpLoadTime=GetDate(),iUpLoadState=2,iExId=@iNewId where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )



          set @iKF=@iKf+1	

        end

        else 

        begin

          insert into ems_001..B(bh,cbrq,pzrq,jclb,jcyy,db,yhbh,lrsj,mj,sfsy)

            Select cCriCode,dbo.uf_dt_GetYMDStr(dVDate),dbo.uf_dt_GetYMDStr(isnull(dVAudit,dVDate))

              -- dbo.uf_UI_GetCode2Ex('类别',cEncCode)

              ,dbo.uf_CA_GetEncCode(cEncCode,1)

              ,'符合规定第'+cRuleCode+'条',dbo.uf_UI_GetCode2Ex('部门代码',cDeptCode)

              ,dbo.uf_GetUserN2C2(cMarker),@dNewCreate,0,1 from #tmp09060501 Where iId=@iId 



          select @iNewId=id from ems_001..B where bh=@CriCode and lrsj=@dNewCreate

          --set @iNewId=@@IDENTITY



          --写回备注字段    

          --Update ems_001..B set jcjs=(Select isnull(cRuleName,'')+' '+@cDeed from  #tmp09060501 where iId=@iId) where Id=@iNewId

          Update ems_001..B set jcjs=@cDeed  where Id=@iNewId



          --写ID回本系统的表中

          update CA_UploadTmp set iUploadState=2,iExId=@iNewId where iId=@iId

          if @cVtype='EA'

            update Ca_EncBatchVouchs set dUpLoadTime=GetDate(),iUpLoadState=2,iExId=@iNewId where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )

          else

            update Ca_EncVouch set dUpLoadTime=GetDate(),iUpLoadState=2,iExId=@iNewId where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )





          set @iJF=@iJF+1 

        end

      end

      else if @iExId>0 ----重新上传

      begin

        if @cVtype='CA' or @cVtype='CD' 

        begin

          Update a set bh=cCriCode,jf=0

             ,rq=dbo.uf_dt_GetYMDStr(isnull(dVAudit,dVDate))

             ,kf=isnull(fScore,0)

             ,ljf=isnull(cast(dbo.uf_CA_GetAssAch(1,cCriCode,@dDate,isnull(dVAudit,dVDate),'','') as numeric),0)

             ,db=dbo.uf_UI_GetCode2Ex('部门代码',cDeptCode)

             ,tk=cRuleCode

             ,tkmc=cRuleName

             ,gj=cVPersonName

             ,spr=cVAudit

             ,yhbh=dbo.uf_GetUserN2C2(cMarker)

             ,lrsj=@dNewCreate

             ,bz=@cDeed

            from ems_001..A a  inner join #tmp09060501 b on a.Id=b.iExId

              where b.iId=@iId

          --写ID回本系统的表中

          update CA_UploadTmp set iUploadState=2 where iId=@iId

          if @cVtype='CA'

            update CA_DailyVouchs set dUpLoadTime=GetDate(),iUpLoadState=2 where iId=@iLinkId  --(select iLinkId from CA_UploadTmp Where iId=@iId )

          else

            update Ca_Daily set dUpLoadTime=GetDate(),iUpLoadState=2 where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )



          set @iRKF=@iRKF+1	

        end



        --行政奖罚---------------------------------

        else 

        begin

          Update a set bh=cCriCode

             ,cbrq=dbo.uf_dt_GetYMDStr(dVDate)

             ,pzrq=dbo.uf_dt_GetYMDStr(isnull(dVAudit,dVDate))

             ,jclb=dbo.uf_CA_GetEncCode(cEncCode,1) --             dbo.uf_UI_GetCode2Ex('奖罚类别',cEncCode)

             ,jcyy='符合考核规定第'+cRuleCode+'条'

             ,jcjs=@cDeed --,jcjs=(isnull(cRuleName,'')+' '+@cDeed) 

             ,db=dbo.uf_UI_GetCode2Ex('部门代码',cDeptCode)

             ,yhbh=dbo.uf_GetUserN2C2(cMarker)

             ,lrsj=@dNewCreate,mj=0,sfsy=1

            from ems_001..B a  inner join #tmp09060501 b on a.Id=b.iExId

              where b.iId=@iId





          --写ID回本系统的表中

          update CA_UploadTmp set iUploadState=2 where iId=@iId

          if @cVtype='EA'

            update Ca_EncBatchVouchs set dUpLoadTime=GetDate(),iUpLoadState=2 where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )

          else

            update Ca_EncVouch set dUpLoadTime=GetDate(),iUpLoadState=2 where iId=@iLinkId --(select iLinkId from CA_UploadTmp Where iId=@iId )





          set @iRJF=@iRJF+1         

        end

      end

  FETCH NEXT FROM tItem into @iId,@cVtype,@CriCode,@iLinkId,@cDeed,@iExId,@dNewCreate

  END

  CLOSE tItem

  DEALLOCATE tItem
...全文
106 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
jxqn_liu 2009-11-25
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 yang_ 的回复:]
引用 6 楼 jxqn_liu 的回复:
我要达到的目的是：
  将本数据库的数据上传到其他数据，而在上传之后，要把那个数据库中刚才上传的记录标识返回到本数据与数据相对应（即把刚上传的数据id更新到本数据表中的iExid中）


这样的需求光用数据库实现有点太偏了，可能是造成你使用游标的原因，游标尽量避免

你现在的写法使用ems_001..A并非使用链接服务器，如果链接服务器应该更慢

太多的自定义函数使用也会造成性能问题

只是大概的印象，总而言之，要改的太多了

[/Quote]

谢谢指点，已经通过一个程序实现了，虽然比两个数据在同一机器上的情况下慢，但是比链接服务器的快多了
shiguangxin 2009-11-24
  • 打赏
  • 举报
 回复
据说
1\ 游标 效率不高
2\ 需要使用游标的地方尽量使用临时表
Q315054403 2009-11-24
  • 打赏
  • 举报
 回复
有偿支持，专业改进，提供后续的技术支持
Yang_ 2009-11-24
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 jxqn_liu 的回复:]
我要达到的目的是：
  将本数据库的数据上传到其他数据，而在上传之后，要把那个数据库中刚才上传的记录标识返回到本数据与数据相对应（即把刚上传的数据id更新到本数据表中的iExid中）
[/Quote]

这样的需求光用数据库实现有点太偏了，可能是造成你使用游标的原因，游标尽量避免

你现在的写法使用ems_001..A并非使用链接服务器，如果链接服务器应该更慢

太多的自定义函数使用也会造成性能问题

只是大概的印象，总而言之，要改的太多了
华夏小卒 2009-11-24
  • 打赏
  • 举报
 回复
备份，还原算了
jxqn_liu 2009-11-24
  • 打赏
  • 举报
 回复
我要达到的目的是：
将本数据库的数据上传到其他数据，而在上传之后，要把那个数据库中刚才上传的记录标识返回到本数据与数据相对应（即把刚上传的数据id更新到本数据表中的iExid中）
ChinaJiaBing 2009-11-24
  • 打赏
  • 举报
 回复
使用游标是要慢一些...
快乐_石头 2009-11-24
  • 打赏
  • 举报
 回复
圍觀並學習各位大大
--小F-- 2009-11-24
  • 打赏
  • 举报
 回复
找P梁一样
icelovey 2009-11-24
  • 打赏
  • 举报
 回复
游标4W多条. 好恐怖,....
楼主不如把需求写出来, 看看能不能用其他好点办法解决?
liangCK 2009-11-24
  • 打赏
  • 举报
 回复
这么长..要找乌龟了.
SQL Server游标的使用/关闭/释放/优化小结
游标是邪恶的！ 在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。 正常面向集合的思维方式是: 而对于游标来说: 这也是为什么游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能. 同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量…… 从游标对数据库的读取方式来说，不难看出游标为什么占用更多的资源，打个比方:     当你从ATM取钱的时候，是一次取1000效率更高呢，还是取10次100？ 既然游标这么“邪恶”，为什么还
SQL Server遍历表中记录的2种方法(使用表变量和游标)
SQL Server遍历表一般都要用到游标，SQL Server中可以很容易的用游标实现循环，实现SQL Server遍历表中记录。本文将介绍利用使用表变量和游标实现数据库中表的遍历。 表变量来实现表的遍历 以下代码中，代码块之间的差异已经用灰色的背景标记。 代码如下: DECLARE @temp TABLE ( [id] INT IDENTITY(1, 1) , [Name] VARCHAR(10) ) DECLARE @tempId INT , @tempName VARCHAR(10) INSERT INTO @temp VALUES ( ‘a’ ) INSERT INTO @temp
SQL Server 2014 索引和游标
主讲内容： 第一讲 课程简介 第二讲 索引简介 第三讲 创建索引 第四讲 查看索引 第五讲 索引管理 第六讲 全文索引 等课程
SQL Server 游标语句 声明/打开/循环实例
SQL Server游标语句使用方法： 代码如下: –声明一个游标 DECLARE MyCursor CURSOR FOR SELECT TOP 5 FBookName,FBookCoding FROM TBookInfo//定义一个叫MyCursor的游标，存放for select 后的数据 –打开一个游标 OPEN MyCursor//即打开这个数据集 –循环一个游标 DECLARE @BookName nvarchar(2000),@BookCoding nvarchar(2000) FETCH NEXT FROM MyCursor INTO @BookName,@BookCoding
MS SQL Server游标(CURSOR)的学习使用
说实的，使用MS SQL Server这样久，游标一直没有使用过。以前实现相似的功能，都是使用WHILE循环加临时表来实现。刚才有参考网上示例练习写了一下。了解到游标概念与语法。 下面代码示例中， 先是宣告你在游标中需使用变量，也就是临时存储处理字段的数据。 2. 宣告一个游标，并SELECT需要处理的数据集。 3. 打开游标(#8行代码)。 4. 从游标中拿来FETCH NEXT 数据给变量赋值。 5. 循环@@FETCH_STATUS = 0条件。 6. 在循环块，可以处理第一笔的记录逻辑了。本示例中是PRINT。 7. 逻辑处理完成，再次从游标中拿来FETCH NEXT 数据给变量赋值。
疑难问题

22,210

社区成员

121,727

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章