游标处理速度非常

一天到晚游泳的鱼2016 2012-09-26 11:06:36
游标处理速度非常，可是我通过声明游标需要循环的数据才三条，在线上跑这段sql用了一分多钟。
我处理过好几千条数据也没问题，但是就是这个三条数据的总是超时。

--打开游标
open GkMpExchange_cursor
--循环数据
fetch next from GkMpExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore,@createon
print @TotalCutScore

WHILE (@@FETCH_STATUS=0 and @TotalCutScore>0)
begin
--begin tran
select @Id=right(newid(),19)
if @CutScore<=@TotalCutScore
begin
set @TotalCutScore=@TotalCutScore-@CutScore
--update MPRuleHistory set CutScore=0 where id=@MPCutScoreDetailId
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,createon,CreateTime) values(@MPCutScoreDetailId,@CutScore,@MpRule,@userid,@IsMp,@isexchange,@createon,getdate())
end
else
begin
--set @CutScore=@CutScore-@TotalCutScore
--update MPRuleHistory set CutScore=@CutScore where id=@MPCutScoreDetailId
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,createon,CreateTime) values(@MPCutScoreDetailId,@TotalCutScore,@MpRule,@userid,@IsMp,@isexchange,@createon,getdate())
set @TotalCutScore=0
BREAK
end

fetch next from GkMpExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore,@createon
end
--关闭，释放游标资源
close GkMpExchange_cursor
DEALLOCATE GkMpExchange_cursor

select reg.loginname,mpj.GkDepartmentName,mpj.MpProjectName,r.name, mpd.CutScore,r.comment,mpd.createon,mpd.createtime from #SubMpScoreTable as mpd left join mprule as r on r.id=mpd.mprule left join MPProject as mpj on mpj.id=r.MpProjectId left join reguser as reg on reg.id=mpd.reguser where r.status='VLD' and mpj.status='VLD' order by mpd.createon asc

drop table #SubMpScoreTable
END
...全文
413 31 打赏 收藏 转发到动态 举报
写回复
用AI写文章
31 条回复
切换为时间正序
请发表友善的回复…
发表回复
汤姆克鲁斯 2012-09-29
  • 打赏
  • 举报
 回复
[Quote=引用 30 楼 的回复:]

这时定义游标，表A你想看什么，数据量大概100多万

有索引，主键聚集索引，
两个外键，非聚集索引

但数我筛选出定义游标的数据只有三条，用游标循环着三条。
[/Quote]
怎么可能是循环三条，你在循环体内加一个计数器看看。。。

一天到晚游泳的鱼2016 2012-09-27
  • 打赏
  • 举报
 回复
这时定义游标，表A你想看什么，数据量大概100多万

有索引，主键聚集索引，
两个外键，非聚集索引

但数我筛选出定义游标的数据只有三条，用游标循环着三条。
一天到晚游泳的鱼2016 2012-09-27
  • 打赏
  • 举报
 回复
declare GkMpExchange_cursor CURSOR FORWARD_ONLY FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore,createon from A where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' and createon<=''2012-09-11'' order by createon desc
汤姆克鲁斯 2012-09-27
  • 打赏
  • 举报
 回复
[Quote=引用 27 楼 的回复:]

我没发全，游标上面是定义游标的sql语句。

上面的执行IO，其实就是游标循环三次，处理这三条数据的IO情况

逻辑读取 ，是不是影响sql执行快慢的最主要因素
[/Quote]
。。。。。。。
你不发 那看个毛线啊。问题不是你游标慢啊 是那个表A 慢。。。。。。
一天到晚游泳的鱼2016 2012-09-27
  • 打赏
  • 举报
 回复
我没发全，游标上面是定义游标的sql语句。

上面的执行IO，其实就是游标循环三次，处理这三条数据的IO情况

逻辑读取 ，是不是影响sql执行快慢的最主要因素
汤姆克鲁斯 2012-09-27
  • 打赏
  • 举报
 回复
你搞的毛线啊 你的表a 在哪里
脚本中没有啊
一天到晚游泳的鱼2016 2012-09-27
  • 打赏
  • 举报
 回复
