1

I am calculating rankings for an entire 300 event season base off teams and games played. This means updating a ranking from oldest event to newest every night for ELO ratings.

We are using a UnitOfCommit setup with Entity Framework 6 and after processing this large season it takes almost 5 minutes to update all the entities. I am doing bulk inserts which work great.

I have three type of entities that get updated. A list of entities called TeamOrganizationSeason is a running total of all the events and needs to be saved at the end of the loop. The DivisionTeamRanking and DivisionTeamRankingGame can be saved at the end of each iteration in the loop instead of doing a bulk.

Is there a way to split up the commits to do the TeamOrganizationSeason separate from the other two? Is there alway a better way to handle this?

// Start At First Event To Calculate Elo Running Values
            foreach (var @event in events.Where(t => t.StartDate.HasValue).OrderBy(t => t.StartDate))
            {
// Start Calculating Points and Ratings
// Could Save List<DivisionTeamRanking> and List<DivisionTeamRankingGame> here
}

// Could Save List<TeamOrganizationSeason> only here
 UnitOfWork.Commit();
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • What is the actual problem you are trying to solve? You mentioned a table being locked? – nvoigt Nov 29 '22 at 18:16
  • I am doing bulk updating, its locking the whole table. That should be obvious what the issue is. Why would I want a table locked for 5 minutes? – Mike Flynn Nov 29 '22 at 19:08

1 Answers1

0

There is not really a good way. ORMs are not made for bulk data, especially not updating.

What you could do conceptually is move all the data you are changing into their own tables. Then instead of updating, calculate the new values for those tables (this should not lock anything) and then truncate the tables and insert the pre-calculated values. That will lock the tables, but that should get done in under a minute if you don't have excessive amounts of data.

How to insert records fast: Fastest Way of Inserting in Entity Framework

nvoigt
  • 75,013
  • 26
  • 93
  • 142
  • Thanks but I already have super fast inserting. It's just the updating. I think you idea is a good solution but I would insert into a new table, then just to an update join on the original ID in the main table and the new table FK on each value. This way instead of updating one at a time, this should be way quicker. – Mike Flynn Nov 30 '22 at 13:58
  • Sounds good, too. – nvoigt Nov 30 '22 at 15:13