專刊分享內文 - 訊光科技系統股份有限公司

EEP ADO.NET的海量資料存取方案

訊光科技

前言

隨著企業IT基礎架構日益複雜，應用系統和資料量呈現爆炸性成長，面對動輒上百萬筆資料，如何有效且快速的從中獲取所需的資料，變成了一個種要的課題，因次訊光在針對EEP讀取大量資料的優化，也一直不遺餘力，本文將介紹EEP如何利用ADO.NET的架構下從大量資料中快速取得資料，做精闢的探討。

ADO架構

ADO.NET 是 .NET 應用程式中的資料存取模組。它是由微軟資料存取物件ADO (ActiveX Data Object) 演進而來的，它是用來存取關聯式資料庫系統，如：SQL Server 2000， Oracle，Informix以及其他許多 OLE DB 或是 ODBC provider 的資料來源。

ADO.NET是以全域資料存取(Universal Data Access；UDA)為概念，簡而言之，就是程式設計師不須要去了解個別資料庫的存取方式，只要了解ADO.NET物件的使用方式就可以了。因此ADO.NET優點整理如下：

◆ 交互運作性：ADO.NET使用XML做為傳輸資料的格式、易於交換。
◆ 易維護：ADO.NET使用本機記憶體中的快取 (Cache) 存放資料，容易應用在多層式 (N-Tier，分散式架構) 應用程式架構中。
◆ 程式化能力：ADO.NET的程式化模型使用強制型別的資料。若結合開發工具，如 Visual Studio提供IntelliSense功能，使得具型別的資料可以讓程式碼更為簡潔，更容易撰寫。
◆ 執行效能：由於ADO.NET使用具型別的資料，避免許多資料型別轉換的負擔，可提升執行的效能。
◆ 延展性：ADO.NET可將資料保存在快取 (Cache) 中，不需要維護資料鎖定，和及時的資料庫連線等問題，更易達到延展性。
◆ 與XML工業標準整合：.NET Framework和XML技術無痕地整合，ADO.NET底層是以XML作為資料交換的基礎的，實際上在.NET中許多技術，都是透過XML做為應用程式組態或交換的標準。

$C:\Users\Edward\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png$

ADO.net效能問題

以WEB程式運作來說，當使用者進行查詢時，會將所有的查詢資料透過ADO.Net從資料來源下載到Web Server之後，再把頁面Render到前端給使用者看，當資料量很大時，來往Web Server和DB Server之間的資料量及所占用的頻寬將跟著放大。假使不做任何處理的話，整個網頁的執行效能將會非常的慢。

因此ASP.NET也提供解決的方案透過GridView、FormView等各種資料繫結控制項，來協助開發人員很容易的把資料來源的資料呈現在網頁上並且提供分頁功能，只把PageIndex所指的頁面Render到前端給使用者看，減少大量資料的傳輸所造成的效能問題。但這樣也僅是解決資料傳輸到瀏覽器的問題，沒有解決Web Server與DB Server間的流量問題。

$C:\Users\Edward\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png$

EEP PacketRecord機制

如前面所言，使用 ASP.NET所提供的控制項分頁機制，僅能解決Web Server與使用者端Browser間大量資料封包傳遞的問題，但是當資料庫資料量很大時，並無法解決來往於Web Server和DB Server之間傳輸的資料量與效能的問題。往往因此而拖慢系統的整體效能，所以若能將資料分批下載，避免將所有資料一次由DB Server下載至WebServer，相信對呈現大量資料的效能會有所助益。

有鑑於此，訊光在.NET EEP平台中就針對這個效能問題著手改善，來提升Web大量資料撈取效能，EEP是在WebDataSet元件中加入PacketRecords屬性(在WEB網頁上的資料來源元件)，透過此屬性可以設定每次從資料庫所要讀入的筆數，讓系統免於一次把所有的資料都讀入，改採用分段讀取的方式運作，例如將WebDataSet的PacketRecords設定為100筆，則使用者在查詢時AP Server會對DB取得該查詢條件所取得的前100筆資料，並把頁面Render到前端給使用者，當使用者瀏覽到第101筆資料時，EEP會再去讀取下100筆資料，這樣可以改善傳統一次將所有資料撈取到Web Server造成效能不佳的窘境。

$C:\Users\Edward\AppData\Local\Temp\msohtmlclip1\01\clip_image003.png$

EEP SelectTop的機制

