ASP.NET存储过程自定义分页详解

by admin on 2020年1月25日

*/

*/ 出自: 编程中国
*/ 作者: hebingbing
*/ 时间: 2008-4-5 04:30 编程论坛首发
*/ 声明: 光看我这么晚了还在工作,转载这段文字应该保留吧……

CREATE proc getdataset
@TableList Varchar(200)=’*’,–搜索表的字段,比如:’id,datatime,job‘,用逗号隔开
@TableName Varchar(30), –搜索的表名
@SelectWhere Varchar(500)=”,–搜索条件,这里不用写where,比如:job=’teacher‘and class=’2′
@SelectOrderId Varchar(20),–表主键字段名。比如:id
@SelectOrder Varchar(200)=”, –排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
@intPageNo int=1, –页号
@intPageSize int=10 ,–每页显示数
@RecordCount int OUTPUT  –总记录数(存储过程输出参数)
as  
   
declare @TmpSelect      NVarchar(600)  
declare @Tmp     NVarchar(600)  

set nocount on–关闭计数

set @TmpSelect = ‘select @RecordCount = count(*) from ‘+@TableName+’ ‘+@SelectWhere

execute sp_executesql
@TmpSelect,    –执行上面的sql语句
N’@RecordCount int OUTPUT’ ,   –执行输出数据的sql语句,output出总记录数
@RecordCount  OUTPUT

  if (@RecordCount = 0)    –如果没有贴子,则返回零
       return 0
      
   /*判断页数是否正确*/
  if (@intPageNo – 1) * @intPageSize > @RecordCount   –页号大于总页数,返回错误
     return (-1)
set nocount off–打开计数
if @SelectWhere != ”
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+’ ‘+@TableList+’ from ‘+@TableName+’ where ‘+@SelectOrderId+’ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from ‘+@TableName+’ ‘+@SelectWhere +’ ‘+@SelectOrder+’) and ‘+@SelectWhere +’ ‘+@SelectOrder
end
else
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+’ ‘+@TableList+’ from ‘+@TableName+’ where ‘+@SelectOrderId+’ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from ‘+@TableName+’ ‘+@SelectOrder+’) ‘+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)
GO

DECLARE @temp TABLE (
    PK  /* PK Type */ NOT NULL PRIMARY 
)

原帖及讨论:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="aa.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Height="180px" Width="867px">
            <Columns>
                <asp:BoundField DataField="job_id" HeaderText="job_id" />
                <asp:BoundField DataField="job_desc" HeaderText="job_desc" />
                <asp:BoundField DataField="max_lvl" HeaderText="max_lxl" />
            </Columns>
        </asp:GridView>
   
    </div>
           <asp:HyperLink ID="hylfirst" runat="server">首页</asp:HyperLink>
         
        <asp:HyperLink ID="hylprev" runat="server">上一页</asp:HyperLink>
         
        <asp:HyperLink ID="hylnext" runat="server">下一页</asp:HyperLink>
        <asp:HyperLink ID="hylend" runat="server">尾页</asp:HyperLink>
               第<asp:Label ID="lbRow" runat="server" Text="Label"></asp:Label>页,
          共<asp:Label ID="lbpage" runat="server" Text="Label"></asp:Label>页,共<asp:Label
            ID="lbRecord" runat="server" Text="Label"></asp:Label>条记录,转到<asp:TextBox ID="txtlink"
                runat="server" Width="29px"></asp:TextBox>
        页<asp:LinkButton ID="link" runat="server" OnClick="link_Click" TabIndex="1">转到</asp:LinkButton>
    </form>
</body>
</html>

虽然你需要订阅,但是可以下载一个包含子查询存储过程定义的zip文件。列表4
SELECT_WITH_PAGINGStoredProcedure.txt文件包含一个完整的通用的动态SQL。在本文中,我也用一个类似的包含所有其他存储过程的通用逻辑。这里的原理是连接到整个过程中,我对原始代码做了一些缩减,因为recordcount在我的测试中不需要)

