關於Net開發中一些SQLServer效能最佳化的建議

標籤：tle   簡單   rip   convert   基本   應用   dispose   設計   keyword   

一、 ExecuteNonQuery和ExecuteScalar

對資料的更新不需要返回結果集，建議使用ExecuteNonQuery。由於不返回結果集可省掉網路資料轉送。它僅僅返回受影響的行數。如果只需更新資料用ExecuteNonQuery效能的開銷比較小。

ExecuteScalar它只返回結果集中第一行的第一列。使用 ExecuteScalar 方法從資料庫中檢索單個值（例如id號）。與使用 ExecuteReader 方法， 返回的資料執行產生單個值所需的操作相比，此操作需要的代碼較少。

只需更新資料用ExecuteNonQuery.單個值的查詢使用ExecuteScalar 。

二、 SqlDataRead和Dataset的選擇

Sqldataread優點：讀取資料非常快。如果對返回的資料不需做大量處理的情況下，建議使用SqlDataReader，其效能要比datset好很多。缺點：直到資料讀完才可close掉於資料庫的串連 。(SqlDataReader 讀資料是快速向前的。SqlDataReader 類提供了一種讀取從 SQL Server 資料庫檢索的只進資料流的方法。它使用 SQL Server 的本機網路資料轉送格式從資料庫連接直接讀取資料。DataReader需及時顯式的close。可及時的釋放對資料的串連。)

Dataset是把資料讀出，緩衝在記憶體中。缺點：對記憶體的佔用較高。如果對返回的資料需做大量的處理用Dataset比較好些可以減少對資料庫的串連操作。優點：只需串連一次就可close於資料庫的串連。

一般情況下,讀取大量資料,對返回資料不做大量處理用SqlDataReader.對返回資料大量處理用datset比較合適.對SqlDataReader和Dataset的選擇取決於程式功能的實現。

資料繫結的選擇

三、 資料的綁定DataBinder

一般的Binder 方法<%# DataBinder.Eval(Container.DataItem, "欄位名") %> 用DataBinder.eval 綁定不必關心資料來源(Dataread或dataset)。不必關心資料的類型eval會把這個資料對象轉換為一個字串。在底層綁定做了很多工作，使用了反射效能。正因為使用方便了，但卻影響了資料效能。

來看下<%# DataBinder.Eval(Container.DataItem, "欄位名") %>。當於dataset綁定時，DataItem其實式一個DataRowView（如果綁定的是一個資料讀取器（dataread）它就是一個IdataRecord。）因此直接轉換成DataRowView的話，將會給效能帶來很大提升。.

<%# ctype(Container.DataItem,DataRowView).Row("欄位名") %>

對資料的綁定建議使用<%# ctype(Container.DataItem,DataRowView).Row("欄位名") %>。資料量大的時候可提高几百倍的速度。使用時注意2方面：1.需在頁面添加<%@ Import namespace="System.Data"%>.2.注意欄位名的大小寫（要特別注意）。如果和查詢的不一致，在某些情況下會導致比<%# DataBinder.Eval(Container.DataItem, "欄位名") %>還要慢。如果想進一步提高速度，可採用<%# ctype(Container.DataItem,DataRowView).Row(0) %>的方法。不過其可讀性不高。

以上的是vb.net的寫法。在c#中：

<@% ((DataRowView)Container.DataItem)["欄位名"] %>

一、 應用Ado.net的一些思考原則

1. 選擇適當的事務類型

2. 使用預存程序

3. 根據資料使用的方式來設計資料訪問層

4. 必要時申請，儘早釋放

5. 快取資料，避免不必要的操作

6. 使用服務帳戶進行串連

7. 減少往返

8. 僅返回需要的資料

9. 關閉可關閉的資源

二、 Connection

資料庫連接是一種共用資源，並且開啟和關閉的開銷較大。Ado.net預設啟用了串連池機制，關閉串連不會真的關閉物理串連，而只是把串連放回到串連池中。因為池中共用的串連資源始終是有限的，如果在使用串連後不儘快關閉串連，

