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