At the beginning of September 2022 I wrote this post introducing the new DataTransfer data type (available from Business Central version 21). DataTransfer is an AL data type that supports the bulk transferring of data between SQL based tables. Instead of operating on a row-by-row model, like the record API does, DataTransfer produces SQL code that operates on sets (direct operations in SQL, without loading the data back and forth to the NST). This behavior improves the performance when moving data during upgrades.
The DataTransfer object can only be used in upgrade codeunits and it will throw a runtime error if used outside of the scope of an upgrade codeunit. The DataTransfer object can be useful during upgrade of your extensions mainly for:
- Copy data from one or more fields in a table to fields in another table (sometimes required when you obsolete fields).
- Copy data from entire rows in a table to rows in another table (sometimes required when you obsolete an entire table).
I talked about this new data type also this Monday at the Microsoft Italy event in Milan saying you that I highly recommend to use it in your upgrade code because it speeds up a lot the upgrade process expecially with tables with a large amount of data. Someone of you asked “great, but how much is the gain in performances?”.
I’ve decided to find the time to do some measures. To do that I’ve created an extension with two simple tables called FromTable and ToTable
The two tables are defined as follows (same structure for simplicity):
FromTable was filled with the following sets of records (5 scenarios):
- 10000 records
- 50000 records
- 100000 records
- 500000 records
- 1000000 records
In the extension upgrade, I want to transfer records from FromTable to ToTable in all the above scenarios and I want to measure the performances of this upgrade process.
The upgrade process was handled in two ways: using standard old AL code (moving records between tables in a loop) and using the new DataTransfer data type.
The old upgrade way is defined in the TransferTableRowsV1 procedure:
and the new upgrade way is defined in the TransferTableRowsV2 procedure:
What happens if I upgrade my extension from version A to version B executing the TransferTableRowsV1 or the TransferTableRowsV2 in the upgrade codeunit (OnUpgradePerCompany trigger)?
These are my measures (in seconds):
Despite the numerical values, I think that there’s a clear result: the gain of using DataTransfer is a lot and you can have upgrades > 70x faster than using pure AL code:
Use it in your upgrade codeunit if you want to provide a smooth upgrade experience to your users.
Stefano Demiliani 