*/

废话:清明节,同学回家的回家,旅游的旅游……我离家远是不可能回家了,旅游吧不感兴趣,觉得还不如看一场电影……呵呵,从小不喜欢旅游观光……
转入正题:大家都知道asp.net中的Gridview。datalist等都可以自定义分页,但是当你翻页的时候,数据表中的所有数据都会加载到内存,重新绑定,当然要是数据量小的话,这是可以的,我们也很乐意用,原因简单因为方便,但是要是数据量是999999999999……,在信息爆炸的这个时代海量数据是经常的时,那么这些控件自带的分页就显得有些……
解决这个问题办法就是自己动手……不多废话了,看代码:
1.首先我是用存储过程来解决的,要弄懂这个问题,首先要从存储过程下手,代码如下:

CREATE proc getdataset
@TableList Varchar(200)=’*’,–搜索表的字段,比如:’id,datatime,job‘,用逗号隔开
@TableName Varchar(30), –搜索的表名
@SelectWhere Varchar(500)=”,–搜索条件,这里不用写where,比如:job=’teacher‘and class=’2′
@SelectOrderId Varchar(20),–表主键字段名。比如:id
@SelectOrder Varchar(200)=”, –排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
@intPageNo int=1, –页号
@intPageSize int=10 ,–每页显示数
@RecordCount int OUTPUT  –总记录数(存储过程输出参数)
as  
   
declare @TmpSelect      NVarchar(600)  
declare @Tmp     NVarchar(600)  

set nocount on–关闭计数

set @TmpSelect = ‘select @RecordCount = count(*) from ‘+@TableName+’ ‘+@SelectWhere

execute sp_executesql
@TmpSelect,    –执行上面的sql语句
N’@RecordCount int OUTPUT’ ,   –执行输出数据的sql语句,output出总记录数
@RecordCount  OUTPUT

  if (@RecordCount = 0)    –如果没有贴子,则返回零
       return 0
      
   /*判断页数是否正确*/
  if (@intPageNo – 1) * @intPageSize > @RecordCount   –页号大于总页数,返回错误
     return (-1)
set nocount off–打开计数
if @SelectWhere != ”
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+’ ‘+@TableList+’ from ‘+@TableName+’ where ‘+@SelectOrderId+’ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from ‘+@TableName+’ ‘+@SelectWhere +’ ‘+@SelectOrder+’) and ‘+@SelectWhere +’ ‘+@SelectOrder
end
else
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+’ ‘+@TableList+’ from ‘+@TableName+’ where ‘+@SelectOrderId+’ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from ‘+@TableName+’ ‘+@SelectOrder+’) ‘+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)
GO

其实代码也很简单,学编程的人基本上都是懂数据库的,这个存储过程估计不是问题。
其他的代码我都做了解释,有颜色的那段我没有解释,我在这里解释一下。其实也很简单,大家来看:
select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from
‘+@TableName+’ ‘+@SelectWhere +’ ‘+@SelectOrder+’
这段代码的执行结果是什么了,是不是当前页前面的主键的集合啊,现在我们从所有的表中选出主键的值不在这个结果的之内的pagesize个记录不就是当前页的内容了吗?
2.aspx页面就不用再将了吧?我这里将代码写上:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="aa.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Height="180px" "867px">
            <Columns>
                <asp:BoundField DataField="job_id" HeaderText="job_id" />
                <asp:BoundField DataField="job_desc" HeaderText="job_desc" />
                <asp:BoundField DataField="max_lvl" HeaderText="max_lxl" />
            </Columns>
        </asp:GridView>
   
    </div>
           <asp:HyperLink ID="hylfirst" runat="server">首页</asp:HyperLink>
         
        <asp:HyperLink ID="hylprev" runat="server">上一页</asp:HyperLink>
         
        <asp:HyperLink ID="hylnext" runat="server">下一页</asp:HyperLink>
        <asp:HyperLink ID="hylend" runat="server">尾页</asp:HyperLink>
               第<asp:Label ID="lbRow" runat="server" Text="Label"></asp:Label>页,
          共<asp:Label ID="lbpage" runat="server" Text="Label"></asp:Label>页,共<asp:Label
            ID="lbRecord" runat="server" Text="Label"></asp:Label>条记录,转到<asp:TextBox ID="txtlink"
                runat="server" "29px"></asp:TextBox>
        页<asp:LinkButton ID="link" runat="server" OnClick="link_Click" TabIndex="1">转到</asp:LinkButton>
    </form>
