mssql分页原理及效率分析

jopen 11年前

下面是常用的分页,及其分页效率分析。

1.分页方案一:(利用Not In和SELECT TOP分页)
语句形式:

  1. SELECT TOP 10 * 
  2. FROM TestTable 
  3. WHERE (ID NOT IN 
  4. (SELECT TOP 20 id 
  5. FROM TestTable 
  6. ORDER BY id)) 
  7. ORDER BY ID 
  1. SELECT TOP 页大小 * 
  2. FROM TestTable 
  3. WHERE (ID NOT IN 
  4. (SELECT TOP 页大小*页数 id 
  5. FROM 表 
  6. ORDER BY id)) 
  7. ORDER BY ID 

2.分页方案二:(利用ID大于多少和SELECT TOP分页)

语句形式:

  1. SELECT TOP 10 * 
  2. FROM TestTable 
  3. WHERE (ID > 
  4. (SELECT MAX(id) 
  5. FROM (SELECT TOP 20 id 
  6. FROM TestTable 
  7. ORDER BY id) AS T)) 
  8. ORDER BY ID 
  1. SELECT TOP 页大小 * 
  2. FROM TestTable 
  3. WHERE (ID > (SELECT MAX(id) 
  4. FROM (SELECT TOP 页大小*页数 id 
  5. FROM 表 
  6. ORDER BY id) AS T)) 
  7. ORDER BY ID  


3.分页方案三:(利用SQL的游标存储过程分页)

  1. createprocedure SqlPager 
  2. @sqlstr nvarchar(4000), --查询字符串 
  3. @currentpage int--第N页 
  4. @pagesize int --每页行数 
  5. as 
  6. set nocount on 
  7. declare @P1 int--P1是游标的id 
  8. @rowcount int 
  9. exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output 
  10. select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
  11. set @currentpage=(@currentpage-1)*@pagesize+1 
  12. exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
  13. exec sp_cursorclose @P1 
  14. set nocount off 

其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。

通过SQL 查询分析器,显示比较:我的结论是:

分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句

分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句

分页方案三:(利用SQL的游标存储过程分页)效率最差,但是最为通用