當然，上面的PacketRecord機制，只是解決了N-Tier多層的資料分批流量的控制，還是無法解決從Application Server去向DB Server讀取海量資料的問題，因為EEP的Application Server也是透過ADO .NET的方式去打開資料表，如果User沒有下達Where的條件，那幾百萬的資料打開還是非常耗時的，EEP為了增加效能，在InfoCommand增加了SelectTop屬性，使用者可以利用此屬性設定查詢所要讀取的最大筆數，當使用者查詢時，會在InfoCommand的CommandText SQL語法加上TOP N的語法，如此一來可以避免大量資料的傳輸所造成的系統效能不佳問題。雖然此屬性可以增進程式效能，不過也有其缺點，就是查詢資料永遠只能回傳SelectTop屬性所設定的筆數，因此使用者必須對查詢條件下得更精準，才能取得所要的資料，似乎不夠人性化。

強大的SelectPaging功能

承上文，EEP 的PacketRecords屬性將資料分段下載，只改善了Application Server與Web Server資料的傳輸效能問題，如果面對動輒上百萬、上千萬筆海量的資料來說，分段讀取時在海量資料中去過濾出所需要的資料，仍然是一個耗時且耗系統資源的工作，因為每一次透過ADO.NET讀取資料還是必須全數讀取到AP Server再過濾，除了佔用了AP Server與DB Server的通訊頻寬，也大大影響DB Server的負載能力(CPU與記憶體都提升很多)。

也因為這兩年來訊光投入的ADO.NET的Entity Framework的研究，發現Entity Framework的效能比傳統ADO.net的存取還快，好奇以Profiler來分析語句後，發現MS-SQL Server有了最新的分頁技術來控制海量資料的讀取。因此EEP在InfoCommand也增加了一個SelectPaging屬性(在EEP 2010的SP2版本中提供)，只要將SelectPaging屬性設為True則會使用新的分段讀取技術來讀取目前的資料庫，接下來我們就了解一下SelectPaging屬性的運作原理與功能。

而資料庫分頁讀取原理就是透過新的SQL Server ROW_NUMBER()函數，這個函數會針對資料表的Key值欄位排序來取得一個次序編號，並以這個編號作為分頁的依據來直接定位分段資料且將資料取出，如此即可節省大量資料過濾所浪費的時間與系統資源。

我們就以MS-SQL的Northwind(北風資料庫)，以Orders(訂單主檔)、Order Details(訂單明細檔)為例，當EEP的PacketRecords屬性設定為100時，則使用者要瀏覽第101筆以後的資料時，因SelectPaging屬性的不同其對應SQL如下表。

SelectPaging=False	SelectPaging=True
單一的Table： select * from orders	select top 100 * from (select *，row_number() over (order by orderid) as r from orders) as o where r > 100 order by r
Left Join的Table： select [order details].* ，orders.customerid from [order details] left join orders on [order details].orderid = orders.orderid	select top 100 * from (select [order details].*,orders.customerid ，row_number() over (order by [order details].orderid，productid) as r from [order details] left join orders on [order details].orderid = orders.orderid) as o where r > 100 order by r

註一：如果是Oracle的話，SelectPaging=True時會自動轉為select * FROM (select *，ROWNUM r from orders where ROWNUM <= 110) WHERE r > 100 ORDER BY r (Oracle版本必須為9以上的版本才能使用SelectPaging)。

註二：如果是Informix的話，SelectPaging=True時會自動轉為select SKIP 100 FIRST 100 * from orders (Informix版本必須為11.5以上的版本才能使用SelectPaging)。

SelectPaging的效能分析

為了測試EEP各屬性針對海量資料來源的效能改善，我們實際利用100萬筆資料的資料表來做查詢的動作(欄位數大約15個，Record Size大約300個Bytes)，並分為三種不同的情況做測試，測試結果如下表所示：

屬性設定	花費時間(Sec)
WebDataSet：PacketRecord=-1 (讀取所有資料) InfoCommand：SelectPaging=False	20
WebDataSet：PacketRecord=100(每次讀取100筆) InfoCommand：SelectPaging=False(不使用新的分頁技術)	11
WebDataSet：PacketRecord=100(每次讀取100筆) InfoCommand：SelectPaging=True(使用新的分頁技術)	3

由上述測試結果可得知，EEP所提供新的SelectPaging解決方案，確實可以在讀取海量資料時，節省不少的時間，有效增進系統運作的效能與負載。

結論

由本文可知，透過EEP的SelectPaging新的技術對海量資料讀取可以得到不小的優化，不但可大幅提升運作效能、減少程式執行時間，也可省下許多CPU 和記憶體等系統資源的耗用，間接提升你現有應用系統的使用效能，更令人振奮的是，你只要調整Server端InfoCommand元件的屬性及可得到新的效能，不必重新調整程式的架構與開發，對於高度重視資料效能的客戶，可謂一大福音。