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.

When an index is created on a table (in DAX) you select the fields to include in the index. The system will then synchronize this index with the database server creating or updating the index in SQL.

A typical example of an index created in DAX might be the on the salestable and an index called SalesIdx. The index in DAX has the field SalesId, and this is a unique index. If you look in SQL and this index you will see three fields, partition, dataareaid and salesid. The system automatically includes the partition and dataareaid, if the table is not shared, into the index.

When we look at the way the SQL index will work for most implementations we find the following:

1. Most implementations only have a single DAX partition.
2. Many implementations only have a small number of companies.

If the above are true, and your sales table has many thousands of records, or more, then the index will not work very efficiently.

The design of an index should have the most unique element of the index first, this means the speed to find the data required is quicker. In the above case the salesid is the most unique information, with dataareaid next and partition last. So ideally we would want the index to be formatted with SalesId, DataareaId and Partition.

The good news is this can be achieve within the standard DAX environment. If we go to the index on the SalesTable and go to the SalesIdx index, we can right click and select new field. Then select the new field and open the properties, allowing you to select a new data field, select DataAreaId. Do the same again for Partition. Once all three fields are present, ensure they are ordered in the correct order; salesid, dataareaid, partition.

Now this have been completed ensure you have synchronized your system so this change is transferred to SQL server. If you look at the SQL server you will not see the change to the index field order, and if you run queries you should see an improvement  in performance.

It is important to remember that the design and development of indexes depends on lots of factors, and changes to standard indexes should not be changed without proper analysis and understanding, and making the wrong changes can hurt system performance. Changes required also dependent on the volume and mix of data, as well as the design of the code, all playing factors in the performance of data retrieval in DAX.

Happy performance tuning…