数据分页技术

几乎所有的应用程序都会用到数据和数据库。但在对于大数据量的存取设计上,是有很多讲究的。例如,我们需要在一个页面上显示很多数据,我们可能会考虑分页。.NET自带的GridView控件就能很容易地实现分页,但是那样做是否真的合理呢?除此之外,是否还有其他的方法呢?下面就特意拿了一些数据来比较一下

【以下的测试结果,仅供参考】

测试场景:

  • 数据库 Adventureworks
  • 数据表 Sales.SalesOrderDetail
  • 数据行数 121317

 

客户端分页的效果(直接使用GridView控件的分页)

image

image

image

数据绑定的代码

using (SqlConnection conn = new SqlConnection("server=(local);database=Adventureworks;integrated security=true"))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "GetSalesOrderDetail2";
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();

        conn.Open();
        adapter.Fill(ds);

        GridView1.AllowPaging = true;
        GridView1.PageSize = 10;
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

分页代码

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    DataBind();
}

 

存储过程

CREATE PROC GetSalesOrderDetail2
AS
SELECT SalesOrderID,SalesOrderDetailID,UnitPrice,OrderQty,LineTotal FROM Sales.SalesOrderDetail ORDER BY SalesOrderID DESC

 

服务器端分页的效果(在SQL Server存储过程中进行分页,即每次只取必须的数据)

image

注意,这几个按钮是要自己专门添加的。

image

image

初略计算,使用服务器端分页的技术速度将比在客户端分页的技术快 871 倍左右,这个情况,在数据量越大的情况下越明显。因为GridView自己的分页方式需要把数据全部读取到内存,然后再决定哪些是要显示的

 

数据绑定代码

int pageIndex = int.Parse(GridView2.Attributes["Index"]);//这里从属性中读取当前的页码
if (pageIndex == 0) pageIndex = 1;

using (SqlConnection conn = new SqlConnection("server=(local);database=Adventureworks;integrated security=true"))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "GetSalesOrderDetail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(new SqlParameter[]{
            new SqlParameter("@PageIndex",pageIndex),
            new SqlParameter("@PageSize",10)
        });

        SqlParameter pageCountParam = new SqlParameter("@PageCount", SqlDbType.Int);
        pageCountParam.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(pageCountParam);

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();

        conn.Open();
        adapter.Fill(ds);
        GridView2.Attributes["PageCount"] = pageCountParam.Value.ToString(); //这里把总的页数读出来,放在一个属性里面
        GridView2.AllowPaging = true;
        GridView2.PageSize = 10;
        GridView2.DataSource = ds;
        GridView2.DataBind();
    }
}

 

分页的代码

protected void btFirstPage_Click(object sender, EventArgs e)
{
    GridView2.Attributes["Index"] = "1";
    DataBind();
}

protected void btPreviouPage_Click(object sender, EventArgs e)
{
    int index = int.Parse(GridView2.Attributes["Index"]);
    GridView2.Attributes["Index"] = (index - 1).ToString();
    DataBind();
}

protected void btNextPage_Click(object sender, EventArgs e)
{
    int index = int.Parse(GridView2.Attributes["Index"]);
    GridView2.Attributes["Index"] = (index + 1).ToString();
    DataBind();
}

protected void btLastPage_Click(object sender, EventArgs e)
{
    int Count = int.Parse(GridView2.Attributes["PageCount"]);
    GridView2.Attributes["Index"] = Count.ToString();
    DataBind();
}

存储过程

ALTER PROC GetSalesOrderDetail(@PageIndex INT,@PageSize INT,@PageCount INT OUTPUT)
AS
DECLARE @TotalRows DECIMAL(18,2)
DECLARE @Count DECIMAL(18,2)
BEGIN
SELECT @TotalRows=COUNT(*) FROM Sales.SalesOrderDetail

SET @COUNT=@TotalRows/CONVERT(DECIMAL(18,0),@PageSize)
IF(@COUNT<=ROUND(@COUNT,0))
    SET @COUNT=ROUND(@COUNT,0)
ELSE
    SET @COUNT=ROUND(@COUNT,0)+1

SET @PageCount=CONVERT(INT,@COUNT)

SELECT SalesOrderID,SalesOrderDetailID,UnitPrice,OrderQty,LineTotal FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY SalesOrderID DESC) RowNumber,SalesOrderID,SalesOrderDetailID,UnitPrice,OrderQty,LineTotal FROM Sales.SalesOrderDetail) OrderData WHERE RowNumber BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize

END

 

值得一说的是,DbDataAdapter有一个Fill 方法 (DataSet, Int32, Int32, String),可以根据一个开始行号,和最大行号,进行填充。这种做法可以理解为在中间做了分层,就是说填充到DataSet的时候,已经只有当前页的数据了。这也能够提高性能,但是却不可避免地是:在数据库里面还是把所有的数据都读出来了,只不过是Adapter再过滤一次

posted @ 2008-07-04 20:43  陈希章  阅读(2488)  评论(2编辑  收藏  举报