主题：[分享] 数据快速保存

先感谢Bin、jspta、有点甜、逛逛

 

狐表什么都好，就是最后一步的保存，不够快。

 

修改或新增的数据，如果在一百行之内，还是狐表的代码简单、方便，也不会有速度慢的感觉。

但是一旦修改或新增的数据记录，超过千条，速度差距，就能明显感觉到。

 

有的项目，需要经常导入、合并同一张表，数据量也很大，保存是个难关。

 

ACCESS数据源，好多SQL语句不支持，只能在SQL数据源中测试了。

 

订单表，两千行数据。

 

一、Filler或高速合并，然后DataTables("订单").Load，200秒

Dim dt As Date = Date.Now
If DataTables.Contains("导入订单") AndAlso DataTables.Contains("订单") Then
    Dim f As New Filler
    f.SourceTable = DataTables("导入订单")
    f.DataTable = DataTables("订单")
    f.ExcludeExistValue = True
    f.Filter = DataTables("导入订单").LoadFilter
    f.Fill()
End If
DataTables("订单").Load
Dim tp As TimeSpan
tp = Date.Now - dt
MessageBox.Show(tp.TotalSeconds)

二、逐行插入 100秒

Dim dt As Date = Date.Now
Dim cmd As New SQLCommand
cmd.C
Dim sql1,sql2 As String
For Each c As Col In Tables("导入订单").Cols
    sql1 + = c.Name & ","
Next
For Each r As Row In Tables("导入订单").Rows
    sql2 = ""
    For Each c As Col In Tables("导入订单").Cols
        If c.IsDate Then
            sql2 + = "'" & r(c.Name) & "'" & ","
        ElseIf c.IsString Then
            sql2 + = "'" & r(c.Name) & "',"
        ElseIf c.IsBoolean Then
            If r(c.Name) = True Then
                sql2 + = 1 & ","
            ElseIf r(c.Name) = False Then
                sql2 + = 0 & ","
            End If
        Else
            sql2 + = r(c.Name) & ","
        End If
    Next
    cmd.CommandText = "InSert Into 订单 (" & sql1.Trim(",") & ") Values (" & sql2.Trim(",") & ")"
    cmd.ExecuteNonQuery
Next
DataTables("订单").Load
Dim tp As TimeSpan
tp = Date.Now - dt
MessageBox.Show(tp.TotalSeconds)

三、多行一次插入，用分号（;）分隔语句 12秒

Dim dt As Date = Date.Now
Dim cmd As New SQLCommand
cmd.C
Dim sql1,sql2 As String
For Each c As Col In Tables("导入订单").Cols
    sql1 + = c.Name & ","
Next
For Each r As Row In Tables("导入订单").Rows
    sql2 = ""
    For Each c As Col In Tables("导入订单").Cols
        If c.IsDate Then
            sql2 + = "'" & r(c.Name) & "'" & ","
        ElseIf c.IsString Then
            sql2 + = "'" & r(c.Name) & "',"
        ElseIf c.IsBoolean Then
            If r(c.Name) = True Then
                sql2 + = 1 & ","
            ElseIf r(c.Name) = False Then
                sql2 + = 0 & ","
            End If
        Else
            sql2 + = r(c.Name) & ","
        End If
    Next
    cmd.CommandText + = "InSert Into 订单 (" & sql1.Trim(",") & ") Values (" & sql2.Trim(",") & ");"   
Next
cmd.CommandText = cmd.CommandText.Trim(";")
cmd.ExecuteNonQuery
DataTables("订单").Load
Dim tp As TimeSpan
tp = Date.Now - dt
MessageBox.Show(tp.TotalSeconds)

四、多行一次插入，用 Union All 分隔语句 8秒

Dim dt As Date = Date.Now
Dim cmd As New SQLCommand
cmd.C
Dim sql1,sql2 As String
For Each c As Col In Tables("导入订单").Cols
    sql1 + = c.Name & ","
Next
For Each r As Row In Tables("导入订单").Rows
    sql2 = ""
    For Each c As Col In Tables("导入订单").Cols
        If c.IsDate Then
            sql2 + = "'" & r(c.Name) & "'" & ","
        ElseIf c.IsString Then
            sql2 + = "'" & r(c.Name) & "',"
        ElseIf c.IsBoolean Then
            If r(c.Name) = True Then
                sql2 + = 1 & ","
            ElseIf r(c.Name) = False Then
                sql2 + = 0 & ","
            End If
        Else
            sql2 + = r(c.Name) & ","
        End If
    Next
    cmd.CommandText + = "Select " & sql2.Trim(",") & vbcrlf & "Union All" & vbcrlf
Next
cmd.CommandText = "InSert Into 订单 (" & sql1.Trim(",") & ")" & vbcrlf & cmd.CommandText.SubString(0,cmd.CommandText.Length - 11)
cmd.ExecuteNonQuery
DataTables("订单").Load
Dim tp As TimeSpan
tp = Date.Now - dt
MessageBox.Show(tp.TotalSeconds)

四个方法中，第四个方法，应该是最好的了，

把Union All换成Union，就可以排除重复值。

速度没区别，有事务，安全性肯定高了。

我也测试了，一个1秒，一个2秒，也可以说，相差一倍吧。

cmd.CommandText = "InSert Into 订单 (" & sql1.Trim(",") & ")" & vbcrlf & cmd.CommandText.SubString(0,cmd.CommandText.Length - 11)

 

就不能减11了，应该减7吧。