WizardWu 編程網

一位台灣的工程師,接觸 .NET 逾十年,近年研究 SQL Server、Performance Tuning、手機應用

博客园 首页 新随笔 联系 订阅 管理
  107 Posts :: 0 Stories :: 1097 Comments :: 35 Trackbacks

(本帖在版工的旧 Blog 中,发表日期为 2007/10/03)

ASP.NET 的 GridView 控件、SqlDataSource 和 ObjectDataSource 控件,其内建的「数据分页 (pager)」功能,默认做法,是当 user 每次单击 GridView 的页码换页时,都重新去数据库里,把数据表的「所有」数据,「全部」重新 SELECT 一次,并「全部」传送至 AP server (IIS),并将大量数据「全部」存储至 memory 里的 DataSet / DataTable,再将「所有」的数据,在 memory 里做分页处理后,再「全部」传送至 user 的浏览器中,最后才呈现在 GridView 控件中;因此当数据表累积了很多笔记录后,例如一百万笔 record 时,因为 user 每次换页或按 GridView 的字段 title 排序时,背后运作,都是把一百万笔 record「全部」重新 SELECT 一次,因此就会在:数据库 → IIS、DataSet → GridView 时,出现严重的 performance 问题,且会浪费大量网络频宽,浪费大量 DB server、AP server 的 CPU、memory 硬件资源;当多人同时上线时问题更严重,甚至会造成 server 当机。

架构图如下:
使用者 (Browser + GridView)AP server (IIS) + DataSet / DataTableDB server (Stored Procedure 或 RowNumber 函数)

以版工自己的经验,当数据表的数据量累积到 5000 笔以上时,即使只有一个 user 在线,每次单击 GridView 的页码换页时,都要等 2 秒以上;因为每次换页时,都是 5000 笔记录全部重捞。而且此种情况,在系统开发时期、数据量还很少时感觉不出来;都是在系统上线、数据量开始大量增加时,才会听到客户抱怨性能不佳。也因为 ASP.NET 这种「分页」的默认行为,造成许多人误以为 ASP.NET、SQL Server 性能不佳,不适合开发大型系统。

对于此种 ASP.NET 分页功能,程序员必须再额外处理,启用 ObjectDataSource 控件默认未启用的「EnablePaging="true"」,并自己写 Stored Procedure 去做分页处理,亦即每次 user 换页时,都只撷取真正需要的记录数量,而非每次都一百万笔记录全部撷取。

在网络上,有些人是用 .NET 的自订 Class + List<T> 动态 array,取代数据库的 Stored Procedure 去处理分页。但参考本帖上方的架构图,此种做法,仍是先从数据库撷取了一百万笔数据、浪费了 DB server 大量的硬件资源,再透过网络把一百万笔数据全部传送至 IIS,此时才用 .NET 自订 Class 去处理分页和过滤,最后才把 user 真正需要的数据 (可能只有 10 笔记录),送至前端的 Browser,因此这种做法显然并非最佳解法。

还有另一种做法,是用 DataAdapter 的 Fill 方法,去做数据过滤的动作,如下方代码所示。但此种做法的问题同上,已先从数据库撷取了一百万笔记录,才从 AP server 去做数据过滤、分页处理的动作,仍非最佳解法。

int currentIndex = 0;
int pageSize = 5;

string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";

// Assumes that connection is a valid SqlConnection object.
SqlDataAdapter adapter = new SqlDataAdapter(orderSQL, connection);

DataSet dataSet 
= new DataSet();
adapter.Fill(dataSet, currentIndex, pageSize, 
"Orders");


此外,SqlDataSource、ObjectDataSource 控件,都有一个 FilterExpression 属性,亦可做数据筛选的处理,但问题同上,仍然是在 AP server 的 memory 中,才去对已撷取的一百万笔记录,做数据过滤的动作。因此釜底抽薪的最佳做法,应该是从数据库端,即先过滤数据,例如 GridView 若每页要显示 10 笔记录,就只从数据库撷取 10 笔记录,以 performance 来说,这样才是最佳解法。否则以 Web-based 的 ASP.NET 系统来说,因为会有大量 user 同时上线,以一般 AP Server、DB server 的硬件资源,绝对承受不了一百万笔数据不断大量重复撷取的 loading,AP server 的 memory 也会很快就耗光造成当机。


在旧版的 SQL Server 2000 和其它厂牌的数据库,大多未内建「排序或自动给号」的函数;程序员在撰写分页的 Stored Procedure 时,可能还得用 IDENTITY 自动增号 (流水号) 的功能,先建立一个存放「临时行号 (自动增号、流水号)」的「临时数据表 (Temporary Table)」;但此种大量建立「临时数据表」的做法,会影响 DB server 的 performance,并非「分页」处理的最佳解法。而在 SQL Server 2005 中,已新增了一个专门用来「排序和自动给号」的 ROW_NUMBER 函数,可对已从数据库撷取的数据,再赋予一个「自动编号」的「字段;列 (column)」,且听说性能颇优,也更有利于 ASP.NET 分页的处理。

