求解释两个简单SQL语句性能差异问题
--建立基本数据：
--///////////////////
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','助教', '电子工程系');
--///////////////////
--查询“张旭“教师任课的学生成绩。
--方案1
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='张旭'
--方案2
SELECT *
FROM SCORE SC
WHERE CNO IN (
SELECT CNO
FROM TEACHER TEA INNER JOIN COURSE CO
ON TEA.TNO = CO.TNO
WHERE TEA.TNAME='张旭'
)
--///////////////
--显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
--显示[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL ON
--///////////////
--方案1 磁盘活动信息
Table 'Worktable'. Scan count 0, logical reads 0, 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 1, 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 1, 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 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--方案2 磁盘活动信息
Table 'TEACHER'. Scan count 1, logical reads 11, 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 12, 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 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--方案1执行计划
|--Hash Match(Inner Join, HASH:([CO].[CNO])=([SC].[CNO]), RESIDUAL:([testDB1].[dbo].[COURSE].[CNO] as [CO].[CNO]=[testDB1].[dbo].[SCORE].[CNO] as [SC].[CNO]))
|--Hash Match(Inner Join, HASH:([TEA].[TNO])=([CO].[TNO]), RESIDUAL:([testDB1].[dbo].[TEACHER].[TNO] as [TEA].[TNO]=[testDB1].[dbo].[COURSE].[TNO] as [CO].[TNO]))
| |--Table Scan(OBJECT:([testDB1].[dbo].[TEACHER] AS [TEA]), WHERE:([testDB1].[dbo].[TEACHER].[TNAME] as [TEA].[TNAME]='张旭'))
| |--Table Scan(OBJECT:([testDB1].[dbo].[COURSE] AS [CO]))
|--Table Scan(OBJECT:([testDB1].[dbo].[SCORE] AS [SC]))
--方案2执行计划
|--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([SC].[SNO]=ANY([testDB1].[dbo].[SCORE].[SNO] as [SC].[SNO]), [SC].[CNO]=ANY([testDB1].[dbo].[SCORE].[CNO] as [SC].[CNO]), [SC].[DEGREE]=ANY([testDB1].[dbo].[SCORE].[DEGREE] as [SC].[DEGREE])))
|--Nested Loops(Inner Join, WHERE:([testDB1].[dbo].[COURSE].[TNO] as [CO].[TNO]=[testDB1].[dbo].[TEACHER].[TNO] as [TEA].[TNO]))
|--Nested Loops(Inner Join, WHERE:([testDB1].[dbo].[SCORE].[CNO] as [SC].[CNO]=[testDB1].[dbo].[COURSE].[CNO] as [CO].[CNO]))
| |--Sort(ORDER BY:([Bmk1000] ASC))
| | |--Table Scan(OBJECT:([testDB1].[dbo].[SCORE] AS [SC]))
| |--Table Scan(OBJECT:([testDB1].[dbo].[COURSE] AS [CO]))
|--Table Scan(OBJECT:([testDB1].[dbo].[TEACHER] AS [TEA]), WHERE:([testDB1].[dbo].[TEACHER].[TNAME] as [TEA].[TNAME]='张旭'))
--////////////////////////////////////////
问题来了~虽然有了执行计划，但是看不懂为什么方案2的效率会比方案1差这么多？求解释，最好结合执行计划说一下。谢谢各位。
[解决办法]
在测试中发现了有趣的事情：
Query Hint FAST number_rows 改变SQL Server 执行计划
http://www.cnblogs.com/nzperfect/archive/2012/12/28/2837286.html
