使用ROW_NUMBER() OVER查询很慢

[sakuram]

查询语句如下所示：
SELECT * FROM(SELECT ROW_NUMBER() OVER(order by dtTime desc) AS rownum,
dtTime ... FROM TbTable)
AS T
where (rownum BETWEEN 10000 AND 10014)

使用到ROW_NUMBER() OVER查询，因为要实现分页，
数据库有十几万数据，查询很慢，查询时间要7,8秒，有什么办法解决？？？？

[dawugui]

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),  -- 需要返回的列
@fldName varchar(255),      -- 排序的字段名
@PageSize   int ,          -- 页尺寸
@PageIndex  int ,           -- 页码
@strWhere  varchar(1500),  -- 查询条件(注意: 不要加where)
@Sort varchar(255)      --排序的方法

)
AS
declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

if @Sort = 'desc'
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
 
if @PageIndex = 1
begin
if @strWhere != ''   
  begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName 
+ ' where ' + @strWhere + ' ' + @strOrder
  end
else
  begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName 
+ ' '+ @strOrder
  end
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from 
(select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' 
+ @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder
 
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from 
(select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from '
 + @tblName + ' where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) 
and ' + @strWhere + ' ' + @strOrder
end
exec (@strSQL)
RETURN 

[ws_hgo]

那就用存储过程..

[ws_hgo]

create database Test
on primary ( name='Test_Data.mdf',
  filename='D:\我的资料\sql\备份\Test_Data.mdf'
)
log on
(
  name='Test_Data.ldf',
  filename='D:\我的资料\sql\备份\Test_Data.ldf'
)

if object_id('tb') is not null drop table tb
create table tb
(
  Col int
)
insert into tb select top 50 number from master..spt_values where type='P' and number>0

create proc SplitPage
(
  @TableName nvarchar(50),
  @PageSize int,--每页显示的数量
  @CurrentPage int,--当前第几页
  @PageCol nvarchar(50),--排序字段
  @OrderNo nvarchar(50)--排序方式(DESC,ASC)
)
as
/*
测试用的
declare @PageCol nvarchar(50)
declare @TableName nvarchar(50)
declare @OrderNo nvarchar(50)
declare @PageSize int
declare @CurrentPage int
set @PageCol='Col'
set @TableName='tb'
set @OrderNo='DESC'
set @PageSize=10
set @CurrentPage=4
*/
declare @sql nvarchar(1000)
set @sql=''
set @sql='
;with hgo as
(
  select *,row_number() over(
  order by '+@PageCol+' '+@OrderNo+') rank
  from '+@TableName+'
)'
set @sql=@sql+'select Col from hgo where rank between '+ltrim((@CurrentPage-1)*@PageSize+1)+' and '+ltrim(@CurrentPage*@PageSize)
--print @sql
exec (@sql)

exec SplitPage 'tb',10,1,'Col','DESC'

Col
-----------
50
49
48
47
46
45
44
43
42
41

(10 行受影响)

[nzperfect]

[Quote=引用楼主 sakuram 的回复:]
查询语句如下所示：
SELECT * FROM(SELECT ROW_NUMBER() OVER(order by dtTime desc) AS rownum,
dtTime ... FROM TbTable)
AS T
where (rownum BETWEEN 10000 AND 10014)

使用到ROW_NUMBER() OVER查询，因为要实现分页，
数据库有十几万……
[/Quote]

大概是你的dtTime没有索引的原因。

[sakuram]

有索引的，是从3个表中查询数据