那麼就有可能導致申請串連的線程被阻塞住，影響整個系統的效能表現。

1、在方法中開啟和關閉串連

這個原則有幾層含義：

1）主要目的是為了做到必要時申請和儘早釋放

2）不要在類的建構函式中開啟串連、在解構函式中釋放串連。因為這將依賴於記憶體回收，而記憶體回收只受記憶體影響，

回收時機不定

3）不要在方法之間傳遞串連，這往往導致串連保持開啟的時間過長

這裡強調一下在方法之間傳遞串連的危害：曾經在壓力測試中遇到過一個測試案例，當增大使用者數的時候，這個案例要比別

的案例早很久就用掉串連池中的所有串連。經分析，就是因為A方法把一個開啟的串連傳遞到了B方法，而B方法又調用了一個

自行開啟和關閉串連的C方法。在A方法的整個運行期間，它至少需要佔用兩條串連才能夠成功工作，並且其中的一條串連佔用時間還特別長，所以造成串連池資源緊張，影響了整個系統的延展性！

2、顯式關閉串連

Connection對象本身在記憶體回收時可以被關閉，而依賴記憶體回收是很不好的策略。推薦使用using語句顯式關閉串連，如下例：

using (SqlConnection conn =new SqlConnection(connString)) { conn.Open(); } // Dispose is automatically called on the conn variable here

3、確保串連池啟用

Ado.net是為每個不同的串連串建立串連池，因此應該確保串連串不會出現與具體使用者相關的資訊。另外，要注意串連串是

大小寫敏感的。

4、不要緩衝串連

例如，把串連緩衝到Session或Application中。在啟用串連池的情況下，這種做法沒有任何意義。

三、Command

1、 使用ExecuteScalar和ExecuteNonQuery

如果想返回像Count(*)、Sum(Price)或Avg(Quantity)那樣的單值，可以使用ExecuteScalar方法。ExecuteScalar返回第一行第一列的值，將結果集作為標量值返回。因為單獨一步就能完成，所以ExecuteScalar不僅簡化了代碼，還提高了效能。

使用不返回行的SQL語句時，例如修改資料（INSERT、UPDATE或DELETE）或僅返回輸出參數或傳回值，請使用ExecuteNonQuery。這避免了用於建立空DataReader的任何不必要處理。

2、使用Prepare

當需要重複執行同一SQL語句多次，可考慮使用Prepare方法提升效率。需要注意的是，如果只是執行一次或兩次，則完全沒有必要。例如：

cmd.CommandText ="insert into Table1 ( Col1, Col2 ) values ( @val1, @val2 )"; cmd.Parameters.Add( "@val1", SqlDbType.Int, 4, "Col1" ); cms.Parameters.Add( "@val2", SqlDbType.NChar, 50, "Col2"); cmd.Parameters[0].Value =1; cmd.Parameters[1].Value ="XXX"; cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value =2; cmd.Parameters[1].Value ="YYY"; cmd.ExecuteNonQuery(); cmd.Parameters[0].Value =3; cmd.Parameters[1].Value ="ZZZ"; cmd.ExecuteNonQuery();

3、使用綁定變數

SQL語句需要先被編譯成執行計畫，然後再執行。如果使用綁定變數的方式，那麼這個執行計畫就可以被後續執行的SQL語句所複用。而如果直接把參數合并到了SQL語句中，由於參數值千變萬化，執行計畫就難以被複用了。例如上面Prepare一節給出的樣本，如果把參數值直接寫到insert語句中，那麼上面的四次調用將需要編譯四次執行計畫。

為避免這種情況造成效能損失，要求一律使用綁定變數方式。

四、 DataReader

DataReader最適合於訪問唯讀單向資料集。與DataSet不同，資料集並不全部在記憶體中，而是隨不斷髮出的read請求，一旦探索資料緩衝區中的資料均被讀取，則從資料來源傳輸一個資料緩衝區大小的資料區塊過來。另外，DataReader保持串連，DataSet則與串連斷開。

