两种SQL语句的执行速度比较
关于读取表中最后一项数据(后续)
第一种
select * from tbwhere id=(select max(ID)from tb)
select top 1 * from tb order by id desc
select top 1000000 id=identity(int,1,1) into Qas from syscolumns a,syscolumns b,syscolumns c,syscolumns dGOselect top 1 * from Qas order by id desc/*执行时间 0秒*/select * from Qas where id=(select max(id) from Qas)/*执行时间 1秒*/GOdrop table Qas
[解决办法]
事实上,table的数据量不同,执行计划也不同,
你可以试下当Qas有1000笔和100000笔时的情况。
又要推荐一下,请看看leimin 大侠的 “一句SQL引发的思考”
执行计划有时候也会骗人的。
[解决办法]
建索引后,2者好像是一样的。
[解决办法]
Ⅰ
0)SELECT COUNT(ID) FROM ta
-----------
100015
Warning: Null value is eliminated by an aggregate or other SET operation.
1)加索引后两者执行计划近乎相同 一个是Index Scan 一个Index Seek,理论上Seek高于Scan
CREATE CLUSTERED INDEX tID ON ta (ID)
GO
--DROP INDEX ON ta.ID
--GO
--第一种SQL code
select *
from ta
where id=(select max(ID)from ta)
11select * from ta where id=(select max(ID)from ta)
11 |--Top(TOP EXPRESSION:((1)))
21 |--Clustered Index Seek(OBJECT:([Testshen].[dbo].[ta].[tID]), SEEK:([Testshen].[dbo].[ta].[ID] IsNotNull) ORDERED BACKWARD)
--DBCC DROPCLEANBUFFERS
--第二种SQL code
select top 1 * from ta order by id desc
11select top 1 * from ta order by id desc
11 |--Top(TOP EXPRESSION:((1)))
11 |--Clustered Index Scan(OBJECT:([Testshen].[dbo].[ta].[tID]), ORDERED BACKWARD)
Ⅱ
1)第一种SQL code
select *
from ta
where id=(select max(ID)from ta)
--这里2次Table Scan并且用到Nested Loops Join
select * from ta where id=(select max(ID)from ta)
11select * from ta where id=(select max(ID)from ta)
11 |--Nested Loops(Inner Join, WHERE:([Expr1008]=[Testshen].[dbo].[ta].[ID]))
11 |--Stream Aggregate(DEFINE:([Expr1008]=MAX([Testshen].[dbo].[ta].[ID])))
1000161 | |--Table Scan(OBJECT:([Testshen].[dbo].[ta]))
1000161 |--Table Scan(OBJECT:([Testshen].[dbo].[ta]))
--DBCC DROPCLEANBUFFERS
2)第一种SQL code
--这里1次表Table Scan和Sort
select top 1 * from ta order by id desc
11select top 1 * from ta order by id desc
11 |--Sort(TOP 1, ORDER BY:([Testshen].[dbo].[ta].[ID] DESC))
1000161 |--Table Scan(OBJECT:([Testshen].[dbo].[ta]))
select count(*) from dbo.TB_FB805616条数据select * from TB_FBwhere id=(select max(ID)from TB_FB)CPU 15 Reads 258select top 1 * from TB_FB order by id descCPU 15 Reads 5
[解决办法]
(☆_☆) 普通扑克透視眼镜
咨询QQ1273803415
[解决办法]
就这两句看来 加索引和不加 区别还是蛮大滴
[解决办法]
在有表索引的情况下 显然第二种方法快的多 如果没有 就第一种了
[解决办法]
2吧 我比较熟悉下面的
[解决办法]