分布存储过程

颠倒分页目前主要流行两种方法：
方法A：
select * from Products where productid in ( select top 10 productid from ( select top 100 productid from Products order by productid ) as t order by t.productid desc )order by productid
优点：很好的利用了主键，是IT界公认的最佳分页方式之一。
缺点：分页的数据数据表必须具备主键。这也使得这种思路在通用性上稍差了一点。

 

方法B：
select * from ( select top 10 * from ( select top 100 * from Products order by productid ) as t order by t.productid desc) as t order by productid
优点：通用性比较好，几乎可以对任何数据进行分页。
缺点：如果表中记录条数在1W以上，且表字段比较多。读取后面几页数据时，几乎要把表中的数据读取出来完。这样的性能和速度是可想而知的。

有时候在做程序开发时，我们都想保持高性能又不失通用性。这里提供一种中间办法，可能代大家参考。
如果某一类信息有1000页以上，在大多用户有这样一个浏览习惯，前10页和最后10页是占总浏览量的95%以上（如果在翻页条上，没有直达中间页码的快捷功能，会更高）。也就是说，随着某一类信息的页码不断增多，浏览的页就主要集中在了前10页和最后10页。

假设某一类信息共分1000页，每页10条数据，共1W条数据。根据上述特点，我们做一个前500页和后500页的二计算。
前500页：仍然按照方法B计算。
最后500页：把数据从后往前读取。
例如：
读取第1000页的数据，只我们只读取最后10条数据，再做一次颠倒排序便可。
select  * from ( select top 10 * from Products order by productid desc) as t order by t.productid asc
读取第999页的数据，只我们只读取最后20条数据，再做一次颠倒排序，读取前10条数据便可。
select top 10  * from ( select top 20 * from Products order by productid desc) as t order by t.productid asc
以次类推.......
颠倒这样改进后，翻第499页和第500页成了速度最慢的页面了，然而这些比较慢中间页码又是少有人到达的。

为了更方便使用改进后颠倒分页思路，我已经把它写成了存储过程如下:

 

 

代码

CREATE PROCEDURE p_Pagination
(
@TableName        NVARCHAR( 500 ),         --
@ReturnFieldLists    NVARCHAR( 2000 ),         --
@SortFieldLists        NVARCHAR( 500 ),         --  (,)
@PageSize INT          =   10 ,             --
@PageIndex INT         =   1 ,             --
@WhereClause NVARCHAR( 800 )     =   ''          --  ( Where  )
)AS
BEGIN
-- 全局变量
DECLARE    @strSQL     NVARCHAR( 4000 ),     --
@strSQLcount     NVARCHAR( 2000 ),   
@InnerOrder    NVARCHAR( 500 ),   
@TotalPage    INT,
@TotalRecord    INT,
@YuShu        INT,
@i        INT
SET @WhereClause  =  RTRIM(LTRIM(@WhereClause));
SET @WhereClause  =  ISNULL(@WhereClause, '' );
SET @InnerOrder   =  @SortFieldLists

-- 颠倒排序字段计算变量
DECLARE    @Index        INT,
@DotIndex    INT,
@SortFieldTemp    NVARCHAR( 500 ),
@strTemp    NVARCHAR( 500 ),
@strOrder1    NVARCHAR( 500 ),
@strOrder2    NVARCHAR( 500 )
------------------------------------------------------------------------------------
-- 计算页码和总记录条数开始
BEGIN
IF @WhereClause <> ''
SET @strSQLcount  =   ' Select @c=COUNT(1) From  '   +  @TableName  +   '  Where  '   +  @WhereClause  +   ' ; ' ;
ELSE
SET @strSQLcount  =   ' Select @c=COUNT(1) From  '   +  @TableName  +   ' ; ' ;

EXECUTE sp_ExecuteSQL @stmt = @strSQLcount,@params  =  N ' @c as int OUTPUT ' ,@c = @TotalRecord  OUTPUT;
SET @YuShu = @TotalRecord  %  @PageSize;
SET @TotalPage = (CASE WHEN @YuShu > 0  THEN @TotalRecord / @PageSize + 1  ELSE @TotalRecord / @PageSize END)
END
SELECT @TotalPage TotalPage,@TotalRecord TotalRecord
-- 计算页码和总记录条数结束 --------------------------------------------------------------------------------------
-- 读取本页数据开始
BEGIN
IF (@PageIndex > @TotalPage) OR (@TotalRecord = 0 )  -- 超出最大页码或总记录条数为0
BEGIN
--------------------------------------------------------------------------------------        
SET @strSQL  =   ' Select TOP 1  ' +  @ReturnFieldLists  + '  From  '   +  @TableName  +   '  where 1<>1 '
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex  =   1 ) -- 第1页
BEGIN
--------------------------------------------------------------------------------------        
IF @WhereClause <> ''
SET @strSQL  =   ' Select TOP  '   +  STR(@PageSize)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Where  '   +  @WhereClause  +
'  Order By  '   +  @InnerOrder;
ELSE
SET @strSQL  =   ' Select TOP  '   +  STR(@PageSize)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Order By  ' +  @InnerOrder;
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex  =  @TotalPage) -- 最后一页
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1  =   ''
SET @strOrder2  =   ''
SET @i = 0
WHILE( 1   =   1 )
BEGIN
-- 防止死循环
SET @i =  @i + 1 ;
IF(@i > 50 )
BREAK;

