[转帖]游标脚本性能问题详解之案例实践篇_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3951 | 回复: 0   主题: [转帖]游标脚本性能问题详解之案例实践篇        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-3 16:11:49 | [全部帖] [楼主帖] 楼主

游标类型对性能影响的实例。下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标。

    不理想的游标类型:(dynamic游标)

declare @p1 int  set @p1=NULL
declare @p2 int  set @p2=0
declare @p5 int  set @p5=4098
declare @p6 int  set @p6=8193
declare @p7 int  set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT       T1.CONFLICT_ID
FROM         dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE  ((T1.BC_BASE_TBL = @P1)
AND  (T1.RECORD_ID = @P2))
ORDER BY  T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'
print 'fetch'
exec sp_cursorfetch @p2,2,4,1
exec sp_cursorclose @p2


  理想的游标类型(fast forward only游标)

declare @p1 int  set @p1=NULL
declare @p2 int  set @p2=0
declare @p5 int  set @p5=4112
declare @p6 int  set @p6=8193
declare @p7 int  set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT       T1.CONFLICT_ID
FROM         dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE  ((T1.BC_BASE_TBL = @P1)
AND  (T1.RECORD_ID = @P2))
ORDER BY  T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6'
select @p1, @p2, @p5, @p6, @p7
print '2'
exec sp_cursorfetch @p2,2,1,1
print '3'
exec sp_cursorclose @p2


  注:脚本中用到的和游标有关的存储过程,请参考:http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec

    如何解读游标的类型

sp_cursorprepexec [@handle =] statement_handle OUTPUT,
[@cursor =] cursor_handle OUTPUT,
[@paramdef =] N'parameter_name data_type, [,...n]'
[@stmt =] N'stmt',
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
@scrollopt


北京联动北方科技有限公司

[@ccopt


  北京联动北方科技有限公司

@p5=4098 转成16进制就是1002,对应的游标类型为Parameterized query + Dynamic cursor
@p5=4112 转成16进制就是1010,对应的游标类型为Parameterized query + Fast forward-only cursor


  问题的现象是,左边的游标类型下,该脚本执行时间远大于右边的游标类型。

    二、如何比较两个不同执行计划的优劣

    在继续以下内容之前,这里要介绍一些查看和比较语句执行计划的知识。通常情况下,我们从management studio中输出图形界面的执行计划进行直观的比较,查看每个表用的访问方式,使用index还是table scan,使用了哪个index,表和表之间使用的join 方式有什么不一样。但是如果是一个复杂的语句,在不同的数据库上使用了不同的执行计划,对于同样表的访问,使用了不同的index,如何比较哪种执行计划更加优化呢?比较整个语句的执行时间是一种方法,但是这个比较的结果并不准确。语句的执行时间很容易受到其他外在因素的影响:

    1. 不同机器上CPU,memory和disk的性能会影响执行时间。

    2. 测试的时候有没有其他人在使用同样的数据造成阻塞

    3. 其他人堆数据库的使用占用了系统资源

    以上这些原因都有可能影响的语句的执行时间,从而影响到我们对语句性能结果的比较。因此我们不能把语句的执行时间作为衡量语句性能的标准。

    这里介绍一种比较语句cost的方法。我们对于语句cost的衡量,主要是通过比对语句总的logical reads.

    我们可以通过在management studio里的query window 执行”set statistics io on” ,在当前窗口中对所有执行的语句输出信息:

set statistics io on
select * from dbo.test_TicketFact
set statistics io on


  执行语句两次,以消除physical reads和read-ahead reads的影响。

    输出的结果如下:

(320 row(s) affected)

Table 'test_TicketFact'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论