以下为从大数据量表检索分页数据的有效方法
测试时,先从LargeTable表选出1000条记录分页呈现
declare @pagesize int--每页大小
declare @currentpage int--当前页
set @pagesize=10
set @currentpage=1
SELECT TOP (@pagesize) * FROM (
SELECT top(1000) ROW_NUMBER() OVER (ORDER BY TIME_SEGMENT desc) AS id,* FROM LargeTable order by id
) as t
WHERE t.id > (@pagesize*(@currentpage-1)) order by t.id;
测试结果,百万级的没有索引的表,为5s左右.
欢迎大家继续改进!!!!!!!