</body>
</html>

3.cs页面其实也每页什么好讲的,也就是一些常用的代码罢了……我把代码加上,大家看看,要是有疑问的可以回复我再解释:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        this.bind();
        
    }

    protected void link_Click(object sender, EventArgs e)
    {
        int page = Convert.ToInt32(txtlink.Text);
        Response.Redirect("aa.aspx?CurrentPage="+page+"");
    }
    public void bind()
    {
        int sumPage;
        int pageNo = 1;
        int pageSize = 3;
        if (Request.QueryString["CurrentPage"] == null)
        {
            pageNo = 1;
        }
        else
        {
            pageNo = Int32.Parse(Request.QueryString["CurrentPage"]);
        }

        SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConStr"]);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = new SqlCommand();
        da.SelectCommand.Connection = conn;
        da.SelectCommand.CommandText = "getdataset";
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add("@TableList", SqlDbType.VarChar, 200).Value = "job_id,job_desc,max_lvl";
        da.SelectCommand.Parameters.Add("@TableName", SqlDbType.VarChar, 30).Value = "jobs";
        //da.SelectCommand.Parameters.Add("@SelectWhere", SqlDbType.VarChar, 500).Value = "where d=1";
        da.SelectCommand.Parameters.Add("@SelectOrderId", SqlDbType.VarChar, 20).Value = "job_id";
        da.SelectCommand.Parameters.Add("@SelectOrder", SqlDbType.VarChar, 200).Value = "order by min_lvl asc";
        da.SelectCommand.Parameters.Add("@intPageNo", SqlDbType.Int).Value = pageNo;
        da.SelectCommand.Parameters.Add("@intPageSize", SqlDbType.Int).Value = pageSize;
        da.SelectCommand.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        da.SelectCommand.Parameters.Add("RowCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
        DataSet ds = new DataSet();
        da.Fill(ds, "jobs");
        GridView1.DataSource = ds;
        GridView1.DataBind();
        Int32 RecordCount = (Int32)da.SelectCommand.Parameters["@RecordCount"].Value; //求出总记录数,该值是output出来的值
        Int32 RowCount = (Int32)da.SelectCommand.Parameters["RowCount"].Value;         //求出当前页中的记录数,在最后一页不等于pagesize,
        lbRecord.Text = RecordCount.ToString();
        lbRow.Text = pageNo.ToString();
        sumPage = (Int32)RecordCount / pageSize;
        if (RecordCount % pageSize > 0)
        {
            sumPage = sumPage + 1;
        }
        lbpage.Text = sumPage.ToString();
        if (pageNo > 1)
        {
            hylfirst.NavigateUrl = "aa.aspx?CurrentPage=1";
            hylprev.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", pageNo – 1);
        }
        else
        {
            hylprev.NavigateUrl = "";
            hylfirst.NavigateUrl = "";
            hylfirst.Visible = false;
            hylprev.Visible = false;
        }
        if (pageNo < sumPage)
        {
            hylend.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", sumPage);
            hylnext.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", pageNo + 1);
        }
        else
        {
            hylnext.NavigateUrl = "";
            hylend.NavigateUrl = "";
            hylend.Visible = false;
            hylnext.Visible = false;
        }

    }
}

就这样吧。要是大家有疑问,回帖我们再讨论,在研究……
对了,我将实现的效果图给大家传上来看看:

