SQL点滴之性能优化其实没有那么神秘(1)(4)

6、慎用游标

数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。

游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源(特别是内存和锁资源)的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。

另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。

A、字符串连接的例子

这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：

 DECLARE @NAME VARCHAR(20) 
 DECLARE @NAME VARCHAR(1000) 
 DECLARE NAME_CURSOR CURSOR FOR 
 SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID 
 OPEN NAME_CURSOR 
 FETCH NEXT FROM RNAME_CURSOR INTO @NAME 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
   SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME 
   FETCH NEXT FROM NAME_CURSOR  INTO @NAME  
 END 
 CLOSE NAME_CURSOR 
 DEALLOCATE NAME_CURSOR 
下修改，功能相同： 
 
DECLARE @NAME VARCHAR(1000) 
SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME 
   FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID 

B、 用CASE WHEN 实现转换的例子

很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如:

表结构:

CREATE TABLE 料件表( 
料号           VARCHAR(30), 
名称           VARCHAR(100), 
主单位         VARCHAR(20), 
单位1         VARCHAR(20), 
单位1参数      NUMERIC(18,4), 
单位2         VARCHAR(20), 
单位2参数      NUMERIC(18,4) 
) 
 
GO 
 
CREATE TABLE 入库表( 
时间               DATETIME, 
料号               VARCHAR(30), 
单位               INT, 
入库数量           NUMERIC(18,4), 
损坏数量           NUMERIC(18,4) 
) 
 
GO 

其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：

DECLARE @料号     VARCHAR(30), 
        @单位   INT, 
        @参数      NUMERIC(18,4), 
 
DECLARE CUR CURSOR FOR 
        SELECT 料号,单位 FROM 入库表 WHERE 单位 <>0 
OPEN CUR 
FETCH NEXT FROM CUR INTO @料号,@单位 
WHILE @@FETCH_STATUS<>-1 
BEGIN 
  IF @单位=1 
  BEGIN 
    SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
    UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
  END 
  IF @单位=2 
  BEGIN 
    SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) 
    UPDATE 入库表 SET 数量=数量*@参数,损坏数量=损坏数量*@参数,单位=1 WHERE CURRENT OF CUR 
  END 
  FETCH NEXT FROM CUR INTO @料号,@单位 
END 
CLOSE CUR 
DEALLOCATE CUR 
                            可以改写成： 
 
UPDATE A SET  
数量=CASE A.单位 WHEN 1 THEN      A.数量*B. 单位1参数 
                   WHEN 2 THEN         A.数量*B. 单位2参数 
                   ELSE A.数量 
END,                   
损坏数量= CASE A.单位 WHEN 1 THEN    A. 损坏数量*B. 单位1参数 
                   WHEN 2 THEN         A. 损坏数量*B. 单位2参数 
                   ELSE A. 损坏数量 
END, 
单位=1  
FROM入库表 A, 料件表 B 
WHERE    A.单位<>1      AND   A.料号=B.料号 

C、 变量参与的UPDATE语句的例子

SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：

SELECT A,B,C,CAST(NULL AS INT) AS 序号 
INTO #T 
FROM 表 
ORDER BY A ,NEWID() 

产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：

DECLARE @A INT 
DECLARE @序号 INT 
UPDATE #T SET 
   @序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END, 
   @A=A, 
   序号=@序号 

D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标(选项FAST_FORWARD)，一般只需要静态游标(选项STATIC)。

E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。