Umbraco-cms: Migrating databases from v7 to v8 can take a long time with larger databases

[nul800sebastiaan]

During testing of migrations, we noticed that larger databases take a long time (up to 2 hours).

Specifically, updating the cmsPropertyData table will take quite a while depending on the amount of rows in there.

We're seeing significant slowdowns on sites we've tested, for example some of them had 100.000, 600.000 and 900.000 rows of property data.

In order to be able to complete the migration you might need to add the following to youir connection string: ;Connection Timeout=3600000 and in the <httpRuntime element you might want to add a long timeout, like executionTimeout="3600000".

It might also help to run an "unversion" script beforehand to make sure there's not so much data in cmsPropertyData before you start migrations.

As always, please make sure that you have good backups of your data before you start working with it.

We're looking into improving the performance of the migrations for the next versions of v8.

[stevemegson]

I've submitted a number of improvements in PR #6191.

My testing on a SQL Server database with 1.6 million rows of cmsPropertyData now completes in around 20 minutes (I don't have a complete 'before' test to compare to, but I abandoned one after leaving it overnight).

Possible further changes

The remaining bottlenecks are mainly transferring large amounts of data to and from the database, which becomes a problem if the database isn't local.

• AddTypedLabels parses int and datetime values in C#. Could we try TRY_PARSE in SQL first, before fetching remaining rows to parse in C#?
• DropDownPropertyEditorsMigration, RadioAndCheckboxPropertyEditorsMigration, AddTypedLabels perform many single-row updates to umbracoPropertyData. On SQL Server, would it be quicker to bulk insert the new values into a temp table and then do one UPDATE...FROM?
• Checking for edited property data is done in C# because we can't compare textValue in SQL. Could we test non-text values in SQL, and only fetch the text values? On SQL Server, could we test text values by casting to nvarchar(MAX)?

[netaddicts]

@stevemegson Are we still supposed to use the drop-in dll as commented here: https://github.com/umbraco/Umbraco-CMS/pull/6191#issuecomment-531701136