澳门新葡亰信誉平台游戏 1

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        this.bind();
        
    }

    protected void link_Click(object sender, EventArgs e)
    {
        int page = Convert.ToInt32(txtlink.Text);
        Response.Redirect("aa.aspx?CurrentPage="+page+"");
    }
    public void bind()
    {
        int sumPage;
        int pageNo = 1;
        int pageSize = 3;
        if (Request.QueryString["CurrentPage"] == null)
        {
            pageNo = 1;
        }
        else
        {
            pageNo = Int32.Parse(Request.QueryString["CurrentPage"]);
        }

        SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConStr"]);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = new SqlCommand();
        da.SelectCommand.Connection = conn;
        da.SelectCommand.CommandText = "getdataset";
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add("@TableList", SqlDbType.VarChar, 200).Value = "job_id,job_desc,max_lvl";
        da.SelectCommand.Parameters.Add("@TableName", SqlDbType.VarChar, 30).Value = "jobs";
        //da.SelectCommand.Parameters.Add("@SelectWhere", SqlDbType.VarChar, 500).Value = "where d=1";
        da.SelectCommand.Parameters.Add("@SelectOrderId", SqlDbType.VarChar, 20).Value = "job_id";
        da.SelectCommand.Parameters.Add("@SelectOrder", SqlDbType.VarChar, 200).Value = "order by min_lvl asc";
        da.SelectCommand.Parameters.Add("@intPageNo", SqlDbType.Int).Value = pageNo;
        da.SelectCommand.Parameters.Add("@intPageSize", SqlDbType.Int).Value = pageSize;
        da.SelectCommand.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        da.SelectCommand.Parameters.Add("RowCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
        DataSet ds = new DataSet();
        da.Fill(ds, "jobs");
        GridView1.DataSource = ds;
        GridView1.DataBind();
        Int32 RecordCount = (Int32)da.SelectCommand.Parameters["@RecordCount"].Value; //求出总记录数,该值是output出来的值
        Int32 RowCount = (Int32)da.SelectCommand.Parameters["RowCount"].Value;         //求出当前页中的记录数,在最后一页不等于pagesize,
        lbRecord.Text = RecordCount.ToString();
        lbRow.Text = pageNo.ToString();
        sumPage = (Int32)RecordCount / pageSize;
        if (RecordCount % pageSize > 0)
        {
            sumPage = sumPage + 1;
        }
        lbpage.Text = sumPage.ToString();
        if (pageNo > 1)
        {
            hylfirst.NavigateUrl = "aa.aspx?CurrentPage=1";
            hylprev.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", pageNo – 1);
        }
        else
        {
            hylprev.NavigateUrl = "";
            hylfirst.NavigateUrl = "";
            hylfirst.Visible = false;
            hylprev.Visible = false;
        }
        if (pageNo < sumPage)
        {
            hylend.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", sumPage);
            hylnext.NavigateUrl = string.Concat("aa.aspx?CurrentPage=", "", pageNo + 1);
        }
        else
        {
            hylnext.NavigateUrl = "";
            hylend.NavigateUrl = "";
            hylend.Visible = false;
            hylnext.Visible = false;
        }

    }
}

3.cs页面其实也每页什么好讲的,也就是一些常用的代码罢了……我把代码加上,大家看看,要是有疑问的可以回复我再解释:

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT 澳门新葡亰信誉平台游戏 2 FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

其实代码也很简单,学编程的人基本上都是懂数据库的,这个存储过程估计不是问题。
澳门新葡亰信誉平台游戏,其他的代码我都做了解释,有颜色的那段我没有解释,我在这里解释一下。其实也很简单,大家来看:
select top ‘+str((@intPageNo-1)*@intPageSize)+’ ‘+@SelectOrderId+’ from
‘+@TableName+’ ‘+@SelectWhere +’ ‘+@SelectOrder+’
这段代码的执行结果是什么了,是不是当前页前面的主键的集合啊,现在我们从所有的表中选出主键的值不在这个结果的之内的pagesize个记录不就是当前页的内容了吗?
2.aspx页面就不用再将了吧?我这里将代码写上:

