SQL提高速度注意事项

1、 只返回需要的数据
返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：
A、横向来看，不要写SELECT *的语句，而是选择你需要的字段。
B、 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。
C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。
D、对于聚合查询，可以用HAVING子句进一步限定返回的行。

2、尽量少做重复的工作
这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：
A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。
B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
D、合并对同一表同一条件的多次UPDATE，比如
UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’
这两个语句应该合并成以下一个语句
UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’BR>WHERE EMP_ID=’ VPA30890F’
E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。
F、不要写一些没有意义的查询，比如
SELECT * FROM EMPLOYEE WHERE 1=2

3、注意事务和锁
事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：
A、事务操作过程要尽量小，能拆分的事务要拆分开来。
B、事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
C、事务操作过程要按同一顺序访问对象。
D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
E、尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。
F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

4、注意临时表和表变量的用法
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：
A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。
B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。
D、其他情况下，应该控制临时表和表变量的使用。
E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。
F、关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。
G、注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。

5、子查询的用法
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。
子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。
如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。
关于相关子查询，应该注意：
A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：
SELECT PUB_NAME FROM PUBLISHERS
WHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS')
可以改写成：
SELECT A.PUB_NAME FROM PUBLISHERS A LEFT JOIN TITLES B
ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL

SELECT TITLE FROM TITLES
WHERE NOT EXISTS  (SELECT TITLE_ID FROM SALES  WHERE TITLE_ID = TITLES.TITLE_ID)
可以改写成：
SELECT TITLE FROM TITLES LEFT JOIN SALES
ON SALES.TITLE_ID = TITLES.TITLE_ID
WHERE SALES.TITLE_ID IS NULL
B、如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
   (SELECT PUB_ID
   FROM TITLES
   WHERE TYPE = 'BUSINESS')
可以改写成：
SELECT DISTINCT A.PUB_NAME
FROM PUBLISHERS A INNER JOIN TITLES B
ON        B.TYPE = 'BUSINESS' AND
          A.PUB_ID=B. PUB_ID

C、IN的相关子查询用EXISTS代替，比如
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
   (SELECT PUB_ID
   FROM TITLES
   WHERE TYPE = 'BUSINESS')
可以用下面语句代替：
SELECT PUB_NAME
FROM PUBLISHERS
WHERE EXISTS
   (SELECT 1
   FROM TITLES
   WHERE TYPE = 'BUSINESS' AND
   PUB_ID= PUBLISHERS.PUB_ID)
D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：
SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
应该改成：
SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
WHERE EMPLOYEE.EMP_ID IS NULL

SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0
应该改成：
SELECT JOB_DESC FROM JOBS
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

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

7、 尽量使用索引
建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。
为了使得优化器能高效使用索引，写语句的时候应该注意：
A、不要对索引字段进行运算，而要想办法做变换，比如
SELECT ID FROM T WHERE NUM/2=100
应改为:
SELECT ID FROM T WHERE NUM=100*2
SELECT ID FROM T WHERE NUM/2=NUM1
如果NUM有索引应改为:
SELECT ID FROM T WHERE NUM=NUM1*2
如果NUM1有索引则不应该改。
发现过这样的语句：
SELECT 年,月,金额 FROM 结余表
WHERE 100*年+月=2007*100+10
应该改为：
SELECT 年,月,金额 FROM 结余表
WHERE 年=2007 AND
      月=10
B、 不要对索引字段进行格式转换
日期字段的例子：
WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’
应该改为
WHERE日期字段〉=’2008-08-15’         AND   日期字段<’2008-08-16’
ISNULL转换的例子：
WHERE ISNULL(字段,’’)<>’’应改为:WHERE字段<>’’
WHERE ISNULL(字段,’’)=’’不应修改
WHERE ISNULL(字段,’F’) =’T’应改为: WHERE字段=’T’
WHERE ISNULL(字段,’F’)<>’T’不应修改
C、 不要对索引字段使用函数
WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'
应改为:
WHERE NAME LIKE 'ABC%'
日期查询的例子：
WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0应改为:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1‘
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0应改为:WHERE 日期 <'2005-11-30‘
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0应改为:WHERE 日期 <'2005-12-01‘
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0应改为:WHERE 日期>='2005-12-01‘
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0应改为:WHERE 日期>='2005-11-30‘
D、不要对索引字段进行多字段连接
比如：
WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’
应改为:
WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

8、 注意连接条件的写法
多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。
A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。
B、 连接条件尽量使用聚集索引
C、 注意ON部分条件和WHERE部分条件的区别

9、 其他需要注意的地方
经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：
A、程序员注意、关心各表的数据量。
B、编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。
C、每个SQL语句尽量简单
D、不要频繁更新有触发器的表的数据
E、注意数据库函数的限制以及其性能

10、学会分辩SQL语句的优劣
自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。
A、看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有油画的余地。也可以用语句
SET SHOWPLAN_ALL ON
要执行的语句
SET SHOWPLAN_ALL OFF
查看执行计划的文本详细信息。
B、用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及I/O数据，从而分析语句的效率。
C、可以用WINDOWS的系统性能检测器，关注CPU、I/O参数