1、 顯式關閉DataReader

與串連類似，也需要顯式關閉DataReader。另外，如果與DataReader關聯的Connection僅為DataReader服務的話，可考慮使用Command對象的ExecuteReader(CommandBehavior.CloseConnection)方式。這可以保證當DataReader關閉時，同時自動關閉Connection。

2、用索引號訪問代替名稱索引號訪問屬性

從Row中訪問某列屬性，使用索引號的方式比使用名稱方式有細微提高。如果會被頻繁調用，例如在迴圈中，那麼可考慮此類最佳化。樣本如下：

cmd.CommandText ="select Col1, Col2 from Table1" ; SqlDataReader dr = cmd.ExecuteReader(); int col1 = dr.GetOrdinal("Col1"); int col2 = dr.GetOrdinal("Col2"); while (dr.Read()) { Console.WriteLine( dr[col1] +"_"+ dr[col2]); }

3、使用類型化方法訪問屬性

從Row中訪問某列屬性，用GetString、GetInt32這種顯式指明類型的方法，其效率較通用的GetValue方法有細微提高，因為不需要做類型轉換。

4、使用多資料集

部分情境可以考慮一次返回多資料集來降低網路互動次數，提升效率。樣本如下：

cmd.CommandText ="StoredProcedureName"; // The stored procedure returns multiple result sets.SqlDataReader dr = cmd.ExecuteReader(); while (dr.read()) // read first result set dr.NextResult(); while (dr.read())

五、DataSet

1、 利用索引加快尋找行的效率

如果需要反覆尋找行，建議增加索引。有兩種方式：

1）設定DataTable的PrimaryKey

適用於按PrimaryKey尋找行的情況。注意此時應調用DataTable.Rows.Find方法，一般慣用的Select方法不能利用索引。

2）使用DataView

適用於按Non-PrimaryKey尋找行的情況。可為DataTable建立一個DataView，並通過SortOrder參數指示建立索引。此後使用Find或FindRows尋找行。

一、減少往返行程（Reduce Round Trips）

使用下面的方法可以減少Web伺服器和Browser之間的往返行程：

1、為Browser啟用緩衝

如果呈現的內容是靜態或變化周期較長，應啟用Browser緩衝，避免發出冗餘的http請求。

2、緩衝頁面輸出

如果可能，則盡量緩衝頁面輸出，處理結束後再一次傳送到用戶端，這可以避免頻繁傳遞小塊內容所造成的多次網路互動。由於這種方式在頁面處理結束之前用戶端無法看到頁面內容，因此如果一個頁面的尺寸較大的話，可考慮使用Response.Flush方法。該方法強制輸出迄今為止在緩衝區中的內容，你應當採用合理的演算法控制調用Response.Flush方法的次數。

3、使用Server.Transfer重新導向請求

使用Server.Transfer方法重新導向請求優於Response.Redirect方法。原因是Response.Redirect會向Broswer回送一個回應標頭，在回應標頭中指出重新導向的URL，之後Brower使用新的URL重新發出請求。而Server.Transfer方法直接是一個簡單的服務端調用，完全沒有這些開銷！

需要注意Server.Transfer有局限性：第一，它會跳過安全檢查；第二，只適用於在同一Web應用內的頁面間跳轉。

二、避免阻塞和長時間的作業

如果需要運行阻塞或長時間啟動並執行操作，可以考慮使用非同步呼叫的機制，以便Web伺服器能夠繼續處理其它的請求。

1、使用非同步方式調用Web服務和遠程對象

只要有可能就要避免在請求的處理過程中對Web服務和遠程對象的同步調用，因為它佔用的是的ASP.NET 線程池中的背景工作執行緒，這將直接影響Web伺服器響應其它請求的能力。

2、考慮給不需要傳回值的Web方法或遠程對象的方法添加OneWay屬性

