手把手教你提升SQL性能
SQL和.NET都是微軟旗下的產品，它們延續了微軟的一貫風格，它們的性能是相互影響的，不可分割的，所以要優化SQL的性能，除了SQL可以注意的一些問題外，還可以從.NET程序優化做起。
SQL性能參數
響應時間：在數據請求和數據返回這個過程中經歷的時間。
吞吐量：在一段時間內從發送程序向接收程序傳輸的數據的數量。
可伸縮性：當同時訪問數據庫的用戶數量增加時，應用程序維持可以接受的響應時間和吞吐量的能力。
編寫SQL代碼原則
減少網絡通信量
限制磁盤I/O
優化應用程序和驅動程序之間的交互
簡化請求
NET程序優化要點
使用連接池。
連接池是應用程序能夠重復使用的一個或多個數據庫連接的高速緩存。
創建一個新的數據庫連接所耗費的時間主要取決于網絡的速度以及應用程序和數據庫服務器的（網絡）距離，而且這個過程通常是一個很耗時的過程。而采用數據庫連接池后，數據庫連接請求可以直接通過連接池滿足而不需要為該請求重新連接、認證到數據庫服務器，減少建立物理連接所需要的相關資源開銷，這樣就節省了時間。
適用：
應用程序運行在應用服務器上時。因為這意味著多個用戶會同時使用應用程序。
應用程序具有多個用戶，并且數據庫服務器具有足夠的內存管理***數量的連接。
不適用：
應用程序每天重新啟動很多次。每次啟動會在連接池中建立一些連接，這會降低性能。
單用戶應用程序。
對于.NET應用程序而言，默認為允許連接池。當然，如果你可以在SQLConnection對象的連接字符串中加進Pooling=false禁止連接池的使用。
高效的建立和斷開連接。
因為建立連接需要耗費內存，如果待垃圾收集器清除不再使用的連接，連接占用內存的時間比所需要的時間更長，從而削弱應用程序的性能。
關閉事務的自動提交。
使用tran.Commit()方法進行手動提交，因為每個提交操作都會耗費大量的磁盤I/O，并且需要在驅動程序和數據庫服務器之間進行額外的網絡往返，使用手動提交，應用程序可以控制何時提交數據庫工作，從而顯著提高系統的性能。
如果需要多次執行SQL語句，可以使用cmd.Prepare()進行預編譯，但如果只是執行一次SQL，則沒有必要，因為沒有預先編譯的Command對象只需要一次網絡往返，而編譯的Command對象需要兩次（一次往返用于解析和優化語句，一次往返用于執行語句并檢索結果）。
當更新大量數據時，為了減少網絡往返次數，可以使用參數數組或批處理取代預編譯。
- string sql = “INSERT INTO employees VALUES (? , ?)”;
- cmd.CommandText = sql;
- cmd.Prepare();
- for(int i = 0 ; i<100 ; i++)
- {
- cmd.Parameters[0].Value = id[i];
- cmd.Parameters[1].Value = name[i];
- cmd.ExecuteNonQuery();
- }
上面的代碼演示了使用預編譯的Command對象執行一條Insert語句多次的情況，這種情況下，為了執行100次插入操作，需要101次網絡往返， 1次用于準備語句，另外100次用于執行迭代操作。
- string sql = “INSERT INTO employees VALUES (? , ?)”;
- cmd.CommandText = sql;
- cmd.ArrayBindCount = 10;
- cmd.Prepare();
- cmd.Parameters[0].Value = idArray;
- cmd.Parameters[1].Value = nameArray;
- cmd.ExecuteNonQuery();
將Command對象的CommandText屬性設置為一個包含一條Insert語句和一個參數數組的字符串，這種方法只需要兩次網絡往返，一次用于準備語句，另外一次用于執行數組。
- string sql = “INSERT INTO employees VALUES (? , ?)”+
- “INSERT INTO employees VALUES (? , ?)”+
- …
- “INSERT INTO employees VALUES (? , ?)”
- cmd.CommandText = sql;
- cmd.Prepare();
- cmd.Parameters[0].Value = id[0];
- cmd.Parameters[1].Value = name[0];
- …
- cmd.ExecuteNonQuery();
以上代碼使用批處理的方式。
如果應用程序更新斷開連接的DataSet，并且數據提供程序支持批處理，可以通過設置DataAdapter對象的UpdateBatchSize屬性優化性能。設置這個屬性指定與數據庫服務器的網絡往返次數，從而優化性能：
- view sourceprint?SqlDataAdapter sda = new SqlDataAdapter();
- sda.InsertCommand = cmd;
- sda.UpdateatchSize = 5; //告訴數據提供程序打包5條命令，并在一次網絡往返中將它們發送到數據庫
避免使用CommandBuilder對象
因為并發操作的限制，CommandBuilder經常生成效率低下的SQL語句，此外CommandBuilder對象在運行時生成語句，每次調用DataAdapter.Update方法時，CommandBuilder都會分析結果集的內容，并為DataAdapter生成Insert/Update/Delete語句，當顯示指定了Insert/Update/Delete語句時，就不需要這個額外的處理步驟。
如果需要檢索大量只讀數據，選擇DataReader，如果需要插入、更新或刪除數據，以任何順序返回數據，才使用DataSet對象。雖然DataSet靈活，但是隨之而來的是它要消耗大量的內存。
DataReader對象針對快速檢索大量數據進行了優化，數據時只讀的，并且只能以向前的順序讀取數據，內存使用量最小。DataSet對象是代表整個數據結果集的數據緩存，可以修改DataSet中的數據，并可以使用任意順序獲取數據。
使用GetXXX方法從DataReader獲取數據
通用的方法是GetValue（）和GetValues（），但需要額外的處理將值數據類型裝換為引用數據類型，為了避免裝箱，可以使用特性的如GetInt32（）方法避免此問題。
在DataSet中搜索數據
在一個數據集中查詢符合特定條件的行時，使用基于索引（index-based）的查看表將提高性能。給數據表指定主鍵（PrimaryKey）值時，就建立了一個索引。
·如果查詢是在數據表的主鍵列上進行的，使用DataTable.Rows.Find代替DataTable.Select。
·查詢非主鍵列，可以使用數據視圖來提高多個數據查詢的速度。當給數據視圖添加排序時，將建立搜索時使用的索引，數據視圖暴露了查詢下層數據表的Find和FindRows方法。
·如果你不是查詢表的排序視圖，也可以通過為數據表建立數據視圖獲得基于索引的查看表的好處。注意如果你執行數據上的多個查詢這是唯一的好處。如果你只執行單個查詢，需要建立索引的過程將因為使用索引而降低了性能。
總結
SQL和.NET性能相互影響，所以兩方的性能優化都做好了，才是王道。
【編輯推薦】
