/*
查询来自'Spain'的客户，对每个匹配的客户，执行在Orders.custid列上的索引执行一次查找操作
检查Orders表是否包含具有该客户的custid的订单
子查询中筛选列custid上的索引在这里非常有帮助，因为通过它可以直接访问Orders表中具有特定custid值的行
*/
SET STATISTICS TIME ON
SELECT custid,companyname
FROM Sales.Customers AS C
WHERE country=N'Spain'
AND custid IN (SELECT custid FROM Sales.Orders)
/*
采用Exists
*/
SELECT *
FROM Sales.Customers AS C
WHERE EXISTS
(
SELECT * FROM Sales.Orders WHERE custid=c.custid
)
AND country='Spain'
/*
这两个查询哪个效率会更高呢
他们逻辑上是相等的2个查询,优化器会为他们生成相同的计划
也许是测试的表数据太少,好吧我们来建2张大表测试一下
*/
--学生表
IF OBJECT_ID('dbo.Sudents') IS NOT NULL
DROP TABLE dbo.Sudents
CREATE TABLE dbo.Sudents
(
id INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(16) NULL
)
--学生成绩表
IF OBJECT_ID('dbo.Score') IS NOT NULL
DROP TABLE dbo.Score
CREATE TABLE dbo.Score
(
id INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT NULL,
Score INT NULL
)
--测试数据
DECLARE @_n INT=1000000
DECLARE @_i INT=1
WHILE @_i<@_n
BEGIN
INSERT INTO dbo.Sudents
SELECT 'Student'+CAST(@_i AS VARCHAR(10))
INSERT INTO dbo.Score
SELECT @@IDENTITY,CAST(CEILING(RAND()*100) AS INT)
SET @_i+=1;
END
--100W条数据确实太多,执行到20+W的时候暂停
--现在删除学号为201的成绩
INSERT INTO dbo.Score
DELETE dbo.Score WHERE StudentID=201
--现在来查询没有成绩的学生
SELECT *
FROM dbo.Sudents AS S
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.Score WHERE StudentID=S.ID
)
/*
SQL Server 执行时间:
CPU 时间 = 264 毫秒，占用时间 = 105 毫秒。
*/
SELECT *
FROM dbo.Sudents AS S
WHERE id NOT IN
(
SELECT StudentID FROM dbo.Score
)
/*
SQL Server 执行时间:
CPU 时间 = 297 毫秒，占用时间 = 115 毫秒。
*/
--现在看出Not Exists和Not in的区别了
--给Score表的StudentId加上非聚集索引
CREATE NONCLUSTERED INDEX idx_Score_StudentID ON dbo.Score(StudentID)
--执行时间
/*
SQL Server 执行时间:
CPU 时间 = 47 毫秒，占用时间 = 43 毫秒。
SQL Server 执行时间:
CPU 时间 = 47 毫秒，占用时间 = 46 毫秒。
*/
--加上索引执行时间都缩短了,但两者还是相差不大,那好吧在多删几条记录
DELETE dbo.Score WHERE id IN (202,203,400,600,700,560,951,452)
/*
SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 44 毫秒。
(9 行受影响)
SQL Server 执行时间:
CPU 时间 = 47 毫秒，占用时间 = 46 毫秒。
*/
--好吧,not exists和not in 在时间效率上还是有区别的
--现在测试一下exists和in 的区别
--清空Score表
TRUNCATE TABLE Score
DECLARE @_m INT=300000
DECLARE @_j INT=210000
WHILE @_j<@_m
BEGIN
INSERT INTO dbo.Score
SELECT @_j,CAST(CEILING(RAND()*100) AS INT)
SELECT @_j+=1
END
--现在查询存在成绩的学生
INSERT INTO Score
SELECT 1000,CAST(CEILING(RAND()*100) AS INT) UNION ALL
SELECT 500,CAST(CEILING(RAND()*100) AS INT) UNION ALL
SELECT 100,CAST(CEILING(RAND()*100) AS INT)
--IN的用法
SET STATISTICS IO OFF
SELECT * FROM Sudents S
WHERE id IN (SELECT StudentID FROM Score )
--Exists的用法
SELECT * FROM Sudents S
WHERE EXISTS
(
SELECT 1 FROM score WHERE StudentId =S.ID
)
/*虽然2者的执行计划是一样的,但时间上效果还是挺明显的
执行时间
SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 21 毫秒。
(3 行受影响)
SQL Server 执行时间:
CPU 时间 = 16 毫秒，占用时间 = 18 毫秒。
*/
--可能是上面加了索引的原因
--有人会考虑到主表和从表的大小关系，好吧，就当把子查询的表为从表
--现在主表用20+W条记录,从表有9W条记录
--现在在给从表增加到100W条记录
DECLARE @_l INT=1000000
DECLARE @_k INT=300000
WHILE @_k<@_l
BEGIN
INSERT INTO Score
SELECT @_k,CAST(CEILING(RAND()*100) AS INT)
SET @_k+=1
END
--好了 在测试上面的语句
/*
SQL Server 执行时间:
CPU 时间 = 16 毫秒，占用时间 = 21 毫秒。
(3 行受影响)
SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 19 毫秒。
*/
--还是Exists的效率还是存在一定效率
/*
为什么IN和Exists会有相同的执行计划呢?
如果考虑一下三值逻辑可能就会意识到他们的区别了,与Exists不同,当输入列表中包含NUll时,In实际上会产生一个UNKOWN的逻辑结果,
例如: a IN (b,c,NULL)的结果是UNKOWN，不过在筛选器中UNKOWN和False处理方式类似，使用IN谓词的查询结果与使用Exists谓词一样，
而却优化器知道这一点所以生成了相同的执行计划，另外NOT IN中包含NULL时，NOT IN 查询总会返回空集。因为 val IN(a,b,c,NULL)永远不会返回False
而是返回TRUE、所以val NOT IN (val1,val2.....,NULL)返回的只有NOTtrue 和not Unkown，这两个结果都不会是true
*/
