Performance and InventDim and InventSum

The standard ‘out-of-the-box’ system will perform reasonably for many companies. There is always a need to test the performance and tune the system to your requirements.

If your implementation uses inventory, and if you are using batch and or serial numbers, then you might find your inventory performance to be sluggish. You might want to look at your indexes on the tables InventDim and InventSum. These tables are used in almost every query when involved with inventory.

In a test system you should try changing the following indexes to see if they give you more performance in your inventory system:

Table: InventDim   Index: DimIdIdx

add two fields to the index, dataAreaId and Partition. Ensure the field order is InventDimId, DataAreaId, Partition.

Table: InventSum    Index: ItemDimIdx

add two fields to the index, dataAreaId and Partition. Ensure the field order is ItemId, InventDimId, DataAreaId, Partition.

So why make this change. To get the best out of an index it is benifical to have the most unique field to be the first index field, this will help to reduce the data volume for searching the quickest. For inventdim with a single legal entity, you will have many inventory dimension (typically) and only one dataareaid and partition, so making the inventdimid first will greatly improve the search for the item you are looking for.

The same works for inventsum, but you can also try testing making the inventdimid first before the itemid, especially if you are using serial or batch numbers, since the use of these greatly increases the number of inventory dimensions (InventDim) records, and can make the InventDimId a more unique entity over ItemId.

This principle can be applied throughout the DAX system, but should only be done within a test environment and validated prior to release to a production environment, as if not done correctly, or proper testing is not carried out, you can hurt the system performance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s