[C#][SQL SERVER] 提高 Insert 效能
前幾天朋友詢問如何提高 Insert 效能，我先簡單描述該需求。
1.使用者上傳 Excel 檔案 (Use Web Browser)
2.檢查比對 Excel 每筆資料內容是否符合商業規則
3.將 Excel 資料新增到SQL2008(需使用交易)
未修改的程式邏輯寫法
1.使用 NPOI 將 Excel 轉換為 DataTable
2.逐筆檢驗比對 DataRow 內容是否符合商業規則，
只要符合就使用 Ado.net 並開啟交易新增該筆資料(Use TSQL)。
但使用者常常抱怨只要上傳筆數一多(大約超過3萬筆)，
網頁執行就相當緩慢，使用者還沒等待網頁秀出資料新增完成訊息，
往往就會直接關閉網頁導致該程式的交易未 commit ，
有時造成 Table Lock 並Block其他查詢該資料表需求的處理，
所以他得常常去資料庫 Kill 這些不良交易處理的session，
然後在請使用者分批上傳檔案(減少筆數)，朋友希望我能夠針對 Insert 這部份效能來處理看看，
要處理 Insert效能前，當然得先看一下影響 Insert 效能有那些(如下圖)。
擷取BOL。
我很幸運，因為該資料表沒有任何約束和觸發程式，索引數量有只有3個，
算是很單存的資料表，我這裡先大概模擬原有程式的新增寫法。
Table Layout
CREATE table Test
(
c1 int primary key,
c2 varchar(30),
c3 datetime default getdate(),
c4 bit default 0,
c5 real
)
模擬30000筆資料(真實世界資料是沒有經過排序的)
static DataTable GetDataFromXls()
{
DataTable result = new DataTable();
result.Columns.Add(new DataColumn("c1", typeof(int)));
result.Columns.Add(new DataColumn("c2", typeof(String)));
result.Columns.Add(new DataColumn("c3", typeof(DateTime)));
result.Columns.Add(new DataColumn("c4", typeof(Boolean)));
result.Columns.Add(new DataColumn("c5", typeof(float)));
for (int i = 30000; i >= 1; i--)
{
result.Rows.Add(new object[] { i, "rico", DateTime.Now, true, 1017.1219 });
}
return result;
}
檢驗比對每筆資料，通過就新增該筆資料到資料庫
static void InsertV1(DataTable srcdata)
{
using (SqlConnection conn = new SqlConnection(myconnection))
{
conn.Open();
string mysql=@"INSERT INTO Test (c1,c2,c3,c4,c5)
VALUES (@c1,@c2,@c3,@c4,@c5)";
SqlTransaction trans = conn.BeginTransaction();
//逐筆檢驗 逐筆新增
foreach (DataRow dr in srcdata.Rows)
{
//檢驗比對資料內容是否正確
//如c2 ==aa then c4=false...等
if (1 == 1)
{
using (SqlCommand cmd = new SqlCommand(mysql, conn))
{
cmd.Transaction = trans;
cmd.Parameters.AddWithValue("@c1", dr["c1"]);
cmd.Parameters.AddWithValue("@c2", dr["c2"]);
cmd.Parameters.AddWithValue("@c3", dr["c3"]);
cmd.Parameters.AddWithValue("@c4", dr["c4"]);
cmd.Parameters.AddWithValue("@c5", dr["c5"]);
cmd.ExecuteNonQuery();
}
}
}
trans.Commit();
}
}
執行寫法1
static void Main(string[] args)
{
DataTable dt = GetDataFromXls(); //30000 rows
//DataView dv = dt.DefaultView;
//dv.Sort = "c1 asc";
//DataTable dtsort = dv.ToTable();
Stopwatch sw = new Stopwatch();
sw.Reset();
sw.Start();
InsertV1(dt); // using tsql
//InsertV2(dtsort); // using store procedure
//InsertV3(dtsort); //using bulk insert
sw.Stop();
Console.WriteLine("花費時間(ms):" + sw.ElapsedMilliseconds.ToString());
Console.ReadLine();
}
5次執行時間如下
5次花費時間平均(ms): 2729.6
DB 角度看如何提高 Insert 效能
1.減少 Page Splits 發生頻率。
為了減少 Page Splits發生頻率，當你在新增資料時，請依照 Clustered Index 順序來處理相關排序，
如果 Page Splits依然很高，可以嘗試調整索引填滿因子(Fill Factor) 設定為85、80、75..等，
但是 Fill Factor 設定太低會導致索引碎片過多，很多空間未使用(浪費I/O)，
該值預設是0(0和100效果相同)，所以調整該值一定要經過多次測試和觀察，
才可在查詢和新增效能之間取得一個平衡。
2.移除無用索引。
這點不必多說，索引越多一定會影響新增效能，DBA需要取得一個平衡。
3.避免長時間交易。
交易時間應儘可能越短越好，交易應避免包含驗證邏輯處理，
應避免單一交易處理過多資料量，且將大交易量分解成數個小交易量。
4.資料檔案(data file *.mdf)和交易記錄檔(log file *.ldf)分開儲存
避免 I/O上的爭用，交易記錄檔最好可以存放在 RAID 1+0 或 RAID 1。
5.避免自動成長
設定合適的交易記錄檔大小，避免自動成長。
假設你新增的資料需要2GB空間，那得先確認交易記錄檔還有3GB以上未使用空間，
避免新增作業執行時，遭遇交易記錄檔空間不夠，浪費額外時間來處理擴充分配空間给SQL Server。
6.注意可變動資料類型
能夠用char(5)就不要使用varchar(5)。
這是因為 varchar 類型是SQL Server必要的處理，
每次當你新增一筆含有可變動資料類型的內容時，
SQL Server 會自動幫你處理該內容字串長度、資料列紀錄...等，
而這些也是要花時間成本的。
7.避免長時間的Table Lock
資料庫是服務多人的，所以資料庫存在Lock也是很正常的，
因為有了Lock機制才能確保每個人所取得的資料是正確的。
而避免長時間的Table Lock(SQL Server預設是row lock)是因為會封鎖(block)其他人的查詢該資料表的處理，
SQL2005後新增了鎖定擴大特性(降低記憶體使用量)，
這特性我個人覺得相當不錯，但無論如何還是要避免長時間的Table Lock。
8.not null and default values
資料行有指定預設值，那將無可避免拉長新增作業處理時間，
如果你很肯定該資料表的新增作業次數遠大於查詢作業次數的話，
建議可以移除 default values並設定 null，因為查詢 null 資料行會有額外的效能花費。
note:如果執行 update、delete作業，請先確定有相對應正確索引。
AP 角度看如何提高 Insert 效能(.NET 環境為例)
1.使用Connection Pool
建立Connection是一項耗時作業，所以重複利用Connection是相當重要，
所以請務必啟用 Connection Pool。
2.使用 Store Procedure
利用 Store Procedure 重用執行計畫，省下SQL Server Compile過程時間。
3.使用參數化
使用參數化不僅可以預防SQL Injection，也可以重用執行計畫，
我想唯一的麻煩就是開發人員需要寫比較多的程式碼。
4.使用批次來處理新增作業
這也算是常識了，但交易資料量龐大的話請記得觀察Table Lock的影響(大交易拆成數個小交易)。
5.交易記得處理 commit or rollback
開發人員沒處理的話....應該可以去跳淡水河了....XD
實際資料庫調整
資料庫方面礙於該公司某些因素，
所以我只能調整交易記錄檔大小和自動成長大小，
而這也是很常見的情況(我ㄧ點都不意外...XD)。
實際AP程式調整
1.使用 NPOI 將 Excel 轉換為 DataTable且依 clustered index 排序資料
2.逐筆檢驗比對 DataRow 內容是否符合商業規則，並和交易新增作業分離。
3.將檢驗通過的資料使用批次新增處理
這裡我先測試使用SP的效能
CREATE PROC usp_insertByTest
@c1 int,@c2 varchar(30),@c3 datetime,@c4 bit,@c5 real
as
INSERT INTO Test (c1,c2,c3,c4,c5)
VALUES (@c1,@c2,@c3,@c4,@c5)
寫法2
static void InsertV2(DataTable srcdata)
{
using (SqlConnection conn = new SqlConnection(myconnection))
{
conn.Open();
string mysql = @"usp_insertByTest";
SqlTransaction trans = conn.BeginTransaction();
//逐筆新增 沒有檢驗比對處理
foreach (DataRow dr in srcdata.Rows)
{
using (SqlCommand cmd = new SqlCommand(mysql, conn))
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@c1", dr["c1"]);
cmd.Parameters.AddWithValue("@c2", dr["c2"]);
cmd.Parameters.AddWithValue("@c3", dr["c3"]);
cmd.Parameters.AddWithValue("@c4", dr["c4"]);
cmd.Parameters.AddWithValue("@c5", dr["c5"]);
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
}
執行寫法2
static void Main(string[] args)
{
DataTable dt = GetDataFromXls(); //30000 rows
DataView dv = dt.DefaultView;
dv.Sort = "c1 asc"; //依 clustered index 排序
DataTable dtsort = dv.ToTable();
Stopwatch sw = new Stopwatch();
sw.Reset();
sw.Start();
//InsertV1(dt); // using tsql
InsertV2(dtsort); // using store procedure
//InsertV3(dtsort); //using bulk insert
5次執行時間如下
5次花費時間平均(ms): 2513
可以看到使用SP來處理 Insert 作業，雖然效能有所改善，但可惜改善幅度沒有很大。
使用批次處理新增作業
create type utype_Test as table
(
c1 int primary key,
c2 varchar(30),
c3 datetime default getdate(),
c4 bit default 0,
c5 real
)
CREATE proc usp_insertByTestType
@mytable utype_Test READONLY
as
INSERT INTO Test (c1,c2,c3,c4,c5)
SELECT c1,c2,c3,c4,c5
from @mytable
寫法3
static void InsertV3(DataTable srcdata)
{
using (SqlConnection conn = new SqlConnection(myconnection))
{
conn.Open();
string mysql = @"usp_insertByTestType";
SqlTransaction trans = conn.BeginTransaction();
//批次處理新增 沒有檢驗比對處理
using (SqlCommand cmd = new SqlCommand(mysql, conn))
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@mytable", SqlDbType.Structured);
param.Value = srcdata;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
執行寫法3
DataTable dt = GetDataFromXls(); //30000 rows
DataView dv = dt.DefaultView;
dv.Sort = "c1 asc"; //依 clustered index 排序
DataTable dtsort = dv.ToTable();
Stopwatch sw = new Stopwatch();
sw.Reset();
sw.Start();
//InsertV1(dt); // using tsql
//InsertV2(dtsort); // using store procedure
InsertV3(dtsort); //using bulk insert
5次執行時間如下
5次花費時間平均(ms): 251
可以看到新增作業利用批次處理後，效能改善相當大。
網友jeff-yeh 提供 SqlBulkCopy(批次處理)也有不錯的效能表現，
回家後我馬上和寫法3(批次處理)重新測試比較了一次(相同硬體、資料庫環境和.NET Framework)，
兩者寫法在效能上幾乎是相等的，但寫法4在程式撰寫上靈活度較高，
感謝 jeff-yeh 熱心回饋喔。
寫法4
static void InsertV4(DataTable srcdata)
{
using (SqlConnection conn = new SqlConnection(myconnection))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//SqlBulkCopy批次處理新增 沒有檢驗比對處理
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, trans))
{
bulkCopy.DestinationTableName ="dbo.Test";
bulkCopy.WriteToServer(srcdata);
}
trans.Commit();
}
}
執行寫法4
DataTable dt = GetDataFromXls(); //30000 rows
DataView dv = dt.DefaultView;
dv.Sort = "c1 asc"; //依 clustered index 排序
DataTable dtsort = dv.ToTable();
Stopwatch sw = new Stopwatch();
sw.Reset();
sw.Start();
//InsertV1(dt); // using tsql
//InsertV2(dtsort); // using store procedure
//InsertV3(dtsort); //using bulk insert
InsertV4(dtsort); //using SqlBulkCopy
5次執行時間如下
5次花費時間平均(ms): 260
| 寫法1新增花費時間 | 寫法2新增花費時間 | 寫法3新增花費時間 | 寫法4新增花費時間 |
|
2729.6ms |
2513 ms |
251 ms |
260 ms |
參考