数据量大的SQL分页查询优化

时间:2014-04-20 07:15:04   收藏:0   阅读:542
bubuko.com,布布扣
我们平常写分页sql,一般都会分两次查询,一次查询结果,一次查询总行数。下面这条sql做了一些优化,只需要查询一次。
代码如下:

declare @nGotoPage int,@nPageSize int; set @nGotoPage = 1; set @nPageSize = 100000; with T_Data(RowID,ID,CID,DisplayName,WriteDate,Body) as ( select Row_Number() over (order by ID) as RowID, ID,CID,DisplayName,WriteDate,Body from Test where CID=1 ),T_DataCount(nRecordCount) as ( select count(*) from T_Data ) select _RecordCount=t2.nRecordCount,_PageCount=Ceiling(t2.nRecordCount * 1.0 / @nPageSize), t1.* from T_Data as T1, T_DataCount as t2 where RowID > ((@nGotoPage - 1) * @nPageSize) and RowID <= (@nGotoPage * @nPageSize);
bubuko.com,布布扣

 

数据量大的SQL分页查询优化,布布扣,bubuko.com

原文:http://www.cnblogs.com/zar901007/p/3643598.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!