sqlserver高性能分页实现分析

SQLServer中有一个Set Rowcount的的设置，它的意思是使命令的处理在响应指定的行数之后停止处理命令，利用这个特点，我们可以借用它来在一个千万行级数据表中实现高性能分页查询。

先来说说实现方式：
1、我们来假定Table中有一个已经建立了索引的主键字段ID（整数型），我们将按照这个字段来取数据进行分页。
2、页的大小我们放在@PageSize中
3、当前页号我们放在@CurrentPage中
4、如何让记录指针快速滚动到我们要取的数据开头的那一行呢，这是关键所在！有了Set RowCount，我们就很容易实现了。
5、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行，然后我们把那一行的记录的ID字段的值记录下来，那么，利用Top和条件，我们就很容易的得到指定页的数据了。当然，有了Set RowCount，我们难道还用Top么？
看看Set Rowcount怎么来帮我们的忙吧：

代码如下:

Declare @ID int 
Declare @MoveRecords int 

--@CurrentPage和@PageSize是传入参数 
Set @MoveRecords=@CurrentPage * @PageSize+1 

--下面两行实现快速滚动到我们要取的数据的行，并把ID记录下来 
Set Rowcount @MoveRecords 
Select @ID=ID from Table1 Order by ID 

Set Rowcount @PageSize 
--最恨为了减少麻烦使用*了，但是在这里为了说明方便，暂时用一下 
Select * From Table1 Where ID>=@ID Order By ID 
Set Rowcount 0 
 
大家可以试试看，在一个1千W记录的表里面，一下子方翻页到第100页（每页100条），看看有多快！ 

因为平常 倒序 排的比较多，上面也很好改。 
将 Order by ID 改成 Order by ID DESC 
将 Where ID>=@ID Order By ID 改成 Where ID<=@ID Order By ID DESC 
就可以了. 
 代码如下:

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
GO 
Create PROCEDURE [dbo].[Tag_Page_Name_Select] 
-- 传入最大显示纪录数和当前页码 
@MaxPageSize int, 
@PageNum int, 
-- 设置一个
输出参数返回总纪录数供分页列表使用 
@Count int output 
AS 
BEGIN 
SET NOCOUNT ON; 

DECLARE 
-- 定义排序名称参数 
@Name nvarchar(50), 
-- 定义游标位置 
@Cursor int 
-- 首先得到纪录总数 
Select @Count = count(tag_Name) 
FROM [viewdatabase0716].[dbo].[view_tag]; 
-- 定义游标需要开始的位置 
Set @Cursor = @MaxPageSize*(@PageNum-1)+1 
-- 如果游标大于纪录总数将游标放到最后一页开始的位置 
IF @Cursor > @Count 
BEGIN 
-- 如果最后一页与最大每次纪录数相等，返回最后整页 
IF @Count % @MaxPageSize = 0 
BEGIN 
IF @Cursor > @MaxPageSize 
Set @Cursor = @Count - @MaxPageSize + 1 
ELSE 
Set @Cursor = 1 
END 
-- 否则返回最后一页剩下的纪录 
ELSE 
Set @Cursor = @Count - (@Count % @MaxPageSize) + 1 
END 
-- 将指针指到该页开始 
Set Rowcount @Cursor 
-- 得到纪录开始的位置 
Select @Name = tag_Name 
FROM [viewdatabase0716].[dbo].[view_tag] 
orDER BY tag_Name; 
-- 设置开始位置 
Set Rowcount @MaxPageSize 
-- 得到该页纪录 
Select * 
From [viewdatabase0716].[dbo].[view_tag] 
Where tag_Name >= @Name 
order By tag_Name 

Set Rowcount 0 
END 
 

分页控件 
 代码如下:

using System.Data; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using System.Text; 

/// 
 
/// 扩展连接字符串 
/// 
 
