Commerce: Failed to migrate property decimal: Timeout expired.

Today I was involved in an issue running the upgrade migration steps in Episerver Commerce.migrationsteps

This step is performed if you upgrade past Episerver Commerce 9.5.0 that came with update 94.

How disappointing! Even though care has been taken in the migration step to do it in small units and with an extended SQL timeout of 5 minutes, a property of SQL leaves it, in this particular query, more dependent than was intended on the size of the dataset to migrate.

SQL is a declarative language

That means you tell SQL what to do, not how to do it. Depending on the way you write the query, the query optimizer has a better chance of efficiently doing what you want it to do.

Even if two scripts will always end up in the same result, the query optimizer may be unable to figure out that they are, in fact, equivalent. That’s why you sometimes need to tweak your query into something equivalent but better performing.

Look at this query taken from the Migrate Property Decimal step. This statement fails to perform:

This Transact-SQL script first retrieves all properties that need migration into @AllDecimals. Then, it joins CatalogContentProperty with this table and finally applies the updates only on lines fulfilling the WHERE-condition. The UPDATE statement affect no more than 10’000 rows.

It would make no difference to the end result if it would join with the first 10’000 rows from @AllDecimals like this:

However, it makes all the difference in the world when it comes to performance. A sample dataset went from 9m 47s to a mere two seconds!

How do I get past the error?

If you do get this error when you attempt to perform the migration, you can open SQL Management Studio and run these two queries until they return 0 rows:


If you feel inclined, you may replace the 10000 (two times in each script) to a larger number, depending on the size of your dataset.

3 thoughts on “Commerce: Failed to migrate property decimal: Timeout expired.”

Leave a Reply

Your email address will not be published. Required fields are marked *