使用foreach循环批量向SQL Server导数据时性能下降问题求助

核心问题分析 #

你的代码每次循环都新建SqlConnection和SqlBulkCopy实例，虽用using保证资源释放，但前期连接池可复用连接，后期会因连接池频繁分配回收、数据库端资源累积（日志增长、索引维护开销）导致速度暴跌。另外未使用事务会让每批插入单独提交，大幅增加数据库IO负担。

优化方案 #

1. 复用数据库连接与SqlBulkCopy实例 #

全程复用一个数据库连接，且只初始化一次SqlBulkCopy，减少连接创建销毁的开销，避免连接池资源耗尽。

优化后代码示例：

using (SqlConnection connection = new SqlConnection(conn))
{
    await connection.OpenAsync();
    // 仅初始化一次SqlBulkCopy
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "[dbo].[XXXX]";
        bulkCopy.BatchSize = 1000;

        foreach (var _dt in dts)
        {
            try
            {
                bulkCopy.WriteToServer(_dt);
                // 处理后清空DataTable释放内存
                _dt.Clear();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"处理组数据失败: {ex.Message}");
                // 可添加错误日志，继续处理下一组
            }
        }
    }
}

2. 启用事务批量提交 #

将多批插入合并到事务中提交，减少数据库日志写入次数，降低IO开销。可选择全程一个事务，或每N组提交一次（平衡性能与风险）。

示例（每10组提交一次事务）：

using (SqlConnection connection = new SqlConnection(conn))
{
    await connection.OpenAsync();
    int batchIndex = 0;
    const int commitBatchCount = 10;

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "[dbo].[XXXX]";
        bulkCopy.BatchSize = 1000;

        foreach (var _dt in dts)
        {
            SqlTransaction trans = null;
            try
            {
                // 每10组开启新事务
                if (batchIndex % commitBatchCount == 0)
                {
                    trans = connection.BeginTransaction();
                    bulkCopy.Transaction = trans;
                }

                bulkCopy.WriteToServer(_dt);
                _dt.Clear();

                // 达到批次数量提交事务
                if (batchIndex % commitBatchCount == commitBatchCount - 1)
                {
                    trans.Commit();
                    trans.Dispose();
                }

                batchIndex++;
            }
            catch (Exception ex)
            {
                trans?.Rollback();
                trans?.Dispose();
                Console.WriteLine($"处理第{batchIndex}组失败: {ex.Message}");
            }
        }

        // 提交剩余未完成的批次
        if (batchIndex % commitBatchCount != 0)
        {
            using (var finalTrans = connection.BeginTransaction())
            {
                bulkCopy.Transaction = finalTrans;
                finalTrans.Commit();
            }
        }
    }
}

3. 优化数据库端配置 #

• 调整恢复模式：插入期间临时将数据库切换为简单恢复模式，减少日志生成量（插入完成后切回完整恢复模式）；或预先扩容日志文件，避免自动扩容的IO开销。
• 禁用非聚集索引：插入前禁用目标表的所有非聚集索引，插入完成后再重建索引，大幅减少索引维护开销。
• 检查磁盘IO：用SQL Server自带的性能监视器查看磁盘使用率，若IO达到瓶颈，需优化存储（如改用SSD、拆分数据与日志磁盘）。

4. 调整SqlBulkCopy参数 #

• 增大BatchSize：根据服务器性能调整为5000或10000，减少批次数量，降低连接与事务的重复开销。
• 启用表锁：创建SqlBulkCopy时指定SqlBulkCopyOptions.TableLock，批量插入时锁定整个表，减少行锁竞争：

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null))

5. 减少内存开销 #

处理完的DataTable及时调用Clear()释放内存，避免内存占用过高触发频繁GC；若可能，直接用IDataReader读取CSV数据（无需转成DataTable），进一步降低内存消耗。

内容的提问来源于stack exchange，提问作者howard.huang