关于分页查询的性能测试！Max\Min

来源：互联网发布：惠州温泉知乎编辑：程序博客网时间：2024/06/05 17:07

原文地址:http://www.cnblogs.com/New-world/archive/2012/11/28/2793488.html

declare @d datetime

set @d = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID not in (select top 9990 ID from Test1 order by ID) order by ID
select [not in方法升序分页执行花费时间(毫秒)]=datediff(ms,@d,getdate())

declare @s datetime
set @s = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID >=(select max(ID) from (select top 9991 ID from test1 order by ID) as T) order by ID
select [Max方法升序分页执行花费时间(毫秒)]=datediff(ms,@s,getdate())

declare @t datetime
set @t = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID not in (select top 9990 ID from Test1 order by ID desc) order by ID desc
select [not in方法降序分页执行花费时间(毫秒)]=datediff(ms,@t,getdate())

declare @q datetime
set @q = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID <=(select min(ID) from (select top 9991 ID from Test1 order by ID desc) as T) order by ID desc

select [Min方法降序分页执行花费时间(毫秒)]=datediff(ms,@q,getdate())

测试结果:

0 0