Top 10 issues discovered from Dynamics AX Code Review

The following list is not exhaustive and is only based on our experience from the field.

1. Wrong caching leads to unnecessary database calls

This is one of the most fundamental feature of the product today. The three tiers architecture of Dynamics AX allows you to define caching on AOS and client. Not using caching properly is the first root cause for performance. Make sure the following rules are defined:

• All your customized tables have relevant table group defined in AOT. For example, master data should have “Main” category and transactional data should be set to “Transaction” (header or line).
• Don’t leave the default setting “None” for Cache Lookup and set the correct value to your table such as “EntireTable” for Parameter table and “Found” for Main group.
• Review the cache limit for every table group in the server performance settings

2. Expensive database calls from the form

Since Dynamics AX 2012, you can use the Form Style checker tool to detect any SQL operations directly written within the form objects. Typically, this code persists in the click() method of the button. SQL operations such as update, create or delete a record should be written on the class level or directly on the table. Having one single version of the code on the class will also help you to avoid duplicate method and obsolete method. 

3. Large and expensive queries due to select * query

This got to be the most common issue with huge penalty on performance, but also the easiest to detect and to fix. Make sure you can use the Field list and Exist join when possible. For every X++ Select statement, make sure you only retrieve the necessary fields. Same idea with Exists Join statement where you can reduce the amount of communication between the AOS and the database. This is especially true when customization adds lot of new fields on existing tables. For example, replace the following code:

           While select TableA
              {  Select TableB where TableB.FieldA == TableA.FieldA;
                 Print TableB.FieldB;
              }With
              While select FieldA from TableA Join FieldA, FieldB from TableB
              Where TableB.FieldA == TableA.FieldA;              {  Print TableB.FieldB;
              }

4. Massive and unnecessary X++ loop

This one is more rare but always nice to catch and easy to fix. Basically the developer is not using theaggregate function and executes a loop to calculate a sum or a count. You can easily find those patterns by searching in the AOT with the keyword “++” or “=+”. So the advice is to replace the following code:

            While Select TableA where FieldA = “ConditionA”
            {  CounterA ++;
            }

With
             Select count(RecId) from TableA where FieldA = “ConditionA”;
             Counter A = TableId.RecId;

 5. Too many Display methods in your grid

This got to be the first root cause of slow opening or refreshing of form object. It is quite easy to find such root cause by removing the display method and run Trace Parser tool to compare the two traces. In a scenario where a display method is used to display a column on a grid, the display method is executed for each row that is populated in the grid. That makes the overhead of the method quite expensive.
The performance of display methods can be improved by caching them if they are calculated on AOS.Caching display methods can also improve performance when records are transferred from the server to the client. The value of all cached methods is set when data is fetched from the back-end database. In addition, the value is refreshed when the reread method is called on the form data source. To avoid any RPC call to the database, you can use the new feature from AX 2012 called Computed Column. Here the expression is directly stored in the SQL Server database as a view. You can see the walkthrough on this MSDN article.http://msdn.microsoft.com/en-us/library/gg845841.aspx

6. Direct SQL code misaligned with AOT indexes 

Every time I check a new Dynamics AX instance, I directly check the store procedure in the SQL Server Management Studio. This is where you may find Direct SQL code written against the Dynamics AX database. You can also find direct SQL statement in the X++ code by searching the AOT with the keyword "connection.createStatement()". Direct SQL can be perfectly fine if well written. If not, it will clearly impact the performance because existing indexes are not used in the query plan.
This blog article from Michael DeVoe shows a good example of such SQL Statement not aligned with AOT indexes like PartitionID and DataAreaId from AX 2012 R2. The other problem is the maintenance cost of such code. Any schema change in the AOT will not be automatically updated in those Direct SQL like in X++ methods. It is like hard coded label, it contradicts principals of object oriented X++ language. 

7. No "Error on Invalid field access" in development environment.

In order to verify that we do not access fields in an invalid way, you can use the system parameter called “Error on Invalid Field access”. This setting will throw an exception if a field that was not retrieved is being accessed. The parameter can be found under System administration \ Setup \ System\Server Configuration and it requires a restart of the AOS service. It is strongly recommended to enable this setting when testing your modifications in the application to avoid unpleasant and hard-to-find bugs later on in the production environment. Please note this is only relevant from AX 2012.

8.   Error compilation in production!

Why should we even talk about it? Well in reality, we often discover error and warning Best Practicesmessages when compiling production code. If you follow the recommended code promotion to release new development into the production instance, you should have run the full compilation a few times in previous environments. For auditing purpose, I also recommend the operational team to save the result of each compilation as HTML file during code promotion.
You can also customize the Best Practice tool to include your own rules. The classes used to check for rules are named SysBPCheck<ElementKind>. You call the init, check, and dispose methods once for each node in the AOT for the element being compiled.
Last but not least, you should define the compiler tool to reject any check-in if there is warning. Most of the errors and warnings can be easily fixed so there is no excuse to postpone the resolution during the development lifecycle. There will always be a struggle to find time for such quality assessment tasks, but it will definitively pay off in the long term. 

9. Bulk operations regressed into row by row ones 

The three bulk operations, Update_RecordSet, Insert_RecordSet and Delete_From operations are great way to improve the performance of X++ queries with only one RPC call to the database for multiple rows impacted. The problem is that overwriting the SYS method update(), insert() and delete() methods may break these features. Wrong customization can lead into a row by row operation. So it is recommended to verify the RPC calls with Trace Parser to verify the performance of the intended X++ code.

 10.  Too many fields in legacy tables

With the Dynamics AX 2012 release, the number of tables is higher than in previous releases because of database normalization. It was mainly done to reduce redundancy of data across various tables, and also to improve performance. For example, for the Parameter type table, where only one record exists per Company. On the opposite, de-normalization can also be recommended in some case to improve performance because it will reduce the number of join in the X++ queries.
You need to find the right balance when doing customization but the basic rule is to limit the number of new fields in SYS tables to 50. From our customer visit, we can discover many legacy tables with hundreds of new fields. What’s more surprising is that most of them remain empty in production because the data modelling was never analyzed in depth. Don’t forget to run the Customization Analysis Tool from Lifecycle Services to automatically get a detailed report of your model.