有索引，主键聚集索引，时间，两个外键，非聚集索引
sunylf 2012-09-27
  • 打赏
  • 举报
 回复
表 A。扫描计数 1，逻辑读取 185757 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表A建个索引吧。建了索引就快了。
一天到晚游泳的鱼2016 2012-09-27
  • 打赏
  • 举报
 回复
(1 行受影响)
表 A。扫描计数 1，逻辑读取 185757 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#74B97702'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)
表 A。扫描计数 1，逻辑读取 846587 次，物理读取 131 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#74B97702'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)
表 A。扫描计数 1，逻辑读取 6776871 次，物理读取 7907 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
0.00000

这是我游标读取这三条数据的IO，执行计划。
这个逻辑读取几十万次，是慢的原因吗？？？
發糞塗牆 2012-09-26
  • 打赏
  • 举报
 回复
循环嵌套的话2005以上可以使用CTE代替。
一天到晚游泳的鱼2016 2012-09-26
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]
你这个好像类似减库存，用关系型查询就可以啊


或者你先把最后那个select 注释了然后跑一下，看是你的游标慢呢
还是你的select 语句慢。
[/Quote]

你说对了，我这个 就是相当于减少库存，只不过减得是用户积分。

这个用户才三条数据，处理那么慢。后台处理过四五千的数据量，一点问题都没有。
一天到晚游泳的鱼2016 2012-09-26
  • 打赏
  • 举报
 回复
我是循环处理数据。

逻辑是这样的，网站上有积分，比如用户A要兑换什么东西，要消减100积分。
他这100积分是有来源记录的，首先定义游标取出这100积分的来源。

当用户兑换时，就循环此人的积分来源，处理这些记录，当这些记录都被消减完成，退出游标循环。

就是循环处理数据，不用游标，那写自定义函数，处理吗？
汤姆克鲁斯 2012-09-26
  • 打赏
  • 举报
 回复
你这个好像类似减库存，用关系型查询就可以啊


或者你先把最后那个select 注释了然后跑一下，看是你的游标慢呢
还是你的select 语句慢。
汤姆克鲁斯 2012-09-26
  • 打赏
  • 举报
 回复
你游标里面执行了多少次？

个人感觉好像不需要用游标来insert

發糞塗牆 2012-09-26
  • 打赏
  • 举报
 回复
游标处理慢是众所周知的，其实绝大部分数据库开发都不需要用到游标，一般在数据库维护方面才用到，你要不把你的表结构、少量数据、和要实现的功能列出来，各位大大会帮你找出更好的方法。我不建议使用游标。
一天到晚游泳的鱼2016 2012-09-26
  • 打赏
  • 举报
 回复
自己顶
一天到晚游泳的鱼2016 2012-09-26
  • 打赏
  • 举报
 回复
@TotalCutScore
从存储过程参数传过来，都大于0
但是在WHILE (@@FETCH_STATUS=0 and @TotalCutScore>0)
对@TotalCutScore 进行减操作，当@TotalCutScore =0时，不就退出了while了吗
duoxu1983 2012-09-26
  • 打赏
  • 举报
 回复
当 @TotalCutScore <=0 根本就不进while循环。
你 while退出的条件是当@CutScore>@TotalCutScore时执行一次@CutScore=@CutScore-@TotalCutScore 然后break退出while循环。
所以你先count(*)下 看看你游标的实际数据有多少。
一天到晚游泳的鱼2016 2012-09-26
  • 打赏
  • 举报
 回复
WHILE (@@FETCH_STATUS=0 and @TotalCutScore>0)
当@TotalCutScore <=0 不退出while循环吗？？？
汤姆克鲁斯 2012-09-26
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]

结果是能出来的就是很慢。
我用sql执行计划看 不是三条数据吗

