10.29
前一段时间研究关于分页的问题,由于数据库属于百万级的,考虑了关于优化方面的问题。其中一个考虑是:第一页展现的频率肯定是最高的,所以我想第一页就使用Top N来读取。
这个想法本身是没有错,因为通常我读取某条件下的N条记录我一直都是使用Top N。
后面拿Top N和分页读取第一条进行效率比较,发现分页的效率居然还高一些,以下是测试代码:
USE [d_study]; GO SET STATISTICS IO ON; SET NOCOUNT ON; GO DECLARE @BeginTime datetime; DECLARE @EndTime datetime; DECLARE @ExecTime int; DECLARE @ExecNum int; SET @ExecNum = 1; SET @ExecTime = 0; -- 测试Top读取第一页的执行时间
WHILE @ExecNum <= 30 BEGIN SET @BeginTime = getdate(); SELECT TOP 30 * FROM users WHERE nID>2000 And nID<50000 ORDER BY nID DESC; SET @EndTime = getdate(); SET @ExecTime = @ExecTime + datediff(ms,@BeginTime,@EndTime); SET @ExecNum = @ExecNum + 1; CHECKPOINT; /*写脏的缓冲入磁盘*/ DBCC FREEPROCCACHE WITH NO_INFOMSGS; /*清除执行计划*/ DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; /*清除缓冲数据*/ END PRINT 'TOP平均执行速度:' + Cast((@ExecTime / 30) AS varchar(10)) + '毫秒'; --测试分页读取第一页的执行时间 SET @ExecNum = 1; --重置执行次数 SET @ExecTime = 0; --重置记录时间 WHILE @ExecNum <= 30 BEGIN Set @BeginTime = getdate(); SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY nID asc) AS rownum,* FROM users Where nID>2000 And nID<50000 ) AS D WHERE rownum>0 AND rownum<31; Set @EndTime = getdate(); SET @ExecTime = @ExecTime + datediff(ms,@BeginTime,@EndTime); SET @ExecNum = @ExecNum + 1; CHECKPOINT; --写脏的缓冲入磁盘 DBCC FREEPROCCACHE WITH NO_INFOMSGS; --清除执行计划 DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; --清除缓冲数据 END Print '分页类似于TOP效果:' + Cast((@ExecTime / 30) AS varchar(10)) + '毫秒'; GO SET NOCOUNT OFF SET STATISTICS IO OFF
修改读取的记录数N和修改读取条件的范围值,依然是分页效率更高。
10.30日
今天跟朋友讨论了下,换了一个写法:
USE [d_study]; GO --SET STATISTICS IO ON; SET NOCOUNT ON; GO DECLARE @BeginTime datetime; DECLARE @EndTime datetime; DECLARE @ExecTime int; DECLARE @ExecNum int; --测试Top的执行时间 SET @ExecNum = 1; SET @BeginTime = getdate(); WHILE @ExecNum <= 30 BEGIN SELECT TOP 30 * FROM users WHERE nID>2000 And nID<50000 ORDER BY nID DESC; SET @ExecNum = @ExecNum + 1; CHECKPOINT; /*写脏的缓冲入磁盘*/ DBCC FREEPROCCACHE WITH NO_INFOMSGS; /*清除执行计划*/ DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; /*清除缓冲数据*/ END SET @EndTime = getdate(); SET @ExecTime = datediff(ms,@BeginTime,@EndTime); PRINT 'TOP执行时间:' + Cast((@ExecTime) AS varchar(10)) + '毫秒'; --测试ROW_NUMBER执行时间 SET @ExecNum = 1; --重置执行次数 Set @BeginTime = getdate(); WHILE @ExecNum <= 30 BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY nID asc) AS rownum,* FROM users Where nID>2000 And nID<50000 ) AS D WHERE rownum > 0 AND rownum < 31; SET @ExecNum=@ExecNum + 1; CHECKPOINT; /*写脏的缓冲入磁盘*/ DBCC FREEPROCCACHE WITH NO_INFOMSGS; /*清除执行计划*/ DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; /*清除缓冲数据*/ END Set @EndTime = getdate(); SET @ExecTime = datediff(ms,@BeginTime,@EndTime); Print 'ROW_NUMBER执行时间:' + Cast((@ExecTime) AS varchar(10)) + '毫秒'; GO SET NOCOUNT OFF --SET STATISTICS IO OFF
发现这种写法TOP的效率是要高于ROW_NUMBER的,其中因由,有点想不明白。