SELECT @Index  =  CHARINDEX(  ' , ' , @SortFieldLists )

IF @Index  =   0

BEGIN
SET @SortFieldTemp  =  @SortFieldLists;
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp, @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp
END

ELSE

BEGIN
SET @SortFieldTemp  =  SUBSTRING(@SortFieldLists,  1 , @Index  -   1 )
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp,  @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp   
END

-- 组织顺排序字段
SET @strOrder1  =  @strOrder1  +   ' , '   +  @strTemp
-- 组织反排序字段
SELECT @DotIndex  =  CHARINDEX(  '  desc ' , @SortFieldTemp)
IF @DotIndex  >   0    
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@SortFieldTemp, '  desc ' , '  asc ' )
ELSE
BEGIN
SELECT @DotIndex  =  CHARINDEX(  '  asc ' ,@SortFieldTemp)
IF @DotIndex  >   0
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@SortFieldTemp, '  asc ' , '  desc ' )
ELSE
SET @strOrder2  =  @strOrder2  +   ' , '   +  @SortFieldTemp  + '  desc '
END
-- 取得下一循环的数据
IF @Index  =   0
BREAK;
ELSE
SELECT @SortFieldLists  =  SUBSTRING(@SortFieldLists,  @Index  +   1 , LEN(@SortFieldLists)  -  @Index  +   1 )

