Sql Server 2000自訂分頁

目前我們採用的翻頁處理通常都是採用DataGrid預設的分頁,這種方式雖然只用綁定當前頁的資料,但是他從資料庫返回的資料集仍然是整個資料集,這樣,如果查詢返回的是一個大資料量的資料集,那麼在翻頁過程將會很耗時,因為他浪費了網路頻寬與伺服器資源(綁定一頁,但仍要返回整個資料集).因此,針對此種情況所做的最佳化的原則就是:不採用DataGrid的預設分頁,採用自訂分頁,要顯示哪頁的資料,就返回那一頁的資料.可以用如下兩種方式來處理該資料分頁問題,a.SQl語句;b.預存程序

測試環境:一張包含兩個欄位的表tempTable,tempId (主鍵,順序ID),tempGuid(資訊,一個Guid),往表插入入了100W行資料

 

採用DataGrid預設翻頁的方式:

翻一頁平均所需的時間大約為:8000毫秒.(該處所使用的測試時間是從頁面Postback到執行完Render方法後所需的時間.

 

採用自訂翻頁的方式:

a.    SQL語句的方式(1)

SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 頁大小*頁數 id
         FROM 表
         ORDER BY id))
ORDER BY ID

示意代碼-

private void Page_Load(object sender, System.EventArgs e){    if(!Page.IsPostBack)    {        BindData(0); //執行綁定第0頁的資料    }    else    {        dt1 = DateTime.Now; //初始時間    }}protected override void Render(HtmlTextWriter writer){    base.Render (writer);    dt2 = DateTime.Now; //結束時間    TimeSpan ts = dt2 - dt1; //所耗時間    Response.Write("alert('" + ts.TotalMilliseconds + "')");}private void BindData(int pageIndex)    {        int recordCount = 0; //定義記錄總數        DataSet ds = GetData(pageIndex,ref recordCount); //得到當前頁的資料        dgTest.VirtualItemCount = recordCount; //        dgTest.DataSource = ds.Tables[0].DefaultView;        dgTest.DataBind();    }private DataSet GetData(int pageIndex,ref int recordCount)    {string strSql = @"select top 20 * from tempTable 
       where tempId not in ( select top " + (20*pageIndex) 
+ " tempId from tempTable order by tempId) order by tempId";string strCount = @"select count(*) from tempTable";DataSet ds = new DataSet();SqlConnection con = 
new SqlConnection(
@"server=localhost;User ID=sa;Password=sa;database=Castle;Connection Reset=FALSE");SqlCommand command = new SqlCommand(strSql,con);con.Open();SqlDataAdapter d = new SqlDataAdapter(command);d.Fill(ds);command.CommandText = strCount;SqlDataReader sr = command.ExecuteReader();sr.Read();recordCount = sr.GetInt32(0);sr.Close();con.Close();return ds;}

 

   

通過此種方式,100W行資料翻頁只需要250毫秒左右.

這種方式的缺點: 1.需要拼湊SQL  2.如果翻到很後面的頁數時,可能會慢一些.(因為not in,可能也不會是問題,因為沒有人會翻到那麼後的頁數)

 

SQL語句方式2:

SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP

頁大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 頁大小*頁數 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID