SQLSERVER SQL性能优化技巧

SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 291; 

方法2 (次低效)

DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,…,…;
…
OPEN C1(291);
FETCH C1 INTO …,…,…;
…
CLOSE C1;
END; 

方法2 (高效)

SELECT A.EMP_NAME, A.SALARY, A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A, EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291; 

5.使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表
例如：

SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = '0020'
AND ENAME LIKE 'SMITH%'; 
SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = '0030'
AND ENAME LIKE 'SMITH%'; 

你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO, '0020', 'X', NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, '0030', 'X', NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, '0020', SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROM EMP
WHERE ENAME LIKE 'SMITH%'; 

‘X’表示任何一个字段
类似的，DECODE函数也可以运用于GROUP BY和ORDER BY子句中
6.用Where子句替换HAVING子句
避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作
如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销
例如：
低效

SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != 'SYDNEY'
AND REGION != 'PERTH' 
高效
SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != 'SYDNEY'
AND REGION != 'PERTH'
GROUP BY REGION 

7.减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询
例如：

低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604) 
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604) 								 
 2/5   首页 上一页 1 2 3 4 5 下一页 尾页