使用EF Core 5与FileHelpers导入900条CSV数据速度过慢问题求助

核心问题根因 #

当前代码的耗时99%来自循环内的重复数据库查询与单次提交：

• 每次循环都执行3次独立数据库查询（球员查重、机构查询、球队查询），600条数据就会产生至少1800次数据库请求，大量时间浪费在网络IO与数据库连接建立上
• 若AddPlayer、AddTeam等方法内部每次都调用SaveChanges()，还会额外产生数百次事务提交开销
• EF Core默认的实时变更跟踪会在批量操作时产生大量无用计算，进一步拖慢速度

优化方案 #

1. 预热缓存减少数据库查询 #

导入前先把所有需要用到的对照数据一次性加载到内存，避免循环内查库：

// 提前加载所有球员查重字段到HashSet，查重仅查内存
var existingPlayers = db.Players.Select(p => new { p.FirstName, p.Surname, p.Year, p.EmailAddress })
    .AsNoTracking()
    .ToHashSet(p => (p.FirstName, p.Surname, p.Year, p.EmailAddress));

// 提前加载机构映射字典
var orgMap = refTable.GetAllRefOrginizations()
    .AsNoTracking()
    .ToDictionary(o => o.Name, o => o.Id);

// 提前加载球队映射字典
var teamMap = team.GetAllTeams()
    .AsNoTracking()
    .ToDictionary(t => t.Name, t => t.Id);

// 待新增列表，统一批量提交
var toAddOrgs = new List<Ref_Orginzation>();
var toAddTeams = new List<Team>();
var toAddPlayers = new List<Player>();

2. 循环内仅做内存计算 #

var engine = new FileHelperEngine<PlayerImportCSVModel>();
var result = engine.ReadFile(FileName);

foreach (var item in result)
{
    // 内存查重，无IO开销
    var playerKey = (item.FirstName, item.LastName, Convert.ToInt32(item.BirthYear), item.Email);
    if (existingPlayers.Contains(playerKey))
        continue;

    var player = new Player
    {
        FirstName = item.FirstName,
        Surname = item.LastName,
        PlayerLevel = item.Level,
        Year = Convert.ToInt32(item.BirthYear),
        IsActive = true,
        EmailAddress = item.Email,
        IsDeleted = false,
        CreatedBy = "System Import",
        CreatedDate = DateTime.Now,
        Gender = item.Gender == "M" ? 1 : 2
    };

    // 处理机构
    if (!orgMap.TryGetValue(item.Association, out var orgId))
    {
        var newOrg = new Ref_Orginzation
        {
            Name = item.Association,
            IsActive = true,
            IsDeleted = false, // 注意原代码这里写的是true，属于逻辑bug，新机构不能标记为删除
            CreatedBy = "System Import",
            CreatedDate = DateTime.Now
        };
        toAddOrgs.Add(newOrg);
        // 临时加入字典，避免重复新增同名称机构
        orgMap[item.Association] = newOrg.Id;
        orgId = newOrg.Id;
    }
    player.OrginizationId = orgId;

    // 处理球队
    if (!teamMap.TryGetValue(item.Team, out var teamId))
    {
        var newTeam = new Team
        {
            Name = item.Team,
            IsActive = true,
            IsDeleted = false,
            CreatedBy = "System Import",
            CreatedDate = DateTime.Now
        };
        toAddTeams.Add(newTeam);
        teamMap[item.Team] = newTeam.Id;
        teamId = newTeam.Id;
    }
    player.TeamId = teamId;

    toAddPlayers.Add(player);
}

3. 统一批量提交 #

// 关闭EF Core变更追踪提升性能
db.ChangeTracker.AutoDetectChangesEnabled = false;

// 批量新增，单次SaveChanges
db.Ref_Orginzations.AddRange(toAddOrgs);
db.Teams.AddRange(toAddTeams);
db.Players.AddRange(toAddPlayers);
db.SaveChanges();

// 恢复原有配置
db.ChangeTracker.AutoDetectChangesEnabled = true;

如果需要进一步提速，可以引入对应的EF Core批量插入扩展库，600条数据总耗时可以控制在1秒以内。

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