服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|

服务器之家 - 数据库 - Sql Server - sqlserver 高性能分页实现分析

sqlserver 高性能分页实现分析

2019-12-03 14:49MSSQL教程网 Sql Server

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; 

/// <summary> 
/// 扩展连接字符串 
/// </summary> 
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(""); 

/// <summary> 
/// 得到生成的HTML 
/// </summary> 
public string GetHtml 

get 

return InsertString.ToString(); 


/// <summary> 
/// 得到生成的分页HTML 
/// </summary> 
public string GetPageHtml 

get 

return PageString.ToString(); 


/// <summary> 
/// 设置或获取目前页数 
/// </summary> 
public int PageNum 

get 

return PrivatePageNum; 

set 

if (value >= 1) 

PrivatePageNum = value; 



/// <summary> 
/// 设置或获取最大分页数 
/// </summary> 
public int MaxPageSize 

get 

return PrivateMaxPageSize; 

set 

if (value >= 1) 

PrivateMaxPageSize = value; 



/// <summary> 
/// 设置或获取每次显示最大页数 
/// </summary> 
public int MaxPages 

get 

return PrivateMaxPages; 

set 

PrivateMaxPages = value; 


/// <summary> 
/// 设置或获取数据总数 
/// </summary> 
public int DateCount 

get 

return PrivateCount; 

set 

PrivateCount = value; 


/// <summary> 
/// 获取数据总页数 
/// </summary> 
public int AllPage 

get 

return PrivateAllPage; 


/// <summary> 
/// 初始化分页 
/// </summary> 
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); 

/// <summary> 
/// 生成Tag分类表格 
/// </summary> 
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; 

延伸 · 阅读

精彩推荐