當DataGrid遇見(jiàn)100萬(wàn)筆資料
DataGrid遇見(jiàn)100萬(wàn)筆資料會(huì )發(fā)生什么事呢？以下是在藍色小鋪的應答
【作者：奚江華．圣殿祭司】
原問(wèn)題網(wǎng)址:http://www.blueshop.com.tw/board/show.asp?subcde=BRD20050216141103C3Y&fumcde=FUM20041006161839LRJ
效能永遠不夠，所以Performance的議題永遠存在，而原因分為兩個(gè)部分：
1.人為
2.系統（軟件和硬件）
而你的8000筆的資料量的問(wèn)題，剛好同時(shí)Cover到上面兩個(gè)原因。
我有個(gè)ASP.NET網(wǎng)頁(yè)報表軟件，使用MSDE 2000，數據庫檔案大小為900MB，里面有二個(gè)龐大的Table，一個(gè)約1,700,000筆（30多個(gè)字段），另一個(gè)為380,000筆(約15個(gè)字段)，每個(gè)月資料比數以幾十萬(wàn)比的成長(cháng)率在持續增加；在P4-2.8G而內存512MB環(huán)境中執行，每次查詢(xún)會(huì )有3~5萬(wàn)個(gè)Table Cells顯示，但查詢(xún)在一秒內就可以顯示完畢（但這中間是經(jīng)過(guò)許多層次的最佳化和調校才能達成的，注：Table沒(méi)有作Join查詢(xún)）；而和你的8000筆資料+DataGrid對ASP.NET而言絕對不會(huì )有問(wèn)題，只是手法要改一下，不能老是依賴(lài)Default的東西。
但為了為了要和你的問(wèn)題有個(gè)客觀(guān)比對，依你的條件，我首先在我的NB做了同樣的仿真測試，NB測試環(huán)境如下：
1.OS：Windows 2003 Server標準版
2.Visual Studio.NET 2003
3.MS SQL Server 2000標準版
4.CPU：Pentium-M 1.8G
5.內存：512MB
我開(kāi)機后內存使用量為205MB，而有個(gè)數據庫有近160,000筆的資料，字段數則有15個(gè)，我的程序代碼如下：
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個(gè)字段，執行結果分幾點(diǎn)來(lái)說(shuō)明：
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（吃得兇）。
所以你應隱約知道這樣子的系統存在了哪些問(wèn)題了吧？
OK，有了上面的數據樣本我們可以開(kāi)始討論正題了（這樣的測試數據應比你的數據還嚴苛吧），問(wèn)題在哪里呢？
基本上為了厘清你的問(wèn)題，必須切割和隔離成三個(gè)層次，如下：
1.SQL Server層
2.ADO.NET層
3.DataGrid控件層
為何要切割成這三個(gè)層次？因為這三層每個(gè)地方都有問(wèn)題，簡(jiǎn)述如下：
1.SQL Server->沒(méi)有最佳化問(wèn)題
2.ADO.NET->DataSet對大量數據處理的問(wèn)題
3.DataGrid控件->對大量數據處理及分頁(yè)的問(wèn)題
接著(zhù)開(kāi)始探討你所碰到的問(wèn)題：
SQL Server部分的問(wèn)題：
1.SQL Analyzer執行5秒有點(diǎn)久，但如果你做8000多筆多個(gè)Table Join的話(huà)則Maybe合理，但還需進(jìn)一步分析是否有改進(jìn)空間。
2.你用的是View，View上面當然不能加索引（Index），但這樣一來(lái)你Join多個(gè)Table時(shí)，就會(huì )造成效能上的問(wèn)題，速度比起叢集索引Clustered及非叢集索引Non Clustered可能會(huì )慢上3~10倍不等的可能性，其決定于SQL Server的最佳化引擎是否有作用。
3.解決索引問(wèn)題當然不能在View上加，而是要在Join的相關(guān)Table加上索引，而索引建立的原則是依T-SQL查詢(xún)語(yǔ)法的where條件式為依據，通常是Relation的Key上去建立索引；然后你可以去比對有索引和沒(méi)索引的執行秒數，應會(huì )差不少，但最客觀(guān)的是你必須去查看SQL Server的最佳化引擎是如何Parse你的 T-SQL語(yǔ)句，用Execution Plan去分析SQL Server是以何種方式執行你的SQL Server語(yǔ)句，若是用Table Scan執行的表示，你一點(diǎn)也沒(méi)用到索引，若用到任何的索引則表示SQL Server最佳化引擎有在作用。
4.Server的最佳化引擎是否有作用取決于1.你的T-SQL語(yǔ)法后的where條作子句2.你建立的索引對不對？！
Execution Plan圖片參考網(wǎng)址：
ADO.NET的問(wèn)題：
1.在A(yíng)DO.NET之中DataSet是一個(gè)In Memory的Database，也就是存在于內存中的數據庫，一般數據庫中有的基本特性它都有，資料量幾千幾萬(wàn)筆難不倒它，但若幾十萬(wàn)筆～100萬(wàn)筆呢？可能在A(yíng)SP.NET環(huán)境中DataSet就是一個(gè)大問(wèn)題，會(huì )非常的不經(jīng)濟（和ASP.NET、IIS及AP Server運作的方式原理有關(guān)，但在這不深入說(shuō)明），但是它的承載性究竟被設計到多大是一個(gè)未知數？！有空大家可以去測試一下…
2.SqlDataAdapter.Fill(ds)這樣的動(dòng)作，要耗費多少內存空間和運算成本cost？且在你的程序代碼之中，因為ASP.NET”無(wú)狀態(tài)”的特性，若每次DataSet中Fill 8000筆資料，耗費幾十MB~100MB，用完一次就丟棄了，你覺(jué)得能負荷多少人？若以我的經(jīng)驗，如果是DataSet每次用掉100MB內存空間，不到10次就會(huì )Timeout掉了。
3.對DataSet而言，即使你一頁(yè)只看30筆資料，8000筆-30筆=7970筆，對系統資源而言，不但7970筆資料也要吃掉記體不說(shuō)，最后耗費系統成本來(lái)處理這些沒(méi)用的7970筆廢物般的資料，活似個(gè)拖油瓶。
4.DataGrid不分頁(yè)一次顯示的話(huà)會(huì )更慘。
DataGrid的問(wèn)題：
1.DataGrid不論有沒(méi)有做分頁(yè)Page，它都必須先照單全收這8000筆資料，然后再進(jìn)行加工處理，選出這個(gè)Page所需的30筆，你想這樣會(huì )快嗎？等你資料成長(cháng)到16000筆時(shí)，就是從16000筆中選出你分頁(yè)Page要的30筆…會(huì )更慢。從SQL Server->ADO.NET DataSet->DataGrid都在傳送非常不經(jīng)濟且大量的資料，對系統內存、效能與CPU運算有著(zhù)很大的傷害。
2.DataGrid分頁(yè)Page換頁(yè)時(shí)又是什么動(dòng)作？它會(huì )觸發(fā)一個(gè)PageIndexChange的事件，里面又要重作一次Fill(DataSet)的動(dòng)作，又來(lái)一次8000筆的Fill，然后再丟給DataGrid，所以說(shuō)程序怎么會(huì )快得了呢？（在大量資料用DataGrid來(lái)處理）。
3.在Http技術(shù)的Stateless無(wú)狀態(tài)特性下，所有資源，除非你用狀態(tài)管理(Application、Session、ViewState)來(lái)處理、宣告為Static或組件的技術(shù)，否則所有資源用過(guò)后即會(huì )被丟棄，不能再重用了。
4.DataGrid在大量只讀數據時(shí)，對于EnableViewState屬性應設為false，因為ViewState會(huì )耗費大量可觀(guān)的內存，你用DataGrid不分頁(yè)來(lái)一次顯示8000筆你就會(huì )知道true與false兩者的差別了，兩者落差可能會(huì )高達30~60MB不等（對30000~60000個(gè)Table Cell而言）。
解決之道：
首先我必需說(shuō)明這不是ASP.NET或任何系統的錯，純粹是沒(méi)有好的方法來(lái)處理這樣的問(wèn)題，是手法面要改一下！
SQL Server部分：
1.請建立有效索引，無(wú)論是叢集索引或非叢集索引索引，或是復合索引都好，甚至必要時(shí)還要指定索引的升降序，減少不必要的Order By。
2.請用Execution Plan來(lái)確認最佳化引擎有采用到你的索引，where條件子句是最佳化引擎分析的關(guān)鍵。
3.你的5秒在上面兩點(diǎn)作用后，一定要有顯著(zhù)的改進(jìn)，才算解決SQL Server這層的問(wèn)題！
DataGrid及DataSet的問(wèn)題：
1.基本上DataGrid+DataSet在遇見(jiàn)大量資料時(shí)，會(huì )變得氣喘如牛，系統資源鉅幅的耗盡（前面已說(shuō)過(guò)了），所以若資料很大時(shí)，請用top xxx來(lái)限制有效資料量，緩解DataSet及DataGrid的壓力。
2.不能用Top時(shí)，請用T-SQL或Store Procedure來(lái)取回該頁(yè)Page所需的30筆資料即可，這就需要一點(diǎn)加工了：
(1)先計算出總筆數，然后總筆數/30=Page頁(yè)數，再以自訂方式產(chǎn)生DataGrid作頁(yè)碼分頁(yè)用。
(2)依DataGrid目前所在的PageIndex去呼叫資料存取層，也就是只要Fill(DataSet)填入30筆回傳即可。
3.最后記得Connection要close，SqlDataAdapter和DataSet也要Dispose，釋放掉系統資源。
基本上這樣你的系統就會(huì )變得非常的”輕”，速度會(huì )飛馳增加十倍以上都不成問(wèn)題，即使你資料量成長(cháng)到100萬(wàn)筆以上，依然Power！
注：
1.SQL Server 2005就支持了ADO.NET的Page，只取回你Page所需的Row，對系統效能和設計會(huì )有很大的幫助。
2.關(guān)于分頁(yè)只取回該頁(yè)Page及SP的程序代碼，請花點(diǎn)時(shí)間做功課，真的研究不出來(lái)時(shí)再回我，我再Post參考程序代碼給你。
有空參觀(guān)我的Blog吧http://blog.sina.com.tw/weblog.php?blog_id=4907
其實(shí)在這有很多高手，大家各有所長(cháng)，互相交流一下而已，剛好曾經(jīng)花了很多時(shí)間研究大量資料時(shí)，ASP.NET與ADO.NET效能的問(wèn)題，因為我系統再過(guò)一陣子就破200萬(wàn)筆了，順道是測試一下MSDE 2000的能耐，沒(méi)想道竟這么Power，MSDE 2000＋ADO.NET離線(xiàn)式真的可以搭配得很好，又可以說(shuō)免錢(qián)的，還可以用Enterprise Manager來(lái)作管理，真的是省了一筆銀子，把心得公布給大家知道，承蒙各位不棄就好！
分頁(yè)的技巧有兩種，一種是直接透過(guò)T-SQL，另一種是透過(guò)Store Procedure，在這Post出來(lái)跟大家分享一下：
T-SQL：
假設Northwind有一個(gè)Customer的Table，你需要取回41~50筆的記錄，T-SQL語(yǔ)法該如何作呢？
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
以上的東西不藏私和大家分享一下，其實(shí)我相信如果你自己花多點(diǎn)心思說(shuō)不定可以寫(xiě)出比上面更好的算法，算是拋磚引玉啰，有更好的方法希望大家貼出來(lái)一起分享!
