首页 > 代码库 > sql分页性能测试结果
sql分页性能测试结果
--方案一:
declare @d datetimeset @d = getdate()select top 10 * from Info where ID not in (select top 9990 ID from Info order by ID) order by ID select [not in方法升序分页执行花费时间(毫秒)]=datediff(ms,@d,getdate()) --430ms
--方案二:
declare @s datetimeset @s = getdate()select top 10 * from info where ID >=(select max(ID) from (select top 9991 ID from info order by ID) as T) order by IDselect [Max方法升序分页执行花费时间(毫秒)]=datediff(ms,@s,getdate()) --13ms
--方案三:
declare @q datetimeset @q = getdate()select top 10 * from Info where ID <=(select min(ID) from (select top 9991 ID from Info order by ID desc) as T) order by ID descselect [Min方法降序分页执行花费时间(毫秒)]=datediff(ms,@q,getdate())--10ms
--方案四:
declare @r datetimeset @r = getdate()select top 10 * from ( select row_number()over( order by id desc) as rowNumber,* from info) Twhere T.rowNumber>0 select [row_number()方法降序分页执行花费时间(毫秒)]=datediff(ms,@r,getdate())--3ms
--方案五:
with infos as(select r.*, row_number()over(order by r.id desc) as r from [Info] r) select * from infos where r between 1 and 30;
方案五没有采取top的形式取数据,而是用了between,相同点都用了row_number()函数。根据平常的分页习惯,方案四、五效率最高,且性能相当.
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。