--方案二执行:
declare @StrSql nvarchar(4000)
declare @CurrentPage INT
declare @PageSize INT
declare @Total INT
set @CurrentPage=1000
set @PageSize=10
set @StrSql='select top '+CONVERT(varchar,@CurrentPage*@PageSize)+' id,title from A_New order by id desc'
exec ExxPage_GetList2 @StrSql,@CurrentPage,@PageSize,@Total output
print @Total
--方案一执行:
declare @StartRows INT
declare @PageSize INT
declare @PrimaryColumn VARCHAR (1000)
declare @SortColumnDbType VARCHAR (100)
declare @SortColumn VARCHAR (1000)
declare @StrColumn VARCHAR (1000)
declare @Sorts VARCHAR (100)
declare @Filter VARCHAR (4000)
declare @TableName VARCHAR (1000)
declare @Total INT
----
set @StartRows=1000000
set @PageSize=10
set @PrimaryColumn='id'
set @SortColumnDbType='datetime'
set @SortColumn='addtime'
set @StrColumn='id,title,nodeid,userid,addtime'
set @Sorts='desc'
set @Filter='nodeid=1'
set @TableName='A_New'
exec ExxPage_GetList1 @StartRows,@PageSize,@PrimaryColumn,@SortColumnDbType,@SortColumn,@StrColumn,@Sorts,@Filter,@TableName,@Total output
*/
执行结果:
方案一:
(1)set @StartRows=0
set @PageSize=10
set @PrimaryColumn='id'
set @SortColumnDbType='int'
set @SortColumn='id'
set @StrColumn='id,title,nodeid,userid,addtime'
set @Sorts='desc'
set @Filter=''
set @TableName='A_New'
耗时:166ms
(2)set @StartRows=100000
其他同(1)
耗时:180ms
(3)set @StartRows=1000000
其他同(1)
耗时:290ms
(3)set @StartRows=10000000
其他同(1)
耗时:1400ms
(4)set @StartRows=100000
set @Filter='nodeid in(1,2)'
其他同(1)
耗时:100ms
(5)set @StartRows=1000000
set @Filter='nodeid in(1,2)'
其他同(1)
耗时:440ms
(6)set @StartRows=2000000
set @Filter='nodeid in(1,2)'
其他同(1)
耗时:820ms
(7)set @StartRows=2000000
set @SortColumnDbType='datetime'
set @SortColumn='addtime'
set @Filter='nodeid in(1,2)'
其他同(1)
耗时:1836ms
耗时:2770ms(addtime为非聚集索引)
(8)set @StartRows=2000000
set @SortColumnDbType='datetime'
set @SortColumn='addtime'
其他同(1)
耗时:5100~6153ms
耗时:1200ms(addtime为非聚集索引)
(9)set @StartRows=1000000
set @SortColumnDbType='datetime'
set @SortColumn='addtime'
set @Filter='nodeid=1'
其他同(1)
耗时:1100ms
耗时:2200ms(addtime为非聚集索引)
耗时:2700ms(addtime、nodeid为非聚集索引)
耗时:900ms(nodeid为非聚集索引)
方案二:
(1)set @CurrentPage=10000
set @PageSize=10
set @StrSql='select top '+CONVERT(varchar,@CurrentPage*@PageSize)+'id,title,nodeid,userid,addtime from A_New order by id desc'
exec ExxPage_GetList2@StrSql,@CurrentPage,@PageSize,@Totaloutput
耗时:140ms
(2)set @CurrentPage=100000
其他同(1)
耗时:1480ms
(3)set @CurrentPage=100000
set @StrSql='select top '+CONVERT(varchar,@CurrentPage*@PageSize)+'id,title,nodeid,userid,addtime from A_New where nodeid=1 order by addtime desc'
其他同(1)
耗时:1886ms
耗时:1900ms(nodeid为非聚集索引)