Index field order – pre AX 2012

In a previous blog I talked about adding additional fields to your indexes in DAX to control the placement of the DataAreaId. This was in relation to AX2012, but did you know you can also do this in previous versions of AX, this has been verified as working on version 4 and 2009.

This can greatly improve the performance of queries and should be considered in your performance tuning tool kit. But as with all things related to performance tuning, remember to test thoroughly before implementing on a production environment.

Advertisements

Index field order – performance tuning

The design of your database, along with your indexes will determine much of the performance limitations of your system. The indexes are how the system will locate information, and if these are well designed can produce considerable performance improvements. This blog will look at the index field order and the system fields, and the effects it can have on your system.

Continue reading

AX4 upgrade to AX2012 – Financial Dimensions

During a recent client upgrade from AX4 to AX2012, had a problem with performance with the upgrade wizard. On the Prepare Application for Preprocessing at the Prepare financial dimension framework step the bottom of the screen, dimension numbers, took 20+ minutes when pressing the upgrade validate button.

The client had 300+ companies, each with many financial dimensions, which resulted in building 180,000+ records in the migration tables. When reviewing the code that builds this information, class LedgerFrameworkSetupHelper, method setupDimensionMergeTable, the records were being read and inserted one at a time, and not using the bulk insert controls. this causes high load on SQL and is also very inefficient.

Changing the code to use the RecordInsertList so records could be bulk inserted, reduced load time by 94% to just over 1 minutes. This means the SQL server was much more efficiently being used, and the time spent waiting for data to be loaded significantly reduced.

So if you are planning to do an upgrade from AX4 to AX2012, and have lots of companies, then consider modify the code to use the bulk insert commands.

Continue reading