PS:这东西是不能给SQL Server 2000用的..因为2000的是没有Row_Number滴..找了一堆资料,关于索引以及高效分页的东东.最终把这东西给整出来了. 电脑配置:CPU 1.7赛扬,内存256M.唉,穷啊..用这破电脑测试. 测试数据:500W条(其实如果真要高效,可以考虑用2005新增加的分区这个东东了) 说明:从前翻和从后翻速度基本相当,只是翻页到中间时,速度会慢点.使用SQL Server 2005新增的Row_Number功能,在1到TotalRecord/2范围内翻页时,Row_Number Over(order by id asc),RowLowerBound和RowUpperBound不用修改,在TotalRecord/2到TotalRecord范围内翻页时,使用Row_Number Over(order by id asc),重新调整RowLowerBound和RowUpperBound,因为返回记录是倒序的,必须重新计算..
闲话不多说,有用SQL Server 2005的可以来评下,Row_Number在Oracle中也有,所以学Oracle的也可以帮俺PP.存储过程改下也可以用于Oracle.存储过程代码如下:
代码:
USE [TestDB] GO /****** 对象: StoredProcedure [dbo].[sp_NetFog_Page] 脚本日期: 09/05/2006 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
-- ============================================= -- Author: <NetFog,邱志> -- Create date: <2008,9,4> -- Description: <RowNumber分页存储过程> -- example: sp_NetFog_Page 0,90000,20,'ID','ID,AritcleTitle,Ftitle,otime,','TblTest','where id>0','order by id desc' -- ============================================= CREATE PROCEDURE [dbo].[sp_NetFog_Page] ( @nRecordCount BigInt OutPut, @nCurPage BigInt, @nPageSize BigInt, @nKey nvarchar(50), @nField nvarchar(1000), @nTable nvarchar(100), @nCondition nvarchar(1000), --类似where id>50,前后不要有空格,可以为空 @nOrder nvarchar(100) --类似Order by id desc,前后不要有空格 ) AS BEGIN SET NOCOUNT ON; Declare @nSQL nvarchar(4000) Declare @tmp Int Declare @RowOrder nvarchar(50) Declare @TotalPage BigInt Declare @RowLowerBound BigInt Declare @RowUpperBound BigInt If @nRecordCount < 1 Begin Set @nSQL='Select @nRecordCount=Count('+@nKey+') From '+@nTable+' '+@nCondition EXEC sp_executesql @nSQL,N'@nRecordCount int OUT',@nRecordCount OUT End Select @tmp = (@nRecordCount/@nPageSize)-Floor(@nRecordCount/@nPageSize) If @tmp <> 0 Select @TotalPage = Floor(@nRecordCount/@nPageSize)+1 Else Begin Select @TotalPage = Floor(@nRecordCount/@nPageSize) End If @nCurPage > @TotalPage Begin Select @nCurPage = @TotalPage End
If @nRecordCount <= 2*@nPageSize Select @RowOrder = 'asc' Else Begin If @nCurPage*@nPageSize<=floor(@nRecordCount/2) Select @RowOrder = 'asc' Else Begin Select @RowOrder = 'desc' End End
If @RowOrder = 'asc' If @nCurPage = 1 Begin Select @RowLowerBound = 1 If @nPageSize > @nRecordCount Select @RowUpperBound = @nRecordCount Else Begin Select @RowUpperBound = @nPageSize End End Else Begin Select @RowLowerBound = (@nCurPage-1)*@nPageSize+1 If @nCurPage < @TotalPage Select @RowUpperBound = @nCurPage*@nPageSize Else Begin Select @RowUpperBound = @nRecordCount End End Else Begin If @nCurPage = @TotalPage Begin Select @RowLowerBound = 1 If @tmp = 0 Select @RowUpperBound = @nPageSize Else Begin Select @RowUpperBound = @nRecordCount-((@nCurPage-1)*@nPageSize) End End Else Begin Select @RowLowerBound = @nRecordCount-@nCurPage*@nPageSize+1 Select @RowUpperBound = @nRecordCount-(@nCurPage-1)*@nPageSize End End Set @nSQL = 'select * from (select '+@nField+' ROW_NUMBER() OVER(order by '+@nKey+' '+@RowOrder+') as row from '+@nTable+' '+@nCondition+') a where row between '+Cast(@RowLowerBound AS nvarchar)+' and '+Cast(@RowUpperBound AS nvarchar)+' '+@nOrder --Print(@RowLowerBound) --Print(@RowUpperBound) --Print(@RowOrder) --Print(@nRecordCount) --Print(@nSQL) EXEC(@nSQL) END
|