使用 TVP (Table Value Parameters) 實作快速大量更新
雖然早先就聽過 TVP (Table Value Parameters) 但一直沒有機會用到，這次難得遇到需要快速大量更新資料表的情景，就順手紀錄一下。
ORM 使用 Dapper 來做簡易展示，其實 Dapper 有第三方 Library 版本 Dapper.Plus，可以處理大量更新，且程式碼簡潔易懂，不過是需要付費的版本
環境
- Dapper
- LinqPad 6
- Northwind 資料庫
- SQL Server Management Studio (SSMS)
Note
使用 TVP 更新格式需要完全相容，為了簡易示範，已經提前拿掉所有關聯性的 FK 值
建立資料表值參數型別以及預存程序
-
使用 SSMS 建立資料表值參數型別
-
建立名為 OrderTableType 的資料表類型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
USE [Northwind]
GO
CREATE TYPE [dbo].[OrderTableType] AS TABLE(
[OrderID] int,
[CustomerID] nchar(5) ,
[EmployeeID] int,
[OrderDate] datetime,
[RequiredDate] datetime,
[ShippedDate] datetime,
[ShipVia] int,
[Freight] money,
[ShipName] nvarchar(40),
[ShipAddress] nvarchar(60),
[ShipCity] nvarchar(15),
[ShipRegion] nvarchar(15),
[ShipPostalCode] nvarchar(10),
[ShipCountry] nvarchar(15)
)
GO
|
-
資料表重新整理，並查看類型是否成功建立
-
接下來建立預存程序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
CREATE PROCEDURE usp_UpdateOrders
(@tvpNewOrder [dbo].[OrderTableType] READONLY)
AS
BEGIN
UPDATE [dbo].[Orders]
SET [OrderID] = ut.OrderID
,[CustomerID] = ut.CustomerID
,[EmployeeID] = ut.EmployeeID
,[OrderDate] = ut.OrderDate
,[RequiredDate] = ut.RequiredDate
,[ShippedDate] = ut.ShippedDate
,[ShipVia] = ut.ShipVia
,[Freight] = ut.Freight
,[ShipName] = ut.ShipName
,[ShipAddress] = ut.ShipAddress
,[ShipCity] = ut.ShipCity
,[ShipRegion] = ut.ShipRegion
,[ShipPostalCode] = ut.ShipPostalCode
,[ShipCountry] = ut.ShipCountry
FROM [dbo].[Order] AS [bt] JOIN @tvpNewOrder AS [ut]
ON [bt].[OrderID] = [ut].[OrderID]
END
GO
|
依照微軟Docs的說明，應該將剛剛的 Type 設置為 READONLY，然後建立更新用的預存程序
-
重新整理一下就可以看到剛剛建立的預存程序
使用 Dapper 更新資料表
將要更新的資料作成 DataTable，透過 Dapper 建立 TVP 參數，把它傳遞給 SQL Server，更新的語法與剛剛建立的預存程序中更新差不多，但需留意 Primary Key 是不能修改的
完整程式碼如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
void Main()
{
// 將原資料 ShipName 欄位加上_1
var updateOrders = Orders.AsEnumerable()
.Select(x => new
{
OrderID = x.OrderID,
CustomerID = x.CustomerID,
EmployeeID = x.EmployeeID,
OrderDate = x.OrderDate,
RequiredDate = x.RequiredDate,
ShippedDate = x.ShippedDate,
ShipVia = x.ShipVia,
Freight = x.Freight,
ShipName = $"{x.ShipName}_1",
ShipAddress = x.ShipAddress,
ShipCity = x.ShipCity,
ShipRegion = x.ShipRegion,
ShipPostalCode = x.ShipPostalCode,
ShipCountry = x.ShipCountry
}).ToList();
string json = Newtonsoft.Json.JsonConvert.SerializeObject(updateOrders);
DataTable dt = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(json);
// 搭配 JOIN 的語法來 Update 資料
var sqlCommand = @"UPDATE [dbo].[Orders]
SET [CustomerID] = ut.CustomerID
,[EmployeeID] = ut.EmployeeID
,[OrderDate] = ut.OrderDate
,[RequiredDate] = ut.RequiredDate
,[ShippedDate] = ut.ShippedDate
,[ShipVia] = ut.ShipVia
,[Freight] = ut.Freight
,[ShipName] = ut.ShipName
,[ShipAddress] = ut.ShipAddress
,[ShipCity] = ut.ShipCity
,[ShipRegion] = ut.ShipRegion
,[ShipPostalCode] = ut.ShipPostalCode
,[ShipCountry] = ut.ShipCountry
FROM [dbo].[Orders] AS [bt] JOIN @tvpNewOrder AS [ut]
ON [bt].[OrderID] = [ut].[OrderID]";
// 只計算更新資料表的時間做比較
Stopwatch stopWatch = new Stopwatch();
stopWatch.Reset();
stopWatch.Start();
using (var tranScope = new TransactionScope())
{
using (var connection = this.Connection)
{
// 建立 TVP 參數
var tvp = new DynamicParameters();
tvp.AddDynamicParams(new { tvpNewOrder = dt.AsTableValuedParameter("OrderTableType") });
connection.Execute(sqlCommand, tvp);
}
tranScope.Complete();
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
elapsedTime.Dump();
}
|
比較
不使用 TVP 的傳統更新
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
void Main()
{
// 將原資料 ShipName 欄位加上_2
var updateOrders = Orders.AsEnumerable()
.Select(x => new
{
OrderID = x.OrderID,
CustomerID = x.CustomerID,
EmployeeID = x.EmployeeID,
OrderDate = x.OrderDate,
RequiredDate = x.RequiredDate,
ShippedDate = x.ShippedDate,
ShipVia = x.ShipVia,
Freight = x.Freight,
ShipName = $"{x.ShipName}_2",
ShipAddress = x.ShipAddress,
ShipCity = x.ShipCity,
ShipRegion = x.ShipRegion,
ShipPostalCode = x.ShipPostalCode,
ShipCountry = x.ShipCountry
}).ToList();
var sqlCommand = @"UPDATE [dbo].[Orders]
SET [CustomerID] = @CustomerID
,[EmployeeID] = @EmployeeID
,[OrderDate] = @OrderDate
,[RequiredDate] = @RequiredDate
,[ShippedDate] = @ShippedDate
,[ShipVia] = @ShipVia
,[Freight] = @Freight
,[ShipName] = @ShipName
,[ShipAddress] = @ShipAddress
,[ShipCity] = @ShipCity
,[ShipRegion] = @ShipRegion
,[ShipPostalCode] = @ShipPostalCode
,[ShipCountry] = @ShipCountry
WHERE [OrderID] = @OrderID";
// 只計算更新資料表的時間做比較
Stopwatch stopWatch = new Stopwatch();
stopWatch.Reset();
stopWatch.Start();
using (var tranScope = new TransactionScope())
{
using (var connection = this.Connection)
{
connection.Execute(sqlCommand, updateOrders);
}
tranScope.Complete();
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
elapsedTime.Dump();
}
|
參考
資料表值參數
https://docs.microsoft.com/zh-tw/dotnet/framework/data/adonet/sql/table-valued-parameters#creating-table-valued-parameter-types
軟體主廚的程式料理廚房
用 SqlBulkCopy 可以快速批次 Insert 大量資料，那批次 Update 大量資料呢？
結論
手邊並沒有更合適的資料表能作為展示，僅能以北風資料庫做示範，比較性有限，實際用在更新五萬多筆且資料欄為龐大的資料表，速度差異非常明顯。