public class ExStringBuilder 
{ 
private StringBuilder InsertString; 
private StringBuilder PageString; 
private int PrivatePageNum = 1; 
private int PrivateMaxPageSize = 25; 
private int PrivateMaxPages = 10; 
private int PrivateCount; 
private int PrivateAllPage; 
public ExStringBuilder() 
{ 
InsertString = new StringBuilder(""); 
} 
/// 
 
/// 得到生成的HTML 
/// 
 
public string GetHtml 
{ 
get 
{ 
return InsertString.ToString(); 
} 
} 
/// 
 
/// 得到生成的分页HTML 
/// 
 
public string GetPageHtml 
{ 
get 
{ 
return PageString.ToString(); 
} 
} 
/// 
 
/// 设置或获取目前页数 
/// 
 
public int PageNum 
{ 
get 
{ 
return PrivatePageNum; 
} 
set 
{ 
if (value >= 1) 
{ 
PrivatePageNum = value; 
} 
} 
} 
/// 
 
/// 设置或获取最大分页数 
/// 
 
public int MaxPageSize 
{ 
get 
{ 
return PrivateMaxPageSize; 
} 
set 
{ 
if (value >= 1) 
{ 
PrivateMaxPageSize = value; 
} 
} 
} 
/// 
 
/// 设置或获取每次显示最大页数 
/// 
 
public int MaxPages 
{ 
get 
{ 
return PrivateMaxPages; 
} 
set 
{ 
PrivateMaxPages = value; 
} 
} 
/// 
 
/// 设置或获取数据总数 
/// 
 
public int DateCount 
{ 
get 
{ 
return PrivateCount; 
} 
set 
{ 
PrivateCount = value; 
} 
} 
/// 
 
/// 获取数据总页数 
/// 
 
public int AllPage 
{ 
get 
{ 
return PrivateAllPage; 
} 
} 
/// 
 
/// 初始化分页 
/// 
 
public void Pagination() 
{ 
PageString = new StringBuilder(""); 
//得到总页数 
PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / (decimal)PrivateMaxPageSize); 
//防止上标或下标越界 
if (PrivatePageNum > PrivateAllPage) 
{ 
PrivatePageNum = PrivateAllPage; 
} 
//滚动游标分页方式 
int LeftRange, RightRange, LeftStart, RightEnd; 
LeftRange = (PrivateMaxPages + 1) / 2-1; 
RightRange = (PrivateMaxPages + 1) / 2; 
if (PrivateMaxPages >= PrivateAllPage) 
{ 
LeftStart = 1; 
RightEnd = PrivateAllPage; 
} 
else 
{ 
if (PrivatePageNum <= LeftRange) 
{ 
LeftStart = 1; 
RightEnd = LeftStart + PrivateMaxPages - 1; 
} 
else if (PrivateAllPage - PrivatePageNum < RightRange) 
{ 
RightEnd = PrivateAllPage; 
LeftStart = RightEnd - PrivateMaxPages + 1; 
} 
else 
{ 
LeftStart = PrivatePageNum - LeftRange; 
RightEnd = PrivatePageNum + RightRange; 
} 
} 

//生成页码列表统计 
PageString.Append(...); 

StringBuilder PreviousString = new StringBuilder(""); 
//如果在第一页 
if (PrivatePageNum > 1) 
{ 
... 
} 
else 
{ 
... 
} 
//如果在第一组分页 
if (PrivatePageNum > PrivateMaxPages) 
{ 
... 
} 
else 
{ 
... 
} 
PageString.Append(PreviousString); 
//生成中间页 
for (int i = LeftStart; i <= RightEnd; i++) 
{ 
//为当前页时 
if (i == PrivatePageNum) 
{ 
... 
} 
else 
{ 
... 
} 
} 
StringBuilder LastString = new StringBuilder(""); 
//如果在最后一页 
if (PrivatePageNum < PrivateAllPage) 
{ 
... 
} 
else 
{ 
... 
} 
//如果在最后一组 
if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage) 
{ 
... 
} 
else 
{ 
... 
} 
PageString.Append(LastString); 
} 
/// 
 
/// 生成Tag分类表格 
/// 
 
public void TagTable(ExDataRow myExDataRow) 
{ 
InsertString.Append(...); 
} 

调用方法: 
//得到分页设置并放入Session 
ExRequest myExRequest = new ExRequest(); 
myExRequest.PageSession("Tag_", new string[] { "page", "size" }); 
//生成Tag分页 
ExStringBuilder Tag = new ExStringBuilder(); 
//设置每次显示多少条纪录 
Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]); 
//设置最多显示多少页码 
Tag.MaxPages = 9; 
//设置当前为第几页 
Tag.PageNum = Convert.ToInt32(Session["Tag_page"]); 
string[][] myNamenValue = new string[2][]{ 
new string[]{"MaxPageSize","PageNum","Count"}, 
new string[]{Tag.MaxPageSize.ToString(),Tag.PageNum.ToString()} 
}; 
//调用存储过程 
DataTable myDataTable = mySQL.BatchGetDB("Tag_Page_Name_Select", myNamenValue, "Count"); 
Tag.DateCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value; 
Tag.Pagination(); 

HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml; 

for (int i = 0, j = myDataTable.Rows.Count; i < j; i++) 
{ 
Tag.TagTable(new ExDataRow(myDataTable.Rows[i])); 
} 
TagBox.InnerHtml = Tag.GetHtml;