Entity Framework: Slow migrations with large volumes of data

Question

We are using Entity Framework Core in our data layer and have found that migrations are very slow when doing a large number of operations on tables with a great deal of data (millions of rows).

For instance, at the moment we're tweaking the sizes of our string (varchar) columns in the whole database. With every single column length change, EF will drop all table indexes and constraints and recreated them. For example, if we had 1 index and 10 columns that changed, EF will drop and recreate the index 10 times, rather than simply dropping the index once, changing the columns, and recreating the index again.

On some of our tables, a single column change takes over 30 minutes to do, and we have in the order of 200 column changes we'd like to make.

Is it possible to change this behaviour to drop all indexes at the start of the migration and recreate them at the end?

Looks like issue for EF Core. Here we need examples of particular migration which recreates idea 10 times. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Feb 12, 2021 at 19:13
I don't believe specific examples are required. The question is simply whether indexes can all be dropped at the start of a migration, execute several operations and recreate indexes at the end of the migration. — André Haupt
– André Haupt, Commented Feb 12, 2021 at 19:25

André Haupt · Accepted Answer · 2021-04-23 11:27:34Z

We ended up creating two migrations to solve this problem.

First, we would hide all the index definitions in our DbContext class. We then create a migration, which will naturally drop all the indexes.

Second, we make the changes to the data structures and uncomment the index definitions. The second migration will change the structure and add the indexes again.

WaitingForGuacamole · Accepted Answer · 2021-02-12 19:19:47Z

If your column length changes are all happening in a single migration, you should be able to do something like migrationBuilder.Sql(@"-- drop index statements here"); then all of your column length changes, then migrationBuilder.Sql(@"-- recreate index statements here");

I think the key here is to ensure that you're doing as many column length changes between those statements as possible to minimize the performance hit. Subject, of course, to a risk assessment regarding all of the changes you're making at once.

EF will continue to attempt to drop the index with each column length change. What one can do is to comment out all indexes in the DbContext, create a new migration, then apply the column changes and to create another migration, and then finally uncommenting the indexes and doing another migration. I was hoping for a more elegant approach.

Collectives™ on Stack Overflow

Entity Framework: Slow migrations with large volumes of data

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related