讨论 使用SQL实现分页

[yexiaotong]

背景：
检索有几十万条记录的表显示在页面上。
把所有记录也就是含有几十万条记录的结果集直接Fill到DataSet里面。
然后使用DataGrid的分页功能来显示几十条记录。

几十万条记录的结果集从MSSQL服务返回到Asp.Net服务就需要花费比较长的时间，Asp.Net把这几十万条记录再填充到DataSet里面要不知要花多少时间，然后DataGrid检索绑定DataTable要需要花多少时间。根据一般为提高服务速度的算法，短任务优先执行的原则。可以想象请求返回几十万条记录的线程在MSSQL和Asp.Net服务里面的优先级别比较低，更增加响应时间。更要命的是，Asp.Net的程序很多PostBack,导致提交一次，就要把上面的事情在忙一次。这样服务器很容易崩溃。

解决方法：
1。 有不少人提出的解决方案是使用存储过程实现分页，这是一个好办法。
2。 SQL语句实现分页的功能。 参考：《构建Web解决方案——应用Asp.net和ado.net》 我把这个例子贴出来，大家来讨论一下利弊。

[yexiaotong]

using System.Data.SqlClient;
using System.Text;

在界面上放置一个DataGrid id ＝ grid
还有一个LinkButton



private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
//计算记录的总数
SetVirtualItemCount();

//设置排序的关键字的字段名
ViewState["SortExpression"] = "employeeid";

//设置grid的分页属性 和 每页的行数
grid.AllowPaging = true;
grid.AllowCustomPaging = true;
grid.PageSize = 5;

}

}


//计算记录的总数
public void SetVirtualItemCount()
{
// Set up the connection
String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn);

// Set up the command
String strCmd = "SELECT COUNT(*) FROM Employees";
SqlCommand cmd = new SqlCommand(strCmd, conn);

// Execute the command
conn.Open();
int nItemCount = (int) cmd.ExecuteScalar();
conn.Close();

grid.VirtualItemCount = nItemCount;
return;
}


//每次取数据的过程
SqlDataReader dr;
public SqlDataReader CreateDataSource(int nPageIndex)
{
nPageIndex ++;

String sConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";

// Prepare the SQL command
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT TOP {0} * FROM ");
sb.Append("(SELECT TOP {0} employeeid, firstname, lastname FROM Employees ");
sb.Append("WHERE {1} IN ");
sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) ");
sb.Append("ORDER BY {1} DESC) AS tmp ");
sb.Append("ORDER BY {1}");
String strCmd = sb.ToString();

// Set pseudo-parameters: RowsToFetch, SortField and RowsToFetch
// {0} - rowstodisplay 显示的记录条数
// {1} - sortfield 排序关键字段名
// {2} - rowstofetch 从后台取得记录数

int nRowsToDisplay = grid.PageSize;
int nMod = grid.VirtualItemCount % grid.PageSize;
if (nPageIndex == grid.PageCount && nMod >0)
nRowsToDisplay = nMod;

strCmd = String.Format(strCmd, nRowsToDisplay, ViewState["SortExpression"],
grid.PageSize * nPageIndex);

SqlConnection conn = new SqlConnection(sConn);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = strCmd;

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return dr;
}



private void grid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
grid.DataSource = CreateDataSource(grid.CurrentPageIndex);
grid.DataBind();
dr.Close();
}



private void LinkButton1_Click(object sender, System.EventArgs e)
{
grid.DataSource = CreateDataSource(0);
grid.DataBind();
}

[duyl]

大数据量的分页一直是个头疼的问题﹐用StroeProcedure可以解决﹐但如果在DataGrid上加有排序事件﹐那又该如何处理呢﹐欢迎来探讨一下

[飞不动]

用存储过程分页，是个不错的想法。
我以前也看到过类似的例子。要充分利用ID号。
不过速度也没有明显的提高！

[yypt111]

想过，但想听听是否有相对比较完美的思路

[gujie]

路过，学习