#033 BC DATATRANSFER
Since Microsoft Dynamics 365 Business Central version 21 there is a new AL type to use when transferring data in bulk is needed.
DataTransfer is a new AL type that allows to transfer data from fields or copy all rows from one table to another.
The usage of this data type produces SQL code that operates on sets (direct operations in SQL), and thereby, this behavior will improve performance.
How to use?
In the example below there are two tables:
- “From Table”, containing 10.000 records.
- “To Table”, containing 0 records.
To demonstrate the performance improvements using DataTransfer, two functions were developed to transfer data from one table to the other:
- CopyRowsUsingDataTransfer()
- CopyRowsUsingOldMethod().
The execution time of each function is shown below:
Using DataTransfer, the data was transferred in 2 seconds. As for the old method, row by row, it took 16 seconds.
This multiplied for thousands of records, is a major difference in performance.
Limitations
- Only allowed in upgrade code (Upgrade Codeunits)
- It will not work for the following table types:
- Non-SQL tables
- System and virtual tables
- Audited tables (as destination table)
- Obsoleted (as destination table)
- It will not work for the following field types:
- System and Timestamp fields (as destination field)
- Media and MediaSet
- Calculated fields
