利用資料表值參數(Table Valued)及預存程序來提升批次更新資料的速度

2016-09-21

日前有一個系統功能是要大量更新資料表的資料，我詢問一下PG預計如何撰寫該功能。PG回覆我說就是用迴圈更新前端餵近來的資料，我聽到當下覺得這樣的做法效能不僅差，且會對SQL造成一定程度的效能影響。

因此我給他的建議是，將預計更新的資料一次給SQL做更新才會快，但要如何將大量要更新的資料給SQL呢 ? 我的作法會是利用資料表值(Table Valued)來當參數來紀錄要異動的資料，然後傳給SQL上的預存程序做一次性的更新，下面我來做一個小小模擬。

Rock程式寫的爛還請大家見諒。我先利用PG原來預計的做法，也就是一筆一筆更新。VB.Net的Code如下，我去更新Tb1這一張資料表中ID是1到10000的資料，將Phone這個欄位改成0955123000。

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim Con As SqlConnection
Dim TheCmd As SqlCommand
Con = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("testDBConn"))
Con.Open()
TheCmd = New SqlCommand
TheCmd.Connection = Con
Label1.Text = "StartTime:" & Now()
        For i = 1 To 10000 Step 1
            TheCmd.CommandText = "Update Tb1 Set Phone='0955123000' Where ID=" & i
            TheCmd.ExecuteNonQuery()
        Next
Label2.Text = "EndTime:" & Now()
TheCmd.Dispose()
TheCmd = Nothing
Con.Close()
Con.Dispose()
Con = Nothing
End Sub

上面這種做法在沒有適當索引的狀態下，耗用時間如下圖。共花費了3分20秒來更新10000筆資料。

我將ID欄位建立索引後再跑一次，耗用時間如下。更新10000筆資料只要5秒鐘。

更新10000筆資料只要5秒應該要滿足了啊 !

來來來，我們來試試更快的作法。首先我們先建立預計要用到的資料表值及預存程序，如下圖所示我建了一個Table Type叫tpUpdateList，其內部欄位就是ID跟Phone。再建立一個預存程序sp_Update，該預存程序需要一個資料型態為tpUpdateList的參數叫@List，更新方式我直接利用Update Join方式來更新參數丟進來的資料。

完成上面步驟，我們就來看看如何將Table Valued傳給預存程序做大量資料更新呢? VB.Net的Code如下。我的做法就是將10000筆資料寫入DataTable，再將該Data Table傳給SQL上的sp_Update預存程序做更新。

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim Con As SqlConnection
Dim TheCmd As SqlCommand
Dim table As DataTable = New DataTable("ParentTable")
Dim column As DataColumn
Dim row As DataRow

column = New DataColumn()
column.DataType = System.Type.GetType("System.Int16")
column.ColumnName = "ID"
column.Unique = True
table.Columns.Add(column)

column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "Phone"
column.Unique = False
table.Columns.Add(column)

Con = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("testDBConn"))
Con.Open()
        
Label1.Text = "StartTime:" & Now()
        For i = 1 To 10000 Step 1
            row = table.NewRow()
            row("ID") = i
            row("Phone") = "0966666777"
            table.Rows.Add(row)
        Next
TheCmd = New SqlCommand("sp_Update", Con)
TheCmd.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = TheCmd.Parameters.AddWithValue("@List", table)
tvpParam.SqlDbType = SqlDbType.Structured
TheCmd.ExecuteNonQuery()
Label2.Text = "EndTime:" & Now()
TheCmd.Dispose()
TheCmd = Nothing
Con.Close()
Con.Dispose()
Con = Nothing
End Sub

在有適當索引狀態下，更新耗費時間如下圖，一整個就是秒殺。