到游标循环到第三次的时候，sql执行计划显示键值查找 聚集索引169万次。
如图
[/Quote]
减了这么多次？
我擦啊，那你临时表的结果是多少？
count 一下
加载更多回复（10）
C#对于Oracle游标一般处理程序速度的测试
C#对于Oracle游标一般处理程序速度的测试C#对于Oracle游标一般处理程序速度的测试
sql总结.doc
1、索引 ·什么是索引 ·索引的基本原理 ·索引的基本写法 ·索引的分类 ·索引的作用 ·索引的优缺点 ·索引的建立原则 2、存储过程 ·什么是存储过程 ·存储过程的基本写法 ·存储过程的作用 ·存储过程的优缺点 ·存储过程的应用场景 3、触发器 ·什么是触发器 ·触发器的的基本写法 ·触发器的功能 ·触发器的优缺点 ·触发器的两种形式 ·触发器的应用场景 4、视图 ·什么是视图 ·视图的基本写法 ·视图在哪些情况下不能被增、删、改 ·视图的作用 ·视图的优缺点 ·视图的应用场景 5、游标 ·什么是游标 ·游标的作用 ·游标的分类 ·游标的基本用法 ·使用游标的优缺点 ·什么时候选择使用游标 1、 （1）索引是对数据库表中一列或多列进行排序的一种结构。 （2）Mysql中搜索引擎Innodb（聚簇索引）和Mysiam(非聚簇索引)都采用B+,oracle也采用B+树实现 注：聚簇索引：一张表只能建立一个聚簇索引，以主键建立索引。聚簇索引包括主键索引和二级索引（二级索引是在对非主键字段建立索引后，通过索引找到对应这个非主键字段的主键，再进行主键索引，找到B+树中叶子结点）；（聚簇索引B+树中叶子结点存放的是数据和指向下一条数据的指针） 非聚簇索引：对主键和非主键字段建立索引时，直接找到B+树中的叶子结点，B+树中叶子结点存放的是对应数据的地址，并通过地址找到数据。 （3）Create index 索引名 on 表名(字段名) （4）索引分为聚簇索引和非聚簇索引 （5）索引的作用 1.创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 2.可以加大数据的检索速度，这也是创建索引的主要原因。 3.可以加速表与表之间的连接效率。 4.在分组排序时使用索引，可以减少分组排序的时间。 （6）索引的优缺点 索引的优点既索引的作用。 索引的缺点：1.建立索引会产生占据一定的空间。2.对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度。 （7）索引的建立原则 1.主外键 2.经常被查询的列 3.用于连接的字段 4.排序分组的字段 2、存储过程 （1）什么是存储过程？ 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 （2）存储过程的基本写法(oracle) Create or replace procedure 名称（参数） Is/as (变量的赋值例如：(a name%type)(a varchar(2))) Begin Sql语句以及判断条件 End 名称; （3）存储过程的作用 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而 一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速 度。 2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来 与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。 （4）存储过程的优缺点 优点： 作用的四条加上 1.分布式工作。 应用程序和数据库的编码工作可以分别独立进行，而不会相互影响。 缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。 2.可移植性差 由于存储过程将应用程序绑定到数据库，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。 （5）存储过程的应用场景 1.通常，复杂的业务逻辑需要多条  SQL  语句。这些语句要分别地从客户机发送到服务器，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户机和服务器之间的网络传输就会大大减少，降低了网络负载。 3、触发器 （1）什么是触发器 在Oracle中，触发器是一种特殊的存储过程，也是由一组sql语句以及一些业务逻辑代码组成的。数据库可以通过增、删、改来触发触发器。 （2）触发器的基本写法 create or replace trigger tri_adddept AFTER INSERT on dept For each row //表级不用加 declare begin dbms_output.put_line('插入了新部门'); end ; (3)触发器的功能 1.配合sequence实现自增(oracle) 2.提供审计和日志记录 3.写复杂的业务逻辑代码 （4）触发器的优缺点 优点： 1.实现级联更改 2.能过实现比check更严格的约束，与CHECK 约束不同，触发器可以引用其它表中的数据。通过其他表中的数据的变化来设置更加复杂的约束。 缺点：滥用触发器会造成数据库及应用程序的维护困难。 （5）触发器的两种形式（行级触发器，表级触发器） 1．在语法上，行级触发器就多了一句话：for each row 2．在表现上，行级触发器，在每一行的数据进行操作的时候都会触发。 语句级触发器，对表的一个完整操作才会触发一次。 简单的说：行级触发器，是对应行操作的；语句级触发器，是对应表操作的。 （6）应用场景 作用+优点 4、视图 （1）什么是视图？ 视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。 视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。 (2)视图的基本写法 Create view 视图名 as select * from emp; (3)视图在哪些情况下不能被增、删、改 （1）若视图的字段是来自字段表达式或常数，则不允许对此视图执行INSERT、UPDATE操作，允许执行DELETE操作； （2）若视图的字段是来自库函数，则此视图不允许更新； （3）若视图的定义中有GROUP BY子句或聚集函数时，则此视图不允许更新； （4）若视图的定义中有DISTINCT任选项，则此视图不允许更新； （5）若视图的定义中有嵌套查询，并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表，则此视图不允许更新； （6）若视图是由两个以上的基表导出的，此视图不允许更新； （7）一个不允许更新的视图上定义的视图也不允许更新； 本表的行列子集视图一般是可更新的。若视图的属性来自集函数、表达式，则该视图肯定是不可以更新的。 (4)视图的作用 1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。 2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限） 3、从而加强了安全性，使用户只能看到视图所显示的数据。 4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。 (5) 视图的优缺点 优点：1.简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。 2.安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。 3. 逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。 缺点：1.如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，也要花费一定的时间。 2.修改限制：当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。 （6）视图的应用场景 1.访问频繁，更新少的一组数据 2.需要对一些数据限制访问权限时 5、游标（cursor） （1）什么是游标 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。 （2）游标的作用 游标的一个常见用途就是保存查询结果，以便以后使用。 （3）游标的分类 游标分为显示游标和隐式游标 隐式游标：是用户操作SQL时自动生成的，而显式游标是在声明块中直接定义的游标。 （4）游标的基本用法 通过ROWCOUNT查看游标指向的行：（隐式游标实例） 例： DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count from dept; --返回一个隐式游标 dbms_output.put_line('SQL%ROWCOUNT = '||SQL%COUNT); END; / 执行结果：SQL%ROWCOUNT = 1 （显式游标基本） DECLARE CURSOR mycur IS SELECT * FROM dept; deptinfo dept%ROWTYPE; BEGIN IF mycur%ISOPEN THEN NULL; ELSE OPEN mycur; END IF; LOOP FETCH mycur INTO deptinfo; EXIT WHEN mycur%NOTFOUND; dbms_output.put_line('部门名称：'||deptinfo.dname); END LOOP; END; （配合for） DECLARE CURSOR mycur IS SELECT * FROM dept; deptinfo dept%ROWTYPE; BEGIN For test in mycur loop EXIT WHEN mycur%NOTFOUND; dbms_output.put_line('部门名称：'||test.dname); END LOOP; END; (5)游标的优缺点 优点： 游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作； 它还提供对基于游标位置而对表中数据进行删除或更新的能力 缺点： 处理大数据量时，效率低下，占用内存大 （6）什么时候选择使用游标？ 一般是在循环处理的时候使用。 比如你判断一个班上的同学数学成绩怎么样，你就可能用游标，先把全部的成绩查询到游标中，之后再循环一条条进行判断处理。
SQL Server的通用分页存储过程 未使用游标，速度更快！
使用SQL Server时，分页处理一直是个比较棘手的问题
存储过程：利用游标+临时表实现查询
存储过程使用游标、临时表实现动态SQL查询 2020.3.24 用能第二周周二上午，任务需求：编写存储过程查询监测设备状态以及目标监测设备状态。（任务完成） 改存储过程包含了大量相关知识，特此记录，以便此后使用！ 为什么要是用存储过程？ 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可以将复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发
大量数据处理优化方案
第一种方式：游标方式（sqlapi.dll支持得较好） //测试结果：200万数据，使用81-83秒；如果没有设置预取参数，10万条数据都要2分多钟 //使用游标返回结果集时，注意给游标设置PreFetchRows参数，会明显提高读取速度，这个参数要求可配置，因为测试发现这个参数值并没有固定最佳值(跟网络环境有关)。在获取数据集的字段数据时，应该使用索引，这样会更快些。字符字段应该使用asStringBuffer()函数来实现，这样会更快些。 //原理：数据库返回游标，程序分批获取数据
疑难问题

22,210

社区成员

121,726

社区内容

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

加载中

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

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

+ 用AI写文章