This one comes up fairly often for me, importing a large volume of data into AX, it performs badly, what on earth can I do to make it faster?? Well there are a few options, here’s the details that I give to people when they ask:
- Optimise the dat file method of export/import, there are a number of things you can do:
- Change the TmpRecIdMap table in the AOT to a real table instead of temporary. If you don’t then you’ll run out of temp space. As I’m sure you know AX temporary tables are not SQL temporary tables, they are instead held in the temp space on the machine using the temporary table.
- In SQL server set logging level to simple (just while importing to prevent excessive logging).
- Inside AX export the data in several separate groups of tables (by configuring multiple definition groups).
- Make sure to keep tables referencing RecId in another table within the same group as the related table, if you don’t then the RecId relationship will not be maintained (as you have found).
- Make sure the size of the database files are set high enough so that they do not have to grow whilst the import is running.
- Set the following options “execute on AOS: Yes”, “Do not search for existing records: yes”, “Indexing: reindex after import”, “Use Record ID Compression: No”.
- Run the import/export of each definition group on a separate client or even on separate AOSs in parallel.
Using these options will maximise performance and will minimise the risk of a single error bringing the whole thing to a halt as you run separate imports in parallel.
- Change the TmpRecIdMap table in the AOT to a real table instead of temporary. If you don’t then you’ll run out of temp space. As I’m sure you know AX temporary tables are not SQL temporary tables, they are instead held in the temp space on the machine using the temporary table.
- Set up the source SQL server as a linked server on the destination SQL server and write a script to generate the transact SQL to move the data
Advantage of this is speed of the import, the disadvantage is the time it takes to create the script, however the script could be written to use sys.objects and so automatically generate the insert statements required, so in the long term this may be faster (if you ever need to move large companies across environments again this could be reused).
- Set up an SSIS (Sql Server Integration Services) package to transfer the data
Similar to the above solution but this may take more time to set up the package as you would have to write many separate transformations.