T-SQL Optimization Tips (4) : 游标

这里所说的游标当然是指Transaction-SQL游标。任何类型的游标都会降低SQL Server的性能，然而游标之所以会存在是因为在某些情境中还是会不可避免地用到它，我们应该做的是尽量避免游标。

如果需要在T-SQL中对记录集进行逐行操作，可以考虑使用以下方案代替游标：

• 临时表
• WHILE循环
• 派生表
• 关联子查询
• CASE
• 多重查询

上面列举的替代方案的性能也是不一样的，在某些时候某些方案甚至可能也会对性能产生不利影响，但是它们都比游标的性能要好。之所以列举具备不同性能的替代方案，是因为由于数据结构的不同，很可能并不是很容易就可以使用任意一种替代方案来代替游标，此时可以考虑其它的替代方案。

如果一定要在T-SQL中使用游标，那么至少应当对游标本身进行优化：

1）减少需要处理的记录集行数：如果游标处理的对象不是整个原始表中的所有记录，那么可以考虑先将数据子集放入到一个临时表中，使游标只处理临时表中的数据；记录集的列数也应当尽量减少，以只获取客户端需要的数据为标。记录集越小，占用的资源就会越少，性能就会越高。

2）如果从某个查询返回的结果集的行数较少，而同时又需要对该结果集进行逐行操作，此时不要使用服务器端游标，而考虑将整个返回的结果集发送到客户端，由客户端完成对每行的必要的操作并将更新过的行集返回给服务器；而如果数据量较大时，应当考虑使用服务器端键集游标代替客户端游标，性能会由于服务器端与客户端网络通信的减少得到提升。当然，应当在实际负载下尝试这两种不同的游标从而决定哪种游标的性能更好一些。

3）如果确实需要使用服务器端游标，应该尽量使用只进游标（FORWARD_ONLY）或是更优化的快速只进（FAST_FORWARD）游标。再退一步，如果无法使用这些选项，那么按照从快到慢顺序排列的应当使用的游标是：动态游标、静态游标和键集游标；

4）尽量避免使用静态游标和键集游标，它们需要在tempdb中创建临时表从而增加服务器资源开销；

5）游标使用tempdb数据库存储游标中的数据，应当将tempdb数据库定位到其自身的物理设备上以加快游标数据的读取从而提高游标的性能。

6）游标的使用可能导致并发性能降低，并可能导致不必要的锁定或阻塞。可能的话，尽量使用只读游标；如果希望进行更新操作，应当为游标指定OPTIMISTIC选项以避免，该游标不锁定行；避免使用带有SCROLL_LOCKS选项的游标，否则并发性能会降低。

 7）使用游标进行必要的操作之后，仅仅关闭（CLOSE）该游标是不够的，关闭之后还应当删除对该游标的引用（DEALLOCATE）。删除游标引用用来释放被游标占用的SQL Server资源，如果仅仅关闭游标的话，锁定被释放，但游标占用的SQL Server资源并未被释放。

 8）如果可能的话，应当尽快移动到结果集的最后一行以快速加载游标，这样可以释放在游标建立时的共享锁定，解除对SQL Server资源的占用。

9）如果需要在游标中进行JOIN操作，键集游标和静态游标一般情况下会比动态游标快，因此这种情况下应当尽量使用键集游标和静态游标。

10）如果事务中包含游标（应当尽量避免这种情况），应当确保需要被游标修改的行数不能太多，被修改的行会被锁定直到事务完成或被取消：要修改的行数越多，锁定的行数就越多，服务器上发生资源资源争夺的概率就越高，性能就越低。

11）本地（LOCAL）游标比全局(GLOBAL)游标具有更好的性能；但是，如果需要在一个批处理中多次使用同一个游标或者在多个存储过程中使用同一个游标，应当考虑使用全局游标，此时游标和包含在其中的数据是预先准备好的，从而可以节省一定的处理时间。

12）如果预期要处理的结果集比较大时，可以考虑使用异步填充游标：如果SQL Server 查询优化器估计键集驱动或静态游标中返回的行数将超过sp_configure cursor threshold 参数的值，服务器就会启动另一个线程来填充工作表。控制权立即返回到应用程序，应用程序开始提取游标中前面的行，而无须等到整个工作表填充完以后才开始执行首次提取。事实上，这并不会在速度上得到真正的提升，但是可以让终端用户得到更好的用户体验。

13）如果在使用游标时发现出错或者处理提前完成，应当在遍历整个行集之前尽早跳出循环。

DOS & DONTS——
5、避免使用游标；
[注意：为了引起注意，所有本系列总结的DOS & DONTS都是具有一定前提的，一般的前提是“除非必要”和“除非必要才不”。]