END
--
SELECT @strOrder1  =  SUBSTRING(@strOrder1,  2 , LEN(@strOrder1))
SELECT @strOrder2  =  SUBSTRING(@strOrder2,  2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 第后一页的SQL
IF @WhereClause <> ''
SET @strSQL  = ' select * from ( ' +
' Select TOP  '   +  STR(CASE WHEN (@YuShu > 0 ) THEN @YuShu ELSE  @PageSize END)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Where  '   +  @WhereClause  +
'  Order By  '   +  @strOrder2 +
' ) t  order by  ' + @strOrder1;
ELSE
SET @strSQL  = ' select * from ( ' +
' Select TOP  '   +  STR(CASE WHEN (@YuShu > 0 ) THEN @YuShu ELSE  @PageSize END)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Order By  '   +  @strOrder2 +
' ) t  order by  ' + @strOrder1;
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex  >  @TotalPage / 2 ) -- 二分后，后一半页面
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1  =   ''
SET @strOrder2  =   ''
SET @i = 0
WHILE( 1   =   1 )
BEGIN
-- 防止死循环
SET @i =  @i + 1 ;
IF(@i > 50 )
BREAK;
SELECT @Index  =  CHARINDEX(  ' , ' , @SortFieldLists )
IF @Index  =   0
BEGIN
SET @SortFieldTemp  =  @SortFieldLists;
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp, @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp
END
ELSE
BEGIN
SET @SortFieldTemp  =  SUBSTRING(@SortFieldLists,  1 , @Index  -   1 )
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp,  @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp   
END
-- 组织顺排序字段
SET @strOrder1  =  @strOrder1  +   ' , '   +  @strTemp
-- 组织反排序字段
SELECT @DotIndex  =  CHARINDEX(  '  desc ' , @SortFieldTemp)
IF @DotIndex  >   0    
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@SortFieldTemp, '  desc ' , '  asc ' )
ELSE
BEGIN
SELECT @DotIndex  =  CHARINDEX(  '  asc ' ,@SortFieldTemp)
IF @DotIndex  >   0
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@SortFieldTemp, '  asc ' , '  desc ' )
ELSE
SET @strOrder2  =  @strOrder2  +   ' , '   +  @SortFieldTemp  + '  desc '
END
-- 取得下一循环的数据
IF @Index  =   0
BREAK;
ELSE
SELECT @SortFieldLists  =  SUBSTRING(@SortFieldLists,  @Index  +   1 , LEN(@SortFieldLists)  -  @Index  +   1 )
END
--
SELECT @strOrder1  =  SUBSTRING(@strOrder1,  2 , LEN(@strOrder1))
SELECT @strOrder2  =  SUBSTRING(@strOrder2,  2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 二分后，后一半页面
IF @WhereClause <> ''
SET @strSQL  = ' select top  ' + STR(@PageSize) +
'  * from ( ' +
' Select TOP  '   +  STR(CASE WHEN (@YuShu > 0 ) THEN ((@TotalPage - @PageIndex) * @PageSize + @YuShu) ELSE (@TotalPage - @PageIndex + 1 ) * @PageSize END)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Where  '   +  @WhereClause  +
'  Order By  '   +  @strOrder2 +
' ) t  order by  ' + @strOrder1;
ELSE
SET @strSQL  = ' select * from ( ' +
' Select TOP  '   +  STR(CASE WHEN (@YuShu > 0 ) THEN ((@TotalPage - @PageIndex) * @PageSize + @YuShu) ELSE (@TotalPage - @PageIndex + 1 ) * @PageSize END)  +
'   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Order By  '   +  @strOrder2 +
' ) t  order by  ' + @strOrder1;
--------------------------------------------------------------------------------------
END
ELSE --- 二分后，前一半页面
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1  =   ''
SET @strOrder2  =   ''
SET @i = 0
WHILE( 1   =   1 )
BEGIN
-- 防止死循环
SET @i =  @i + 1 ;
IF(@i > 50 )
BREAK;
SELECT @Index  =  CHARINDEX(  ' , ' , @SortFieldLists )
IF @Index  =   0
BEGIN
SET @SortFieldTemp  =  @SortFieldLists;
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp, @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp
END
ELSE
BEGIN
SET @SortFieldTemp  =  SUBSTRING(@SortFieldLists,  1 , @Index  -   1 )
SELECT @DotIndex  =  CHARINDEX(  ' . ' , @SortFieldTemp )
IF @DotIndex  >   0
SET @strTemp  =   SUBSTRING(@SortFieldTemp,  @DotIndex  +   1 , LEN(@SortFieldTemp)  -  @DotIndex  +   1 )
ELSE
SET @strTemp  =   @SortFieldTemp       
END
-- 组织顺排序字段
SET @strOrder1  =  @strOrder1  +   ' , '   +  @strTemp
-- 组织反排序字段
SELECT @DotIndex  =  CHARINDEX(  '  desc ' , @strTemp)
IF @DotIndex  >   0    
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@strTemp, '  desc ' , '  asc ' )
ELSE
BEGIN
SELECT @DotIndex  =  CHARINDEX(  '  asc ' , @strTemp)
IF @DotIndex  >   0
SET @strOrder2  =  @strOrder2  +   ' , '   +  REPLACE(@strTemp, '  asc ' , '  desc ' )
ELSE
SET @strOrder2  =  @strOrder2  +   ' , '   +  @strTemp  + '  desc '
END
-- 取得下一循环的数据
IF @Index  =   0
BREAK;
ELSE
SELECT @SortFieldLists  =  SUBSTRING(@SortFieldLists,  @Index  +   1 , LEN(@SortFieldLists)  -  @Index  +   1 )
END
--
SELECT @strOrder1  =  SUBSTRING( @strOrder1,  2 , LEN(@strOrder1))
SELECT @strOrder2  =  SUBSTRING( @strOrder2,  2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 二分后，前一半页面
IF @WhereClause <> ''
SET @strSQL  = ' select * from (select top  ' +  STR(@PageSize)  +  
'  * from ( ' + ' Select TOP  '   +  STR(@PageIndex * @PageSize)  + '   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Where  '   +  @WhereClause  +
'  Order By  '   +  @InnerOrder +
' ) t order by  ' + @strOrder2 +
' ) t  order by  ' + @strOrder1;
ELSE
SET @strSQL  = ' select * from (select top  ' +  STR(@PageSize)  +  
'  * from ( ' + ' Select TOP  '   +  STR(@PageIndex * @PageSize)  + '   ' +  @ReturnFieldLists  +
'  From  '   +  @TableName  +
'  Order By  '   +  @InnerOrder +
' ) t order by  ' + @strOrder2 +
' ) t  order by  ' + @strOrder1;
--------------------------------------------------------------------------------------
END
END
-- print @strSQL;
-- print  ' xx ' ;
-- print @strSQLcount;       
-- SQL
EXECUTE sp_ExecuteSQL @strSQL;
-- 读取本页数据结束
END -----------------------------------------------------------------------------------
------------------------------------------------
-- 调用示例
/* p_Pagination 'NEWGXE_Order a, NEWGXE_Transaction b, NEWGXE_ProductTypeImage c',
    'b.PaypalAccount_nvarchar,count(a.OrderID_int) as OrderCount,sum(a.USDmoney_money) as USDmoney_money',
    'USDmoney_money DESC', 10, 1,
    'a.OrderID_int=b.OrderID_int and a.ProductType_char=c.ProductType_char and a.Approve_char=''Yes'' and b.PaymentStatus_char=''Completed'' and a.ShipStatus_char=''Fini'' and DateDiff(d,''2007-09-01'', b.PaymentTime_nvarchar) >= 0 and DateDiff(d, b.PaymentTime_nvarchar, ''2007-10-01'') >=0 group by b.PaypalAccount_nvarchar having sum(a.USDmoney_money) > 0' */

 

转自：http://www.czz8.com/SQL/1240.html

转载于:https://www.cnblogs.com/lgc19/archive/2010/09/14/1826178.html