SQL
Server服务器端分页

DECLARE @PK /* PK Type */
DECLARE @tblPK TABLE (
    PK /* PK Type */ NOT NULL PRIMARY KEY
)

SELECT Customers.ContactName AS Customer, 
       Customers.Address + ‘, ‘ + Customers.City + ‘, ‘ + 
                                                Customers.Country AS Address, 
       SUM([Order Details].UnitPrice*[Order Details].Quantity) AS 
                                                          [Total money spent] 
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ‘USA’ AND Customers.Country <> ‘Mexico’
GROUP BY Customers.ContactName, Customers.Address, Customers.City, 
         Customers.Country 
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
ORDER BY Customer DESC, Address DESC

通用化
我决定对其中的三个方法进行仔细的分析,它们是临时表(TempTable),动态SQL(DynamicSQL)和行计数
(Rowcount)。在下文中,我更愿意把第二个方法称为(升序-降序)Asc-Desc方法。我不认为动态SQL是一个好名字,因为你也可以把动态
SQL逻辑应用于另一个方法中。所有这些存储过程的通病在于,你不得不估计哪些列是你即将要排序的,而不仅仅是估计主键列(PK
Columns)而已,这可能导致一系列的问题——对于每个查询来说,你需要通过分页显示,也就是说对于每不同的排序列你必须有许多不同的分页查询,这意味着你要么给每个排序列做不同的存储过程(无论使用哪种分页方法),也么你必须借助动态SQL的帮助把这个功能放在一个存储过程中。这两个方法对于性能有微小的影响,但是它增加了可维护性,特别是当你需要使用这个方法显示不同的查询。因此,在本文中我会尝试使用动态SQL对所有的存储过程进行归纳,但是由于一些原因,我们只能对实现部分的通用性,因此你还是得为复杂查询写独立的存储过程。

结论
测试是按照从性能最好到最差的顺序进行的——行计数、游标、升序-降序、子查询。有一件事很有趣,通常人们很少会访问前五页之后的页面,因此子查询方法可能在这种情况下满足你的需要,这得看你的结果集的大小和对于远距离(distant)页面的发生频率预测,你也很有可能使用这些方法的组合模式。如果是我,在任何情况下,我都更喜欢用行计数方法,它运行起来十分不错,即使对于第一页也是如此,这里的“任何情况”代表了一些很难实现通用化的情况,在这种情况下,我会使用游标。(对于前两页我可能使用子查询方法,之后再用游标方法)

介绍
在Web应用程序中,对一个大数据库结果集进行分页已经是一个家喻户晓的问题了。简单的说,你不希望所有的查询数据显示在一个单独的页面中,所以带有分页的显示才是更合适的。虽然在传统的asp里这并不是一个简单的任务,但在asp.net中,DataGrid控件把这一过程简化为只有几行代码。因此,在
asp.net中,分页很简单,但是默认的DataGrid分页事件会从数据库中把所有的记录全部读出来放到asp.net
web应用程序中。当你的数据在一百万以上的时候,这将引起严重的性能问题(如果你不相信,你可以在你的应用程序中执行一个查询,然后在任务管理器中查看
aspnet_wp.exe的内存消耗情况)这也就是为什么需要自定义分页行为,这样可以保证仅获得当前页需要的数据记录。

结果
这里有我的测试结果:Paging_Results
(Excel文件)

完整代码:Paging_Asc_Desc

澳门新葡亰信誉平台游戏 3
澳门新葡亰信誉平台游戏 4
澳门新葡亰信誉平台游戏 5
澳门新葡亰信誉平台游戏 6
澳门新葡亰信誉平台游戏 7

