sqlserver 高效分页查询

Select * FROM (
???? select ROW_NUMBER()Over(order by cf_id desc) as rowId,* from T_Test
) as mytable
where rowId between 21 and 40


每页20条，第２页

ROW_NUMBER是SqlServer2005中的新特性

以下是利用本方法和传统利用Set RowCount方法存储过程的效率比较结果:

未建索引情况下:
500w数据: 存储过程1分20秒,新方法1分18秒
400w数据：存储过程47秒，新方法41秒
300w数据：存储过程35秒，新方法30秒
200w数据：存储过程25秒，新方法20秒
100W数据：存储过程12秒，新方法10秒
50W数据：存储过程 7秒，新方法 0秒

=============================================
-- Author:???????? <杨俊明>
-- Create date: <2006-11-05>
-- Description:???? <高效分页存储过程，仅适用于Sql2005>
-- Notes:???????? <排序字段强烈建议建索引>
-- =============================================
Alter Procedure [dbo].[up_Page2005]
@TableName varchar(50),???????? --表名
@Fields varchar(5000) = '*',???? --字段名(全部字段为*)
@OrderField varchar(5000),???????? --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int,???????????????????? --每页多少条记录
@pageIndex int = 1,???????????? --指定当前为第几页
@TotalPage int output???????????? --返回总页数
as
begin

???? Begin Tran --开始事务

???? Declare @sql nvarchar(4000);
???? Declare @totalRecord int;????

???? --计算总记录数
?????????
???? if (@SqlWhere='' or @sqlWhere=NULL)
???????? set @sql = 'select @totalRecord = count(*) from ' + @TableName
???? else
???????? set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere

???? EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数????????
????
???? --计算总页数
???? select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)

???? if (@SqlWhere='' or @sqlWhere=NULL)
???????? set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
???? else
???????? set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere????
????????
????
???? --处理页数超出范围情况
???? if @PageIndex<=0
???????? Set @pageIndex = 1
????
???? if @pageIndex>@TotalPage
???????? Set @pageIndex = @TotalPage

????? --处理开始点和结束点
???? Declare @StartRecord int
???? Declare @EndRecord int
????
???? set @StartRecord = (@pageIndex-1)*@PageSize + 1
???? set @EndRecord = @StartRecord + @pageSize - 1

???? --继续合成sql语句
???? set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +?? Convert(varchar(50),@EndRecord)
????
???? Exec(@Sql)
???? ---------------------------------------------------
???? If @@Error <> 0
?????? Begin
???????? RollBack Tran
???????? Return -1
?????? End
????? Else
?????? Begin
???????? Commit Tran
???????? Return @totalRecord ---返回记录总数
?????? End????
end

?

方案五

//对数据进行重新排序然后分页

USE [Mail]

GO

/****** Object:? StoredProcedure [dbo].[pro_MailReceiverPageList]??? Script Date: 08/24/2012 15:08:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ======================================================

-- Author:??? james.moriarty

-- Create date: 2012.05.22

-- Description: 邮件接收者数据分页过程

-- ======================================================

ALTER PROCEDURE [dbo].[pro_MailReceiverPageList]

? @mailLogID INT=0,

? @FIdx????? INT=1,

? @EIdx????? INT=20,

? @IsCount?? INT=1

AS

--with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能

? WITH TEMP

?????? AS (SELECT Row_number() OVER(ORDER BY receiverID DESC) rowID,

????????????? ? receiverID,

????????????????? receiverAddress

?????????? FROM?? [dbo].[MailReceiver]

?????????? WHERE? mailLogID = @mailLogID)

? SELECT *

? FROM?? TEMP

? WHERE? rowID BETWEEN @FIdx AND @EIdx;

?

? IF @IsCount = 0

??? RETURN 0;

?

? DECLARE @rowcount INT

?

? SELECT @rowcount = COUNT(*)

? FROM?? [MailReceiver]

? WHERE? mailLogID = @mailLogID

?

? PRINT @rowcount

?

? RETURN @rowcount

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!