這種模式能讓Web Server調用之後就立即返回。可根據實際情況決定是否使用這種方法。

3、使用工作隊列

將作業提交到伺服器上的工作隊列中。用戶端通過發送請求來輪詢作業的執行結果。

三、 使用緩衝

緩衝能在很大程度上決定ASP.NET應用的最終效能。Asp.net支援網頁輸出快取和頁面部分緩衝，並提供Cache API，供應用程式緩衝自己的資料。是否使用緩衝可考慮下面的要點：

1、識別建立與訪問代價較大的資料

2、評估需要快取資料的易變性

3、評估資料的使用頻次

4、 將要快取資料中易變資料和不變資料分離，只緩衝不變資料

5、選擇合適的緩衝機制（除Asp.net Cache外，Application state和Session state也可以作為緩衝使用）

四、 系統資源

1、考慮實現資源集區以提升效能

2、明確地調用Dispose或Close釋放系統資源

3、不要緩衝或長時間佔用資源集區中的資源

4、儘可能晚的申請，儘可能早的釋放

五、多線程

1、避免在請求處理過程中建立線程

在執行請求的過程中建立線程是一種代價較大的操作，會嚴重影響Web Server的效能。如果後續的操作必須用線程完成，建議通過thread pool來建立/管理線程。

2、避免阻塞處理請求的線程

3、避免非同步呼叫

這和1的情況類似。非同步呼叫會導致建立新的線程，增加伺服器的負擔。所以，如果沒有並發的作業要執行，就不要執行非同步呼叫。

4、不要依賴線程資料槽或線程靜態變數

由於執行請求的線程是ASP.NET thread pool中的背景工作執行緒，同一個Client的兩次請求不一定由相同的線程來處理。

六、 頁面處理

1、盡量減小Page的尺寸

包括縮短控制項的名稱、CSS的class的名稱、去掉無謂空行和空格、禁用不需要的ViewState

2、最佳化複雜和代價較大的迴圈

3、合理利用用戶端的計算資源，將一些操作轉移到用戶端進行

4、啟用頁面輸出的緩衝區（Buffer）

如果Buffer的機制被關閉，可以用下面的方法開啟。

使用程式開啟網頁輸出快取：

Response.BufferOutput = true;<%@ Page Buffer = "true" %>

使用Web.config或Machine.config設定檔的<pages>節點：

<pages buffer="true" …>

5、利用Page.IsPostBack最佳化頁面輸出

6、通過分離頁面的不同的內容，來提高緩衝效率和減少呈現的時間

七、 ViewState

ViewState是Asp.net為服務端控制項在頁面回傳之間跟蹤狀態資訊而設計的一種機制。

1、在恰當的時間點初始化控制項屬性

ASP.NET的控制項在執行建構函式、初始化的期間設定的屬性不會被跟蹤變化；而在初始化階段之後對屬性的修改都會被跟蹤，並最終記錄到IE頁面的__VIEWSTATE之中。所以，選擇合理的初始化控制項屬性的執行點，能有效減小頁面尺寸。

2、謹慎選擇放到ViewState中的內容

放到ViewState中的內容會被序列化/還原序列化，Asp.net為String、Integer、Boolean等基本類型的序列化做了最佳化，如果Array、ArrayList、HashTable儲存的是基本類型效率也較高，但其它類型則需要提供類型轉換器（Type Converter），否則將使用代價昂貴的二進位序列化程式。

3、 關閉ViewState

如果不需要跟蹤頁面狀態，例如頁面不會 回傳（PostBack）、不需要處理服務端控制項事件或者每次頁面重新整理時都會重新計算控制項內容，那麼就不需要用ViewState來記錄頁面狀態了。可以對特定的WebControl設定EnableViewState屬性，也可以在頁面一級設定：

<%@ Page EnableViewState="false" %>

歡迎關注我的公眾號（同步更新文章）：DoNet技術分享平台
閱讀原文

關於Net開發中一些SQLServer效能最佳化的建議