优化游标性能

原文 http://dreams75.iteye.com/blog/474864

 

最好的改进光标性能的技术就是：能避免时就避免使用游标。



——摘自《Transact-SQL权威指南》 Ken Henderson[著]

    最好的改进光标性能的技术就是：能避免时就避免使用游标。SQL Server是关系数据库，其处理数据集比处理单行好得多，单独行的访问根本不适合关系DBMS。若有时无法避免使用游标，则可以用如下技巧来优化游标的性能。
(1). 除非必要否则不要使用static/insensitive游标。打开static游标会造成所有的行都被拷贝到临时表。这正是为什么它对变化不敏感的原 因——它实际上是指向临时数据库表中的一个备份。很自然，结果集越大，声明其上的static游标就会引起越多的临时数据库的资源争夺问题。
(2). 除非必要否则不要使用keyset游标。和static游标一样，打开keyset游标会创建临时表。虽然这个表只包括基本表的一个关键字列(除非不存在唯一关键字)，但是当处理大结果集时还是会相当大的。
(3). 当处理单向的只读结果集时，使用fast_forward代替forward_only。使用fast_forward定义一个forward_only，则read_only游标具有一定的内部性能优化。
(4). 使用read_only关键字定义只读游标。这样可以防止意外的修改，并且让服务器了解游标移动时不会修改行。
(5). 小心事务处理中通过游标进行的大量行修改。根据事务隔离级别，这些行在事务完成或回滚前会保持锁定，这可能造成服务器上的资源争夺。
(6). 小心动态光标的修改，尤其是建在非唯一聚集索引键的表上的游标，因为他们会造成“Halloween”问题——对同一行或同一行的重复的错误的修改。因为 SQL Server在内部会把某行的关键字修改成一个已经存在的值，并强迫服务器追加下标，使它以后可以再结果集中移动。当从结果集的剩余项中存取时，又会遇到 那一行，然后程序会重复，结果造成死循环。
(7). 对于大结果集要考虑使用异步游标，尽可能地把控制权交给调用者。当返回相当大的结果集到可移动的表格时，异步游标特别有用，因为它们允许应用程序几乎马上就可以显示行。
Halloween:

CREATE
 
Table
 #T(
    k1 int
 identity(1,1),
    c1 int
 
null
);

CREATE
 CLUSTERED 
INDEX
 C1 
ON
 #T(C1);

INSERT
 
INTO
 #T(C1) 
VALUES
(8)
INSERT
 
INTO
 #T(C1) 
VALUES
(6)
INSERT
 
INTO
 #T(C1) 
VALUES
(7)
INSERT
 
INTO
 #T(C1) 
VALUES
(5)
INSERT
 
INTO
 #T(C1) 
VALUES
(3)
INSERT
 
INTO
 #T(C1) 
VALUES
(0)
INSERT
 
INTO
 #T(C1) 
VALUES
(9)


DECLARE
 C 
CURSOR
 
DYNAMIC
    FOR
 
SELECT
 K1,C1 
FROM
 #T;

OPEN
 C
FETCH
 C
WHILE(@@FETCH_Status=0)
BEGIN
    UPDATE
 #T 
SET
 C1=C1+1
        WHERE
 
CURRENT
 
OF
 C;
    FETCH
 C;
END

CLOSE
 C;
DEALLOCATE
 C;

DROP
 
Table
 #T;
GO

CREATE Table #T(
    k1 int identity(1,1),
    c1 int null
);
 
CREATE CLUSTERED INDEX C1 ON #T(C1);
 
INSERT INTO #T(C1) VALUES(8)
INSERT INTO #T(C1) VALUES(6)
INSERT INTO #T(C1) VALUES(7)
INSERT INTO #T(C1) VALUES(5)
INSERT INTO #T(C1) VALUES(3)
INSERT INTO #T(C1) VALUES(0)
INSERT INTO #T(C1) VALUES(9)
 
 
DECLARE C CURSOR DYNAMIC
    FOR SELECT K1,C1 FROM #T;
 
OPEN C
FETCH C
WHILE(@@FETCH_Status=0)
BEGIN
    UPDATE #T SET C1=C1+1
        WHERE CURRENT OF C;
    FETCH C;
END
 
CLOSE C;
DEALLOCATE C;
 
DROP Table #T;
GO