There are a few things to think off when writing code, design requirements and performance. If we include the thought process of performance when developing we can often improve our overall code performance at the time of writing.
When you need to import large numbers of item records into AX2012 you will find the time it takes to load them fairly slow. In my example I have 200,000 records to load. First I need to create the Product masters, then I need to release the product to the different companies. I was finding the creation of the product masters was taking about 3 hours to complete with the release to each of the companies around 3 hours each, and I had two companies to release.
The use of RecordInsertList has been well documented for increasing the system performance of inserts records.But I was curious how the use of RecordInsertList would scale and what impact it would have on performance.
To understand the impact on performance and scale-ability I created a table with one field. I then wrote a job that would insert X number of records into that table using first the standard insert method, then use the RecordInsertList method. Each pass would record the number of milliseconds to complete. I did this for 100 records then 1000, 5000, 10000, 20000, 40000, 100000, 1000000 and 10000000.
The use of SANs in today network environment is almost common place, but is the performance requirements of the systems using those SANs considered in the design on the SAN setup.
A typical problem I find is people are not considering how the SAN should be setup to get the best performance, primarily for DAX we are talking about the considerations for SQL server, regardless if SQL is visualized or physical.
Using the Database Engine Advisor to analyse a SQL query can give you an insight into where to look for problems with performance. Recently I have a problem opening the advisor from a query window recieving an error: “Failed to connect to an IPC Port: The system cannot find the file specified”.
The problem was caused by a previous use of the system that had not been shutdown cleanly. The simplest way to resolve this problem is to open the windows task manager and end the process for DTAShell.exe.
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.
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.