日期:2014-05-18  浏览次数:20519 次

MSSQL中row_number()函数真能提高分页效率吗?
在sqlserver2005中,新增了隐藏列来帮忙对数据表中的行进行排序,在分页中也可以用到它,最近我摒弃mssql2000,尝试使用了2005中row_number()函数,但并没有发现它在分页中的优势,请大家指教,情况如下:
现在有一张做分页的表
create table PageIndex
(
 s_id int identity(001,1) primary key,
 title varchar(20),
 contentText varchar(20),
 DepName varchar(20)
)

用row_number()函数来做,假如每页5条,取第三页的数据是

with newtable as (select (ROW_NUMBER() OVER(order by s_id))as idx,* from PageIndex)
select * from newtable where idx between 10 and 15
这里的newtable 相当于一个临时的集果结,用创建的临时的结果集对表中的每行先排序再设置其RowNumber数据,然后通过select查询,通过where设置查询条件来到达分页的目的。如果表中的数据越多(比如10万条),结果集就越大,分页就越慢。那我想办法改小这个结果集的范围,改成下面这样

with newtable as (select top 15 (ROW_NUMBER() OVER(order by s_id))as idx,* from PageIndex)
select * from newtable where idx between 10 and 15
觉得这里产生的newtable还是比较大,继续改成

with newtable as (select top 15 (ROW_NUMBER() OVER(order by s_id))as idx ,s_id from PageIndex)
select * from PageIndex where s_id in (select top 5 s_id from newtable order by s_id desc) 
这里的最后一句运用了in关键字,in后面结的是结果集,但是这个结果集始终只有5个s_id,很小,不会影响性能,而newtable
也尽量做到了最小,但是这还不如用:
select top 5 * from PageIndex where s_id>(select top 1 s_id from(select top 10 s_id from PageIndex order by s_id ) tt order by s_id desc)
这样来的快,还用的着ROW_NUMBER()函数吗?微软新加了这个函数只是帮助人们在表中索引有重复字段时就用它,如果表中设了有“不重复的索引”可用,ROW_NUMBER()函数完全可以不用?它并没有带来性能上的革命?

------解决方案--------------------
那个方法是通用方法,对于没有标识或主键列的时候的通用方法,但是效率是不一定高
效率我觉得还是基于聚集索引(主键)的数字型int应该是最快的
------解决方案--------------------
提供三个,自己选择.
SQL code
http://topic.csdn.net/u/20100203/17/8F916471-597D-481A-B170-83BCEFE3B199.html

应一个朋友的要求,贴上收藏的SQL常用分页的办法~~ 

表中主键必须为标识列,[ID] int IDENTITY (1,1) 

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

语句形式:  
SELECT TOP 页记录数量 * 
FROM 表名 
WHERE (ID NOT IN 
  (SELECT TOP (每页行数*(页数-1)) ID 
  FROM 表名 
  ORDER BY ID)) 
  ORDER BY ID 
//自己还可以加上一些查询条件 


例: 
select top 2 * 
from Sys_Material_Type 
where (MT_ID not in 
    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID)) 
order by MT_ID 



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

语句形式: 
SELECT TOP 每页记录数量 * 
FROM 表名 
WHERE (ID > 
          (SELECT MAX(id) 
    FROM (SELECT TOP 每页行数*页数 id  FROM 表 
          ORDER BY id) AS T) 
      ) 
ORDER BY ID 

例: 
SELECT TOP 2 * 
FROM Sys_Material_Type 
WHERE (MT_ID > 
          (SELECT MAX(MT_ID) 
          FROM (SELECT TOP (2*(3-1)) MT_ID 
                FROM Sys_Material_Type 
                ORDER BY MT_ID) AS T)) 
ORDER BY MT_ID 



3.分页方案三:(利用SQL的游标存储过程分页) 
create  procedure SqlPager 
@sqlstr nvarchar(4000), --查询字符串 
@currentpage int, --第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P1 int, --P1是游标的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 



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

通过SQL 查询分析器,显示比较:我的结论是: 
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句 
分页方案一:(利用Not In和SELECT TOP分页)  效率次之,需要拼接SQL语句 
分页方案三:(利用SQL的游标存储过程分页)    效率最差,但是最为通用

------解决方案--------------------
这个也许和楼主有关,自己试试.
SQL code
SQL 2005的ROW_NUMBER()实现分页功能

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, 
        newsid, topic, ntime, hits
      FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC 

aspx里面只需给SQL传入pageid和条数即可。 

CSDN上还有个存储过程实现分页的代码: 


ALTER PROCEDURE news_Showlist
(
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000),  -