-------------------------------------------------

我们若在 SQL Server 2005 的 Northwind 数据库中,执行下列 SQL 语句:

SELECT OrderID, CustomerID, OrderDate, ROW_NUMBER() OVER(ORDER BY OrderID DESCAS 字段编号
FROM Orders 
ORDER BY 字段编号


因为未对 Orders 数据表做 WHERE 条件过滤,会如下图 1 所示,将里面的 830 笔记录全部捞出、用 DESC 反向排序后,再由 ROW_NUMBER 函数提供一个重新编号过的流水号字段「字段编号」,从 1 号自动给号至 830 号:

figure1
图 1 图中右下角的 830 个数据列,事实上是指「记录 (record);行 (row)」。简体中文和繁体中文对数据表的「行」、「列」使用上刚好相反

-------------------------------------------------

若我们改执行下列的 SQL 语句 (取自 SQL Server 在线丛书):

WITH 暂存表 AS
(
SELECT OrderID, CustomerID, OrderDate, ROW_NUMBER() OVER(ORDER BY OrderID DESCAS 字段编号
FROM Orders) 
SELECT * FROM 暂存表 WHERE 字段编号 BETWEEN 5 AND 13


figure2
图 2

则可由 ROW_NUMBER 函数模拟的临时数据表中,取得我们写分页时,所需要的某个范围内的数据记录笔数。以上图 2 来说,即只撷取 DESC 反向排序后、5 至 13 号的这九笔记录。且这种 WITH 的 T-SQL 新语法,又称为「一般数据表表达式 (CTE, common_table_expression)」,也是与 ROW_NUMBER 函数搭配,撰写分页程序的精要所在,在 SQL Server 在线丛书也有相关介绍。

-------------------------------------------------

版工还在网络上的论坛 [5],看到别人提供的语法,先用 ROW_NUMBER 函数取得数据的顺序编号,再用 WHERE 条件过滤:

SELECT TOP(分页大小) * 
FROM 
(
SELECT ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNo FROM 数据表
AS T
WHERE RowNo > ((目前页数 - 1* 分页大小) 


假设我们的 GridView 每页要显示 10 笔记录,user 目前在 GridView 的第 20 页,当他单击「下一页」或第 21 页的页码时,就去 SQL Server 2005 撷取第 201 ~ 210 笔记录,在 Stored Procedure 里即执行下列 SQL 语句。撷取结果如下图 3 所示:

SELECT TOP(10* 
FROM 
(
SELECT OrderID, CustomerID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderID DESCAS 字段编号 FROM Orders
AS 暂存表 
WHERE 字段编号 > ((21 - 1* 10


figure3
图 3 GridView 每页显示 10 笔记录,若使用者按下了第 21 页的页码

-------------------------------------------------

但以 ROW_NUMBER 函数撰写分页的话,亦要考虑系统以后无法更换数据库的问题;且用 ROW_NUMBER 写好的 Stored Procedure,在其它 project 中,也无法重复使用于他牌的数据库,或旧版的 SQL Server。就如同 ADO.NET 2.0 中,有新增一些针对 SQL Server 2005 可提升 performance 的 .NET 数据处理语法,但使用前应先评估,系统日后是否有移植或维护上的问题。

-------------------------------------------------

本帖参考文件:

[1] 揚棄土法煉鋼、巧妙運用排序函數 實現SQL Server 2005自動分頁非難事:
http://oa.digitimes.com.tw/print.aspx?zNotesDocId=0000040434_B51LZD55XF2OFQG5TTEZM

[2] Sql Server 2005 ROW_NUMBER 函数实现分页:
http://www.cnblogs.com/shanyou/archive/2006/08/22/rownumber.html

[3] 當 DataGrid 遇見 100 萬筆資料:
http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921

[4] 請問有關開啟頁面時,一次載入數千筆資料的效能問題:
http://www.blueshop.com.tw/board/show.asp?subcde=BRD200709141021458MV

[5] 老問題 - GridView 分頁:
http://www.purecs.net/thread/topic368_1.aspx

-------------------------------------------------

本帖相关文件:

[1] SQL Server 2005 中的 ROW_NUMBER 和 RANK:
http://blog.csdn.net/leizhipan/archive/2006/10/19/1341245.aspx

[2] SQL Script 應用實例系列(01)--將同一資料表的每三筆資料組合成一筆:
http://blog.blueshop.com.tw/jacksun/archive/2007/03/07/49860.aspx

[3] SQL Script 應用實例系列(02)--分群組排名的問題 :
http://blog.blueshop.com.tw/jacksun/archive/2007/03/08/49883.aspx

[4] 10 Tips for Writing High-Performance Web Applications:
http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/

-------------------------------------------------
(有些 Hyperlink 是连到台湾的网站,若无法连结,麻烦请留言反应)

 

posted on 2008-08-02 16:11  WizardWu  阅读(...)  评论(... 编辑 收藏