C#导入千万级CSV至SQL表：DataTable填充过慢求性能优化

字段映射规则（中文翻译） #

• EXTSENSORID - F1的EXTSENSORID字段
• UTCDateTime - 将F1的READTS字段转换为DateTime类型
• Value - F1的VAL字段乘以F2的MULT1字段（通过EXTSENSORID关联F1与F2）
• LocalDateTime - 将F1的READTS字段转换为本地时间
• LOCSERV - F2的LOCSERV字段（通过EXTSENSORID关联F1与F2）
• CID - F3的CID字段（通过LOCSERV关联F2与F3）
• RATE - F3的RATE字段（通过LOCSERV关联F2与F3）
• MULT1、MULT2 - F2中对应字段（通过EXTSENSORID关联F1与F2）

当前核心瓶颈在DataTable填充环节，主要源于Linq遍历List的低效查询、DataTable的内存开销、全量处理的内存压力。以下是针对性优化方案：

一、优化关联数据存储结构（核心提速点） #

将F2、F3从List改为Dictionary，利用字典O(1)的查询性能替代List的O(n)遍历，直接消除关联环节的性能损耗：

// 定义F2实体类
public class F2Model
{
    public string EXTSENSORID { get; set; }
    public decimal MULT1 { get; set; }
    public decimal MULT2 { get; set; }
    public string LOCSERV { get; set; }
}

// 定义F3实体类
public class F3Model
{
    public string LOCSERV { get; set; }
    public string CID { get; set; }
    public decimal RATE { get; set; }
}

// 加载F2到Dictionary（键为EXTSENSORID）
var f2Dict = new Dictionary<string, F2Model>(StringComparer.OrdinalIgnoreCase);
foreach (var row in 读取F2的逻辑)
{
    var f2 = new F2Model
    {
        EXTSENSORID = row["EXTSENSORID"],
        MULT1 = decimal.Parse(row["MULT1"]),
        MULT2 = decimal.Parse(row["MULT2"]),
        LOCSERV = row["LOCSERV"]
    };
    if (!f2Dict.ContainsKey(f2.EXTSENSORID))
    {
        f2Dict.Add(f2.EXTSENSORID, f2);
    }
}

// 加载F3到Dictionary（键为LOCSERV）
var f3Dict = new Dictionary<string, F3Model>(StringComparer.OrdinalIgnoreCase);
foreach (var row in 读取F3的逻辑)
{
    var f3 = new F3Model
    {
        LOCSERV = row["LOCSERV"],
        CID = row["CID"],
        RATE = decimal.Parse(row["RATE"])
    };
    if (!f3Dict.ContainsKey(f3.LOCSERV))
    {
        f3Dict.Add(f3.LOCSERV, f3);
    }
}

二、替换DataTable，用强类型集合+批量写入 #

DataTable对百万级数据的内存开销极大，改用强类型实体+SqlBulkCopy直接写入，完全跳过DataTable的转换环节。推荐用FastMember库快速将强类型集合转为IDataReader，供SqlBulkCopy使用：

1. 定义目标SQL表对应的实体类：

public class TargetEntity
{
    public string EXTSENSORID { get; set; }
    public DateTime UTCDateTime { get; set; }
    public decimal Value { get; set; }
    public DateTime LocalDateTime { get; set; }
    public string LOCSERV { get; set; }
    public string CID { get; set; }
    public decimal RATE { get; set; }
    public decimal MULT1 { get; set; }
    public decimal MULT2 { get; set; }
}

2. 流式处理F1，边读边处理边批量写入：

const int BatchSize = 10000; // 每批处理1万行，可根据服务器内存调整
var batchList = new List<TargetEntity>(BatchSize);
var connectionString = "你的数据库连接字符串";

using (var reader = new StreamReader("F1文件路径"))
{
    var header = reader.ReadLine(); // 跳过CSV表头
    string line;
    while ((line = reader.ReadLine()) != null)
    {
        // 注意：复杂CSV请用CsvHelper解析，避免Split(',')处理带引号/逗号的字段
        var fields = line.Split(',');
        var extSensorId = fields[EXTSENSORID对应的索引];
        
        // 快速匹配关联数据
        if (!f2Dict.TryGetValue(extSensorId, out var f2)) continue;
        if (!f3Dict.TryGetValue(f2.LOCSERV, out var f3)) continue;
        
        // 构造目标实体（提前转换数值/日期类型，避免重复解析）
        var readTs = DateTime.Parse(fields[READTS对应的索引]);
        var val = decimal.Parse(fields[VAL对应的索引]);
        var target = new TargetEntity
        {
            EXTSENSORID = extSensorId,
            UTCDateTime = readTs,
            Value = val * f2.MULT1,
            LocalDateTime = readTs.ToLocalTime(),
            LOCSERV = f2.LOCSERV,
            CID = f3.CID,
            RATE = f3.RATE,
            MULT1 = f2.MULT1,
            MULT2 = f2.MULT2
        };
        
        batchList.Add(target);
        
        // 达到批处理阈值，执行批量插入
        if (batchList.Count >= BatchSize)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = "目标SQL表名";
                    // 字段映射（属性名与表字段名一致时可省略）
                    bulkCopy.ColumnMappings.Add(nameof(TargetEntity.EXTSENSORID), "EXTSENSORID");
                    bulkCopy.ColumnMappings.Add(nameof(TargetEntity.UTCDateTime), "UTCDateTime");
                    // 其他字段同理添加映射...
                    
                    // 用FastMember将集合转为IDataReader
                    using (var dataReader = ObjectReader.Create(batchList))
                    {
                        bulkCopy.WriteToServer(dataReader);
                    }
                }
            }
            batchList.Clear(); // 清空批次，释放内存
        }
    }
    
    // 处理剩余不足一批的数据
    if (batchList.Count > 0)
    {
        // 重复上述批量插入逻辑
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (var bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.DestinationTableName = "目标SQL表名";
                // 字段映射...
                using (var dataReader = ObjectReader.Create(batchList))
                {
                    bulkCopy.WriteToServer(dataReader);
                }
            }
        }
    }
}

三、额外优化建议 #

1. 用专业CSV解析库：避免用Split(',')处理CSV，推荐使用CsvHelper，支持复杂格式且解析效率更高。
2. 提前转换数据类型：加载F2、F3时就将MULT1、RATE等字段转为decimal，避免每行重复解析。
3. 调整SqlBulkCopy参数：设置bulkCopy.BatchSize = BatchSize，添加SqlBulkCopyOptions.TableLock锁表提升写入速度；若目标表约束允许，可临时关闭CheckConstraints。
4. 内存优化：避免一次性加载F1全量数据到内存，保持流式处理的方式。

四、若必须保留DataTable的优化方案 #

如果因业务限制必须使用DataTable，可做以下调整：

• 提前定义DataTable的列结构，禁用AutoIncrement、Constraints。
• 设置dataTable.LoadOption = LoadOption.OverwriteChanges，减少内部事件触发。
• 批量添加行，避免逐行调用DataTable.Rows.Add。
• 同样使用Dictionary关联F2、F3，替代Linq的List查询。

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