Query Hint FAST number_rows 改变SQL Server 执行计划

作者：nzperfect
本文地址：http://www.cnblogs.com/nzperfect/archive/2012/12/28/2837286.html

本文示例使用FASH nuber_rows改变SQL执行计划
准备测试数据：

USE TEMPDB
GO
--建立基本数据：
--///////////////////
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL, 
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL, 
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
GO
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL, 
CNAME VARCHAR(10) NOT NULL, 
TNO VARCHAR(10) NOT NULL)
GO
CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE NUMERIC(10, 1) NOT NULL) 
GO
CREATE TABLE TEACHER 
(TNO VARCHAR(3) NOT NULL, 
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL, 
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6), 
DEPART VARCHAR(10) NOT NULL)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,1975-10-02,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,1976-01-23,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,1976-02-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,1974-06-03,95031);
GO
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
GO
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
GO
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师',  '电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (825, '王萍','女','1972-05-05','助教',  '计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教',  '电子工程系');

加索引：

CREATE UNIQUE CLUSTERED INDEX IX_1 ON COURSE(CNO)
CREATE NONCLUSTERED INDEX IX_2 ON COURSE(TNO,CNO)

CREATE UNIQUE CLUSTERED INDEX IX_1 ON SCORE(SNO,CNO)
CREATE NONCLUSTERED INDEX IX_2 ON SCORE(CNO)


CREATE UNIQUE CLUSTERED INDEX IX_1 ON TEACHER(TNO)
CREATE NONCLUSTERED INDEX IX_2 ON TEACHER(TNAME,TNO)

 

查询语句写法一：

SELECT SC.*
FROM TEACHER TEA 
INNER JOIN COURSE CO ON TEA.TNO=CO.TNO 
INNER JOIN SCORE SC ON CO.CNO = SC.CNO
WHERE TEA.TNAME='张旭'

 

 查询语句写法二：

SELECT *
FROM SCORE SC
WHERE CNO IN ( 
    SELECT CNO
    FROM TEACHER TEA INNER JOIN COURSE CO
    ON TEA.TNO = CO.TNO
    WHERE TEA.TNAME='张旭'
) 

首先看下IO：

查询语句写法一：

Table 'SCORE'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'COURSE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEACHER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
查询语句写法二：

Table 'TEACHER'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'COURSE'. Scan count 0, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SCORE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

从上面的IO可以看到写法二IO比写法一多一些逻辑读。

 

再比较一下执行计划：

写法一：

写法二：

上面这个写法二生成的执行计划看似不是我们想要的，我们的写法是：

SELECT *
FROM SCORE SC
WHERE CNO IN ( 
    SELECT CNO
    FROM TEACHER TEA INNER JOIN COURSE CO
    ON TEA.TNO = CO.TNO
    WHERE TEA.TNAME='张旭'
) 

按一般我们是这么想的：

step 1 : 先执行：

    SELECT CNO
    FROM TEACHER TEA INNER JOIN COURSE CO
    ON TEA.TNO = CO.TNO
    WHERE TEA.TNAME='张旭'

step 2 : 然后再执行：

SELECT *
FROM SCORE SC
WHERE CNO IN
(
    STEP 1 CNO结果
)

但实际上，但SQL执行计划并未按我们想的生成我们想要的执行计划，所以我打算想办法改变它，使用一个query hint : FAST number_rows

SELECT *
FROM SCORE SC
WHERE CNO IN ( 
    SELECT CNO
    FROM TEACHER TEA INNER JOIN COURSE CO
    ON TEA.TNO = CO.TNO
    WHERE TEA.TNAME='张旭'
) 
OPTION(FAST 1)

看看它的IO：

Table 'SCORE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'COURSE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEACHER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

看看它的执行计划：

这基本就是我们想要的了。

关于FAST hint，联机文档是这么解释的：

FAST number_rows

指定对查询进行优化，以便快速检索第一个 number_rows.。该值是非负整数。在返回第一个 number_rows 后，查询继续执行并生成完整的结果集。

其实也就是说FAST提示会将join优化为nested loop join ，而不用hash join，这样就能够快速的返回前几行/前几十行，尤其针对一个结果集会返回大量数据，但我们只需要显示前10行，那么如果hash join就不如nested loop join快了，当然，如果你要全部返回并显示，那么nested loop join就可能会比hash join还要慢了。