當DataGrid遇見100萬筆資料
DataGrid遇見100萬筆資料會發生什麼事呢？以下是在藍色小舖的應答
【作者：奚江華．聖殿祭司】
原問題網址:http://www.blueshop.com.tw/board/show.asp?subcde=BRD20050216141103C3Y&fumcde=FUM20041006161839LRJ
效能永遠不夠，所以Performance的議題永遠存在，而原因分為兩個部分：
1.人為
2.系統（軟體和硬體）
而你的8000筆的資料量的問題，剛好同時Cover到上面兩個原因。
我有個ASP.NET網頁報表軟體，使用MSDE 2000，資料庫檔案大小為900MB，裡面有二個龐大的Table，一個約1,700,000筆（30多個欄位），另一個為380,000筆(約15個欄位)，每個月資料比數以幾十萬比的成長率在持續增加；在P4-2.8G而記憶體512MB環境中執行，每次查詢會有3~5萬個Table Cells顯示，但查詢在一秒內就可以顯示完畢（但這中間是經過許多層次的最佳化和調校才能達成的，註：Table沒有作Join查詢）；而和你的8000筆資料+DataGrid對ASP.NET而言絕對不會有問題，只是手法要改一下，不能老是依賴Default的東西。
但為了為了要和你的問題有個客觀比對，依你的條件，我首先在我的NB做了同樣的模擬測試，NB測試環境如下：
1.OS：Windows 2003 Server標準版
2.Visual Studio.NET 2003
3.MS SQL Server 2000標準版
4.CPU：Pentium-M 1.8G
5.記憶體：512MB
我開機後記憶體使用量為205MB，而有個資料庫有近160,000筆的資料，欄位數則有15個，我的程式碼如下：
SqlConnection conn=new SqlConnection("data source=.;initial catalog=LK;user id=sa;password="admin");
conn.Open();
SqlDataAdapter da1=new SqlDataAdapter("select top 8000 * from lm1200detail",conn);
DataSet ds=new DataSet();
da1.Fill(ds,"ABC");
conn.Close();
DataGrid1.DataSource=ds.Tables["ABC"].DefaultView;
DataGrid1.DataBind();
da1.Dispose();
ds.Dispose();
以上為8000筆資料，15個欄位，執行結果分幾點來說明：
1.在我的SQL Analyzer中執行row筆數的計算select Top 10000 * from lm1200detail，第一次不計，以第二次以後是SQL Analyzer顯示為0秒，也就是不到1秒。
2.執行ASP.NET的DataGrid顯示後，CPU的使用率立刻爆增到100%，持續了20秒才完成，但DataGrid依然把所有的資料量顯示完。
3.記憶體由205MB爆增到422MB，一共增加了217MB（吃得兇）。
所以你應隱約知道這樣子的系統存在了哪些問題了吧？
OK，有了上面的數據樣本我們可以開始討論正題了（這樣的測試數據應比你的數據還嚴苛吧），問題在哪裡呢？
基本上為了釐清你的問題，必須切割和隔離成三個層次，如下：
1.SQL Server層
2.ADO.NET層
3.DataGrid控制項層
為何要切割成這三個層次？因為這三層每個地方都有問題，簡述如下：
1.SQL Server->沒有最佳化問題
2.ADO.NET->DataSet對大量資料處理的問題
3.DataGrid控制項->對大量資料處理及分頁的問題
接著開始探討你所碰到的問題：
SQL Server部分的問題：
1.SQL Analyzer執行5秒有點久，但如果你做8000多筆多個Table Join的話則Maybe合理，但還需進一步分析是否有改進空間。
2.你用的是View，View上面當然不能加索引（Index），但這樣一來你Join多個Table時，就會造成效能上的問題，速度比起叢集索引Clustered及非叢集索引Non Clustered可能會慢上3~10倍不等的可能性，其決定於SQL Server的最佳化引擎是否有作用。
3.解決索引問題當然不能在View上加，而是要在Join的相關Table加上索引，而索引建立的原則是依T-SQL查詢語法的where條件式為依據，通常是Relation的Key上去建立索引；然後你可以去比對有索引和沒索引的執行秒數，應會差不少，但最客觀的是你必須去查看SQL Server的最佳化引擎是如何Parse你的 T-SQL語句，用Execution Plan去分析SQL Server是以何種方式執行你的SQL Server語句，若是用Table Scan執行的表示，你一點也沒用到索引，若用到任何的索引則表示SQL Server最佳化引擎有在作用。
4.Server的最佳化引擎是否有作用取決於1.你的T-SQL語法後的where條作子句2.你建立的索引對不對？！
Execution Plan圖片參考網址：
DataGrid遇見100萬筆資料會發生什麼事呢？以下是在藍色小舖的應答
【作者：奚江華．聖殿祭司】
原問題網址:http://www.blueshop.com.tw/board/show.asp?subcde=BRD20050216141103C3Y&fumcde=FUM20041006161839LRJ
效能永遠不夠，所以Performance的議題永遠存在，而原因分為兩個部分：
1.人為
2.系統（軟體和硬體）
而你的8000筆的資料量的問題，剛好同時Cover到上面兩個原因。
我有個ASP.NET網頁報表軟體，使用MSDE 2000，資料庫檔案大小為900MB，裡面有二個龐大的Table，一個約1,700,000筆（30多個欄位），另一個為380,000筆(約15個欄位)，每個月資料比數以幾十萬比的成長率在持續增加；在P4-2.8G而記憶體512MB環境中執行，每次查詢會有3~5萬個Table Cells顯示，但查詢在一秒內就可以顯示完畢（但這中間是經過許多層次的最佳化和調校才能達成的，註：Table沒有作Join查詢）；而和你的8000筆資料+DataGrid對ASP.NET而言絕對不會有問題，只是手法要改一下，不能老是依賴Default的東西。
但為了為了要和你的問題有個客觀比對，依你的條件，我首先在我的NB做了同樣的模擬測試，NB測試環境如下：
1.OS：Windows 2003 Server標準版
2.Visual Studio.NET 2003
3.MS SQL Server 2000標準版
4.CPU：Pentium-M 1.8G
5.記憶體：512MB
我開機後記憶體使用量為205MB，而有個資料庫有近160,000筆的資料，欄位數則有15個，我的程式碼如下：
SqlConnection conn=new SqlConnection("data source=.;initial catalog=LK;user id=sa;password="admin");
conn.Open();
SqlDataAdapter da1=new SqlDataAdapter("select top 8000 * from lm1200detail",conn);
DataSet ds=new DataSet();
da1.Fill(ds,"ABC");
conn.Close();
DataGrid1.DataSource=ds.Tables["ABC"].DefaultView;
DataGrid1.DataBind();
da1.Dispose();
ds.Dispose();
以上為8000筆資料，15個欄位，執行結果分幾點來說明：
1.在我的SQL Analyzer中執行row筆數的計算select Top 10000 * from lm1200detail，第一次不計，以第二次以後是SQL Analyzer顯示為0秒，也就是不到1秒。
2.執行ASP.NET的DataGrid顯示後，CPU的使用率立刻爆增到100%，持續了20秒才完成，但DataGrid依然把所有的資料量顯示完。
3.記憶體由205MB爆增到422MB，一共增加了217MB（吃得兇）。
所以你應隱約知道這樣子的系統存在了哪些問題了吧？
OK，有了上面的數據樣本我們可以開始討論正題了（這樣的測試數據應比你的數據還嚴苛吧），問題在哪裡呢？
基本上為了釐清你的問題，必須切割和隔離成三個層次，如下：
1.SQL Server層
2.ADO.NET層
3.DataGrid控制項層
為何要切割成這三個層次？因為這三層每個地方都有問題，簡述如下：
1.SQL Server->沒有最佳化問題
2.ADO.NET->DataSet對大量資料處理的問題
3.DataGrid控制項->對大量資料處理及分頁的問題
接著開始探討你所碰到的問題：
SQL Server部分的問題：
1.SQL Analyzer執行5秒有點久，但如果你做8000多筆多個Table Join的話則Maybe合理，但還需進一步分析是否有改進空間。
2.你用的是View，View上面當然不能加索引（Index），但這樣一來你Join多個Table時，就會造成效能上的問題，速度比起叢集索引Clustered及非叢集索引Non Clustered可能會慢上3~10倍不等的可能性，其決定於SQL Server的最佳化引擎是否有作用。
3.解決索引問題當然不能在View上加，而是要在Join的相關Table加上索引，而索引建立的原則是依T-SQL查詢語法的where條件式為依據，通常是Relation的Key上去建立索引；然後你可以去比對有索引和沒索引的執行秒數，應會差不少，但最客觀的是你必須去查看SQL Server的最佳化引擎是如何Parse你的 T-SQL語句，用Execution Plan去分析SQL Server是以何種方式執行你的SQL Server語句，若是用Table Scan執行的表示，你一點也沒用到索引，若用到任何的索引則表示SQL Server最佳化引擎有在作用。
4.Server的最佳化引擎是否有作用取決於1.你的T-SQL語法後的where條作子句2.你建立的索引對不對？！
Execution Plan圖片參考網址：
ADO.NET的問題：
1.在ADO.NET之中DataSet是一個In Memory的Database，也就是存在於記憶體中的資料庫，一般資料庫中有的基本特性它都有，資料量幾千幾萬筆難不倒它，但若幾十萬筆～100萬筆呢？可能在ASP.NET環境中DataSet就是一個大問題，會非常的不經濟（和ASP.NET、IIS及AP Server運作的方式原理有關，但在這不深入說明），但是它的承載性究竟被設計到多大是一個未知數？！有空大家可以去測試一下…
2.SqlDataAdapter.Fill(ds)這樣的動作，要耗費多少記憶體空間和運算成本cost？且在你的程式碼之中，因為ASP.NET”無狀態”的特性，若每次DataSet中Fill 8000筆資料，耗費幾十MB~100MB，用完一次就丟棄了，你覺得能負荷多少人？若以我的經驗，如果是DataSet每次用掉100MB記憶體空間，不到10次就會Timeout掉了。
3.對DataSet而言，即使你一頁只看30筆資料，8000筆-30筆=7970筆，對系統資源而言，不但7970筆資料也要吃掉記體不說，最後耗費系統成本來處理這些沒用的7970筆廢物般的資料，活似個拖油瓶。
4.DataGrid不分頁一次顯示的話會更慘。
DataGrid的問題：
1.DataGrid不論有沒有做分頁Page，它都必須先照單全收這8000筆資料，然後再進行加工處理，選出這個Page所需的30筆，你想這樣會快嗎？等你資料成長到16000筆時，就是從16000筆中選出你分頁Page要的30筆…會更慢。從SQL Server->ADO.NET DataSet->DataGrid都在傳送非常不經濟且大量的資料，對系統記憶體、效能與CPU運算有著很大的傷害。
2.DataGrid分頁Page換頁時又是什麼動作？它會觸發一個PageIndexChange的事件，裡面又要重作一次Fill(DataSet)的動作，又來一次8000筆的Fill，然後再丟給DataGrid，所以說程式怎麼會快得了呢？（在大量資料用DataGrid來處理）。
3.在Http技術的Stateless無狀態特性下，所有資源，除非你用狀態管理(Application、Session、ViewState)來處理、宣告為Static或元件的技術，否則所有資源用過後即會被丟棄，不能再重用了。
4.DataGrid在大量唯讀資料時，對於EnableViewState屬性應設為false，因為ViewState會耗費大量可觀的記憶體，你用DataGrid不分頁來一次顯示8000筆你就會知道true與false兩者的差別了，兩者落差可能會高達30~60MB不等（對30000~60000個Table Cell而言）。
解決之道：
首先我必需說明這不是ASP.NET或任何系統的錯，純粹是沒有好的方法來處理這樣的問題，是手法面要改一下！
SQL Server部分：
1.請建立有效索引，無論是叢集索引或非叢集索引索引，或是複合索引都好，甚至必要時還要指定索引的升降冪，減少不必要的Order By。
2.請用Execution Plan來確認最佳化引擎有採用到你的索引，where條件子句是最佳化引擎分析的關鍵。
3.你的5秒在上面兩點作用後，一定要有顯著的改進，才算解決SQL Server這層的問題！
DataGrid及DataSet的問題：
1.基本上DataGrid+DataSet在遇見大量資料時，會變得氣喘如牛，系統資源鉅幅的耗盡（前面已說過了），所以若資料很大時，請用top xxx來限制有效資料量，緩解DataSet及DataGrid的壓力。
2.不能用Top時，請用T-SQL或Store Procedure來取回該頁Page所需的30筆資料即可，這就需要一點加工了：
(1)先計算出總筆數，然後總筆數/30=Page頁數，再以自訂方式產生DataGrid作頁碼分頁用。
(2)依DataGrid目前所在的PageIndex去呼叫資料存取層，也就是只要Fill(DataSet)填入30筆回傳即可。
3.最後記得Connection要close，SqlDataAdapter和DataSet也要Dispose，釋放掉系統資源。
基本上這樣你的系統就會變得非常的”輕”，速度會飛馳增加十倍以上都不成問題，即使你資料量成長到100萬筆以上，依然Power！
註：
1.SQL Server 2005就支援了ADO.NET的Page，只取回你Page所需的Row，對系統效能和設計會有很大的幫助。
2.關於分頁只取回該頁Page及SP的程式碼，請花點時間做功課，真的研究不出來時再回我，我再Post參考程式碼給你。
有空參觀我的Blog吧http://blog.sina.com.tw/weblog.php?blog_id=4907
其實在這有很多高手，大家各有所長，互相交流一下而已，剛好曾經花了很多時間研究大量資料時，ASP.NET與ADO.NET效能的問題，因為我系統再過一陣子就破200萬筆了，順道是測試一下MSDE 2000的能耐，沒想道竟這麼Power，MSDE 2000＋ADO.NET離線式真的可以搭配得很好，又可以說免錢的，還可以用Enterprise Manager來作管理，真的是省了一筆銀子，把心得公布給大家知道，承蒙各位不棄就好！
分頁的技巧有兩種，一種是直接透過T-SQL，另一種是透過Store Procedure，在這Post出來跟大家分享一下：
T-SQL：
假設Northwind有一個Customer的Table，你需要取回41~50筆的記錄，T-SQL語法該如何作呢？
Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers where CustomerID Not in
(Select top 40 CustomerID from Customers order by Country,CustomerID)
Order by Country,CustomerID
Store Procedure：
出自MSDN Magazine，是別人的智慧
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
以上的東西不藏私和大家分享一下，其實我相信如果你自己花多點心思說不定可以寫出比上面更好的演算法，算是拋磚引玉囉，有更好的方法希望大家貼出來一起分享!
【作者：奚江華．聖殿祭司】
本文引用網址:http://blog.sina.com.tw/dotnet/article.php?pbgid=4907&entryid=3921
全站熱搜
留言列表
