Best Practices for Indexes

Article
08/03/2018

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The basic rules for index design are as follows:

Assign a unique index to each table.
Add as few indexes as possible and maintain query performance.
Strongly consider designating one of the indexes as the cluster index.

An error will be displayed if an index is overlapped by another index, and the other is enabled and doesn't have a configuration key. Error icon An error will also be displayed if an index is created with no fields in it.

Using the Key

If the table has a key, create a unique index on the fields in the key (set the AllowDuplicates property to No). The database system ensures the uniqueness of the key.

When to Create an Index

The advantages of indexes are as follows:

Their use in queries usually results in much better performance.
They make it possible to quickly retrieve (fetch) data.
They can be used for sorting. A post-fetch-sort operation can be eliminated.
Unique indexes guarantee uniquely identifiable records in the database.

The disadvantages of indexes are as follows:

They decrease performance on inserts, updates, and deletes.
They take up space (this increases with the number of fields used and the length of the fields).
Some databases will monocase values in fields that are indexed.

You should only create indexes when they are actually needed.

Take care not to add an index on something that has already been indexed. If you need a more detailed index, you can add fields to an existing index as long as it is not a unique index.

Tip

It is more time-consuming to change fields at the beginning of an index than at the end of an index. If fields in an index are updated frequently, place these at the end of the index.

Index Hints

When you use index hints, verify them with performance tests. The optimizer might be able to find a more efficient hint.

The following examples show finding ledger transactions in account number, transaction date order.

Last weeks' (few days) transactions on all the (many) Profit & Loss accounts.

select ledgerTrans
    index hint DateIdx
    order by accountNum, transDate
    where ledgerTrans.accountNum >= '40000'   
        &&  ledgerTrans.accountNum <= '99999'   
        &&  ledgerTrans.transDate  >= 26\04\1999 
        &&  ledgerTrans.transDate  <= 02\05\1999;

Transactions for the whole year (many dates) on (the few) liquid assets accounts.

select ledgerTrans
        index hint ACDate
        order by accountNum, transDate
        where ledgerTrans.accountNum >= '11100'   
            &&  ledgerTrans.accountNum <= '11190'   
            &&  ledgerTrans.transDate  >= 01\07\1999 
            &&  ledgerTrans.transDate  <= 30\06\2000;

Best Practices for Indexes

Using the Key

When to Create an Index

Index Hints

See also

Additional resources