ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 游标脚本性能问题解决与分析 (4) - Cursor Performance Analysis

游标脚本性能问题解决与分析 (4) - Cursor Performance Analysis

原创 Linux操作系统 作者:edwardking888 时间:2011-08-25 13:39:09 0 删除 编辑

第四部分、游标相关知识讲解

(一)Fast Forward Only(快速只进)游标分析

Microsoft SQL Server 实现了一种称作fast forward only游标的性能优化http://msdn.microsoft.com/zh-cn/library/ms187502.aspx

当遇到下面情形时,Fast Forward-only游标会隐式转换为其他类型

1.         SELECT语句连接包含trigger tableINSERTED/DELETED)的一个或多个表,游标被转换成static类型。

2.         SELECT语句查阅textntextimage列,如果SQL Server OLE DB访问接口或使用了SQL Server ODBC驱动器,游标被转换成dynamic类型。

3.         Fast Forward-only游标不是只读的,它会被转换成dynamic游标。动态游标是可以用来update当前滚动到的数据行并将更新回写到table中的。

4.         SELECT语句是一个查阅了链接服务器中一个或多个远程表进行分部查询,游标被转换成keyset-driven类型。

5.         如果SELECT语句查阅textntextimage列以及TOP语句,游标被转换成keyset-driven类型。

 

(二)几种游标的使用规则:

另外,我们这里列出了几条游标使用规则可供参考:

1.         如果应用程序中,只能使用服务器端游标(所有在SQL Server上定义的游标都是服务器端游标,如果应用程序在客户端使用游标,在SQL Server端是不会看到有游标打开的),尽量选择使用FORWARD-ONLYFAST-FORWARDREAD-ONLY游标。当处理单向只读数据时,使用FAST_FORWARD选项而不是FORWARD_ONLY,因为它可以提供一些内部的性能优化。这种类型的所游标产生的SQL Server整体开销是最少的。如果您无法使用FAST_FORWARD游标,可以按序尝试使用下面的游标,找到适合您需求的游标。按照他们的性能特征列出,从最快到最慢为:dynamicstatickeyset

2.         除非没有其他选择,避免使用static/insensitivekeyset游标,这是因为他们会在TEMPDB中创建临时表,这会增加整体开销导致内存抢夺问题。

3.         使用游标会减少并行而导致不必要的锁和阻塞的发生。要避免这种情况,可以适当地使用READ_ONLY游标选项;或者在你需要进行更新时,使用OPTIMISTIC游标选项来减少锁的产生。避免使用SCROLL_LOCKS游标选项,因为它会减少并行。

4.         从应用程序的角度尽快的加载并且滚动游标到最后一行。这会释放在创建游标时随之创建的共享锁,从而释放SQL Server资源。

5.         如果你的应用程序需要手动滚动记录并进行更新而必须使用游标,请避免使用客户端游标,除非返回的行数很少或数据是静态的。如果行数很大,或者数据不是静态的,可以考虑使用服务器端的keyset游标。由于客户端和服务器端网络拥挤的减少,性能问题很可能会出现。为了优化性能,可能需要在实际情况下对两种游标类型都进行尝试,决定哪种更适合需要应用的系统。

6.         如果游标需要执行JOIN操作,keysetstatic游标通常比dynamic游标快。

 

(三)深入了解Fast_forward游标

1.         既然已经有了read_only forward_only游标,fast_forward游标岂不是是多余的?为什么还需要它们?

fast_forward的确是多余的。read_only forward_only游标确实适用于很多应用,但是在有些应用的查询计划中却不理想。Read_only forward_only游标是动态游标,动态游标一般使用的是动态计划。问题在于,有些情况下即使最好的动态计划也远不如静态计划。因此我们引入了Fast_forward游标来使用了一种更平衡的方式,当静态计划更适合时它会选择静态计划。

2.         什么时候用fast_forward,什么时候用read_only forward_only

综合来说,fast_forward游标更好一点。但是,在做最后决定之前应该先对您的应用进行性能测试。这是因为,使用动态或者静态计划的决定方式是完全不同的(看下面的解释)。不论使用哪种计划方式或哪种游标模式,索引调优(index tuning)或计划提示(plan hint)都会是游标优化的一个重要部分。

3.         什么是动态计划?

动态计划可以增量地进行,在SQL Server中,我们通过将查询执行状态序列化到maker中来实现。然后,我们可以构建一个新的查询计划树,使用刚刚的maker来复位每个操作。另外,动态计划可以根据当前位置前后移动。dynamic和一些fast_forward游标都会使用动态计划。

动态计划只包括动态运算符(支持maker及前后的移动),这很像流操作符中进行查询的notionstop-and-go)。但并不是每个流操作符都是动态的。

SQL Server中,动态意味着:

1       操作符可以使用maker复位到它当前的位置,或者到当前位置的相关位置(下一个或前一个)

2       操作符状态一定要小,从而使maker比较小。操作符中不能存储行数据,尤其是sort tablehash table或者work table,甚至一行都不可以,因为即使单行也可能很大。

没有动态计划,游标可能需要临时存储媒介来保存查询结果集(或它的keyset)。然而,某些操作符对动态执行计划来说是不合适的,比如hash joinhash aggcompute sequencesort。这会导致一个次优的计划。

4.         什么时候动态计划会不如静态计划?

在某些查询中,比如使用row_number的,动态计划是不可用的。但是当动态静态都可用时就有问题出现了:在某个操作符(比如join)既有动态(nested loops)也有非动态(hash)实现时可能发生;当有些索引支持排序而有些不支持时,也可能会发生问题。

下面是一个例子,表ORDERS有一个DATE上的索引,以及一个SUBTOTAL上的索引(在这个例子中,clustered或者non-clustered没关系)。该查询想要查询订单信息:

SELECT DATE, SUBTOTAL, ORDERID, CUSTOMERID

FROM ORDERS where SUBTOTAL > 10000000

ORDER BY DATE

表中有一亿条数据,查询结果包含100条。动态游标不能排序,所以它必须使用DATE索引,查看每条数据,填到SUBTOTAL中。而静态游标可以查询大于10000范围的SUBTOTAL索引,排序并把它们存储到游标(temp table)中。

5.         fast_forward游标如何解决该问题?

在特定条件下,fast_forward游标从最佳静态计划和最佳动态计划中选择开销最小的一个。在上面所提到的极端例子中,它就会使用静态计划。

6.         应用开发者可以做什么?

1       OPTION(FAST )选择一个中性值。

2       通过调用sp_cursorprepareOPTION(RECOMPILE)避免在不同参数下的计划重用。

3       避免在游标中使用ORDER BY

4       使用相等谓词及多列索引来支持序列。稍微修改一下上面的例子,如果我们在SUBTOTAL范围有一个包含值{S,M,L,XL}的计算列SIZE,我们可以查询WHERE SIZE=’XL’ ORDER BY DATE,并在上使用索引。通常通过增加索引列来包括相等谓词后的排序列。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8183550/viewspace-705773/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1436713