INSERT INTO @temp 
SELECT TOP @PageSize PK FROM (
    SELECT TOP (@StartRow + @PageSize) 
    PK, 
    SortColumn /*If sorting column is defferent from the PK, SortColumn must 
                 be fetched as well, otherwise just the PK is necessary */ 
    ORDER BY SortColumn /* default order – typically ASC */) 
ORDER BY SortColumn /* reversed default order – typically DESC */

临时表
首先,我准备先来说一下临时表方法,这是一个广泛被建议使用的解决方案,我在项目中遇到过好几次了,这里有另一篇解释它如何工作的文章,还有一个如何在DataGrid中是用定制化分页(Custom
Paging)的例子:

在测试中,我使用了一个自动生成得大数据表,大概插入了500000条数据。如果你没有一张这样的表来做实验,你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列,而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本,你可能会考虑在生成表之后添加一个自增列,这些自增数据会根据主键进行数字排序,这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。

SELECT 澳门新葡亰信誉平台游戏 8 FROM Table WHERE PK IN 
    (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
        (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
    ORDER BY SortColumn)
ORDER BY SortColumn

CLOSE PagingCursor
DEALLOCATE PagingCursor

完整代码:Paging_Cursor

完整代码:Paging_RowCount

值得注意的是,在原始查询中在ORDER
BY语句中使用了别名,但你最好不要在分页存储过程中这么做,因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现,但原则是不要在一开始把所有的字段包括进去,而仅仅是包括主键列(等同于RowCount方法中的排序列),这样可以加快任务完成速度。只有在请求页中,才获得所有需要的字段。并且,在最终查询中不存在字段别名,在跳行查询中,必须提前使用索引列。

行计数
这个方法的基本逻辑依赖于SQL中的SET
ROWCOUNT表达式,这样可以跳过不必要的行并且获得需要的行记录

PagingTest Solution (C#) – 55.8
Kb
Paging_Procedures SQL script – 2.48
Kb
Paging_LargeTable SQL script – 0.6
Kb

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
    INSERT @tblPK(PK) VALUES(@PK)
    FETCH NEXT FROM PagingCursor INTO @PK
    SET @PageSize = @PageSize – 1
END

性能测试
在测试中,我使用了四种方法,如果你有更好的方法的话,我很有兴趣知道。不管如何,我需要对这些方法进行比较,并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序,然后测试页面结果。当然,这无法反映存储过程的真实响应时间,所以控制台应用程序显得更加适合。我还加入了一个Web应用程序,但不是为了性能测试,而是一个关于DataGrid自定义分页和存储过程一起工作的例子。这两个应用程序都可以在
Paging Test
Solution中找到。

OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK

允许包括主键列在内的所有排序字段的第二个问题在于,如果那些列没有作适当的索引,那么这些方法一个也帮不上忙。在所有这些方法中,对于一个分页源必须先做排序,对于大数据表来说,使用非索引列排序的成本是可以忽略不计的。在这种情况下,由于相应时间过长,所有的存储过程都是无法在实际情况下使用的。(相应的时间各有不同,从几秒钟到几分钟不等,这要根据表的大小和所要获得的第一个记录而定)。其他列的索引会带来额外的不希望出现的性能问题,例如如果你每天的导入数据很多,它有可能变得很慢。

返回第二个页面的分页存储调用如下:

《如何通过Recordset进行分页?》

这两篇文章中的方法都是通过把主键数据拷贝到临时表中,然后对主查询做join实现查询优化。下面让我们来看看这个方法的实质:

但是我对上文的大部分内容不是很满意。第一,半数的方法是用了传统的ADO,很明显它们是为“古老”的asp而写的。剩下的一些方法就是SQL
Server存储过程,并且其中的一些由于相应时间过慢而无法使用,正如你在文章最后所看到的性能结果一样,但是还是有一些引起了我的注意。

SELECT 澳门新葡亰信誉平台游戏 9 FROM Table JOIN @tblPK temp ON Table.PK = temp.PK 
ORDER BY SortColumn

SELECT 澳门新葡亰信誉平台游戏 10 FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID 
WHERE ID > @StartRow AND ID < @EndRow

游标
在看google讨论组的时候,我找到了最后一个方法,你可以点这里查看原始帖子。该方法是用了一个服务器端动态游标。许多人试图避免使用游标,因为游标没有关系可言,以及有序性导致其效率不高,但回过头来看,分页其实是一个有序的任务,无论你使用哪种方法,你都必须回到开始行记录。在之前的方法中,先选择所有在开始记录之前的所有行,加上需要的行记录,然后删除所有之前的行。动态游标有一个FETCH
RELATIVE选项可以完成魔法般的跳转。基本的逻辑如下:

子查询
还有两个方法也是我考虑过的,他们的来源不同。第一个是众所周知的三角查询(Triple
Query)或者说自查询方法,我找的一个比较透彻的方法在下面的文章中有描述

复杂查询的通用化
我在之前指出,所有的存储过程都是用动态SQL实现通用性的,因此,理论上它们可以用任何种类的复杂查询。下面有一个基于Northwind数据库的复杂查询例子。

完整代码:Paging_SubQuery

ASP.NET DataGrid分页 第二部分 –
定制化分页

CREATE TABLE #Temp (
    ID int IDENTITY PRIMARY KEY,
    PK  /* here goes PK type */
)

通过把所有的行拷贝到临时表中,我们可以对查询进一步的优化(SELECT TOP
EndRow
…),但是关键在于最坏情况——一个包含100万记录的表就会产生一个100万条记录的临时表。考虑到这样的情况,再看看上面文章的结果,我决定在我的测试中放弃该方法

为了实现性能测试,我是通过一个循环多次调用一个特定的存储过程,然后计算平均相应时间来实现的。考虑到缓存的原因,为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的,因此,我们在调用同一个存储过程时,每一次调用所请求的页码应该是随机的。当然,我们必须假设页的数量是固定的,10-20页,不同页码的数据可能被获取很多次,但是是随机获取的。

行计数(RowCount)存储过程有一个另外的问题,要实现通用化,在ORDER
BY语句中只允许有一个列,这也是升序-降序方法和游标方法的问题,虽然他们可以对几个列进行排序,但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的,但是在我看来这不是很值得。虽然这样的情况很有可能发生,但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。

SELECT 澳门新葡亰信誉平台游戏 11 FROM Table JOIN @Temp temp ON Table.PK = temp.PK 
ORDER BY SortColumn /* default order */

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

EXEC ProcedureName
/* Tables */
‘Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID’,
/* PK */
‘Customers.CustomerID’,
/* ORDER BY */
‘Customers.ContactName DESC, Customers.Address DESC’,
/* PageNumber */
2,
/* Page Size */
10,
/* Fields */
‘Customers.ContactName AS Customer,
Customers.Address + ”, ” + Customers.City + ”, ” + Customers.Country 
                                                                  AS Address, 
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]’,
/* Filter */
‘Customers.Country <> ”USA” AND Customers.Country <> ”Mexico”’,
/*Group By*/
‘Customers.CustomerID, Customers.ContactName, Customers.Address, 
 Customers.City, Customers.Country 
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000′

升序-降序
这个方法在子查询中使用默认排序,在主查询中使用反向排序,原理是这样的:

在网上有很多关于这个问题的文章和帖子,还有一些成熟的解决方案。我写这篇文章的目的不是向你展示一个可以解决一切问题的存储过程,而是出于优化已有方法,同时为你提供一个可供测试的应用程序,这样你就可以根据自己的需要进行开发。下文是一个很好的开始,它包含了很多不同的方法,并且给出了一些性能测试结果

有一点我们很容易注意到,相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的,越是远离结果集的开始位置,就有越多的记录要跳过,这也是我为什么不把前20也包括进我的随机序列的原因。作为替换,我会使用2的n次方个页面,循环的大小是需要的不同页的数量*1000,所以,每个页面几乎都被获取了1000次(由于随机原因,肯定会有所偏差)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图