高性能分页

发布时间：2020-12-12 15:03:17 所属栏目：MsSql教程来源：网络整理

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

高性能分页

??? 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;

（编辑：李大同）

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