sqlserver 高性能分页实现分析

先来说说实现方式：
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;