使用ADODB Recordset UpdateBatch优化SQL Server批量更新性能

通过Excel VBA的ADO向SQL Server单列临时表插入1000行耗时约145秒，期望找到无需拼接原生SQL、易维护的实现（偏好.AddNew+.UpdateBatch），若不可行则求最优高效方案。当前代码中.UpdateBatch本身仅耗时0.5秒，剩余耗时都在该操作的实际执行阶段。

已做测试 #

• Update 1：用BeginTrans+CommitTrans包裹循环执行INSERT，耗时无变化
• Update 2：服务器端执行带GO 1000的INSERT语句，耗时约2分钟
• Update 3：服务器端用事务包裹WHILE循环插入，耗时仅数分之一秒，怀疑VBA中即便用事务仍在执行单条事务
• Update 4：确认连接提供者支持事务，cn.Properties("Transaction DDL").Value返回8

优化.AddNew+.UpdateBatch的实现 #

默认ADO配置可能导致逐行提交，需调整连接和记录集参数来启用真正的批量操作：

1. 优化连接字符串
   添加参数禁用不必要的OLEDB服务，减少额外开销：

   Dim connStr As String
   connStr = "Provider=SQLOLEDB;Data Source=你的服务器地址;Initial Catalog=目标数据库;Integrated Security=SSPI;" & _
             "OLE DB Services=-2;" & _  ' 仅保留连接池，禁用其他服务
             "Use Encryption for Data=False;"
   

2. 配置记录集批量属性
   必须设置客户端游标和批量乐观锁定，确保本地缓存所有变更后一次性提交：

   Dim rs As ADODB.Recordset
   Set rs = New ADODB.Recordset
   
   With rs
       .CursorLocation = adUseClient  ' 客户端游标支持批量更新
       .LockType = adLockBatchOptimistic  ' 批量乐观锁模式
       ' 以表模式打开临时表，避免解析SQL开销
       .Open "#你的临时表名", cn, adOpenStatic, adLockBatchOptimistic, adCmdTable
       
       ' 批量添加数据
       For i = 1 To 1000
           .AddNew
           .Fields("目标列名").Value = 待插入的值
       Next i
       
       .UpdateBatch adAffectAll  ' 一次性提交所有缓存的变更
       .Close
   End With
   Set rs = Nothing
   

   核心是adUseClient和adLockBatchOptimistic，这两个设置能彻底避免逐行网络交互，把所有插入操作合并为一次请求。

3. 临时表优化
   确保临时表是会话级的#临时表（避免跨会话干扰），且未创建不必要的索引——索引会大幅减慢批量插入速度。

最优高效方案：表值参数（Table-Valued Parameters） #

如果上述优化仍达不到预期，表值参数是SQL Server批量插入的最优方案，无需拼接SQL，性能接近服务器端本地插入：

1. 在SQL Server端创建自定义表类型

   CREATE TYPE dbo.SingleColumnType AS TABLE (ColName INT)  -- 替换为你的列类型
   

2. VBA中实现批量插入

   Dim cmd As ADODB.Command
   Dim param As ADODB.Parameter
   Dim rsParam As ADODB.Recordset
   
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cn
   cmd.CommandText = "INSERT INTO #你的临时表 (ColName) SELECT ColName FROM @TVP"
   cmd.CommandType = adCmdText
   
   ' 创建匹配表类型的记录集
   Set rsParam = New ADODB.Recordset
   rsParam.Fields.Append "ColName", adInteger  ' 类型需与自定义表类型一致
   rsParam.Open
   
   ' 填充待插入数据
   For i = 1 To 1000
       rsParam.AddNew
       rsParam("ColName").Value = i
   Next i
   
   ' 配置表值参数
   Set param = cmd.CreateParameter("@TVP", adVariant, adParamInput, , rsParam)
   param.Type = adDBTypeTable
   param.Properties("SQL Server Type Name").Value = "dbo.SingleColumnType"
   cmd.Parameters.Append param
   
   ' 执行批量插入
   cmd.Execute
   
   rsParam.Close
   Set rsParam = Nothing
   Set cmd = Nothing
   

   该方案通过一次网络请求将所有数据发送到服务器，由SQL Server内部完成批量插入，完全消除逐行网络延迟。

事务问题说明 #

你测试中VBA事务包裹循环INSERT无性能提升，核心原因是每一次INSERT都是独立的网络请求——即使开启了事务，往返服务器的开销依然存在。而服务器端事务WHILE循环是本地执行，没有网络延迟。因此解决性能问题的关键始终是减少网络交互次数，批量提交而非逐行操作。

内容的提问来源于stack exchange，提问作者langbourne