AX 2012 R3. How to disable inventory dimension hashing to improve performance.

As all of you may know there is a SQL limitation on maximum number of fields in index. We cannot have more than 16 (actually maximum is 14 because index includes DataAreaId and Partition fields). It’s quite important for inventory dimensions story and this limitation was hit in AX 2012 R3 with new fields for advance warehouse.

To overcome this limitation new hash field is introduced.  This field contains hashed value of other dimension fields, so-called “secondary dimensions”.  Using new approach, we can add more than 14 dimensions but have to pay a performance overhead for this flexibility.

However, usually we don’t use all inventory dimensions like InventGtdId_RU, InventProfileId_RU and InventOwnerId_RU. So we can disable inventory extensibility to reduce the performance overhead.

Let’s do it step by step.

1. Remove unused fields from DimIdx index of InventDim table.

We don’t use Russian localization, so we will remove InventGtdId_RU, InventProfileId_RU, and InventOwnerId_RU fields from the index.

DimIdx

Also we need to remove SHA1Hash filed because we don’t need it any more.

We will add InventStatusId and LicensePlateId fields to DimIdx index because we are using advance warehouse.

DimIdxNewFields

2. Disable InventDimExtensibility configuration key that controls dimensions extensibility.

3. Modify InventDIm.hashKey() method to skip hash calculation on update and insert.

/// <summary>
/// Calculates the string that is used when the hash value is calculated for the dimension values that
/// are not included in the <c>DimIdx</c> index.
/// </summary>
/// <returns>
/// A string that contains the calculated hash value.
/// </returns>
public str hashKey()
{
    str     hashKey   = '';
    str     hashKeyCaseInsensitive = '';

    #InventDimDevelop

    //Disable invent dimension extensibility -->
    if (!isConfigurationkeyEnabled(configurationKeyNum(InventDimExtensibility)))
    {
        return hashKey;
    }
    //Disable invent dimension extensibility <--

    /*
    SQL Server has a limitation of 16 fields in one index. For InventDim this is a problem if extra dimensions are added in an installation.
    Instead we have added a new field which can store hashed values of the least used dimensions.
    The class InventDimUniquenessEnabling can be used to validate if the proper indexes are defined and this method includes the right fields.

    Create a string this is unique for every combination of the dimensions. This can for example be achieved by using the code pattern below for each field included in the hash.
    The field values must be trimmed for trailing spaces, as this method is invoked before insert() - where such trimming also occurs.

    if (this.<FieldName>)
    {
        hashKey += (hashKey ? '~' : '') + '<FieldName>:' + strRTrim(this.<FieldName>);
    }
    */

    // Due to index limitations, hash the values.
    if (this.LicensePlateId)
    {
        hashKeyCaseInsensitive += (hashKey ? '~' : '') + 'LicensePlateId:' + strRTrim(this.LicensePlateId);
    }

    if (this.InventStatusId)
    {
        hashKeyCaseInsensitive += (hashKeyCaseInsensitive ? '~' : '') + 'InventStatusId:' + strRTrim(this.InventStatusId);
    }

    return hashKey + strLwr(hashKeyCaseInsensitive);
}

4. Modify InventDIm.findDim() method to search by new index fields.

client server static public InventDim findDim(
    InventDim   _inventDim,
    boolean     _forupdate = false)
{
    // <GEERU>
    #ISOCountryRegionCodes
    // </GEERU>
    InventDim       inventDim;

    if (_forupdate)
    {
        inventDim.selectForUpdate(_forupdate);
    }

    // Fields might not have been selected on the specified buffers, or might have been updated since selection
    _inventDim.checkInvalidFieldAccess(false);

    if (isConfigurationkeyEnabled(configurationKeyNum(InventDimExtensibility)))
    {
        select firstonly inventDim
        where inventDim.ConfigId            == _inventDim.ConfigId
           && inventDim.InventSizeId        == _inventDim.InventSizeId
           && inventDim.InventColorId       == _inventDim.InventColorId
           && inventDim.InventStyleId       == _inventDim.InventStyleId
           && inventDim.InventSiteId        == _inventDim.InventSiteId
           && inventDim.InventLocationId    == _inventDim.InventLocationId
           && inventDim.InventBatchId       == _inventDim.InventBatchId
           && inventDim.wmsLocationId       == _inventDim.wmsLocationId
           && inventDim.wmsPalletId         == _inventDim.wmsPalletId
           && inventDim.sha1Hash            == _inventDim.hashValue() // Needed to hit unique index cache. All dimensions should be included in the where clause - also those included in the hash key,
           && inventDim.InventSerialId      == _inventDim.InventSerialId
           && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
           && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
           && inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU;
    }
    else
    {
        select firstonly inventDim
            where inventDim.ConfigId         == _inventDim.ConfigId
               && inventDim.InventSizeId     == _inventDim.InventSizeId
               && inventDim.InventColorId    == _inventDim.InventColorId
               && inventDim.InventStyleId    == _inventDim.InventStyleId
               && inventDim.InventSiteId     == _inventDim.InventSiteId
               && inventDim.InventLocationId == _inventDim.InventLocationId
               && inventDim.InventBatchId    == _inventDim.InventBatchId
               && inventDim.wmsLocationId    == _inventDim.wmsLocationId
               && inventDim.wmsPalletId      == _inventDim.wmsPalletId
               //Disable invent dimension extensibility -->
               /* Orig -->
               // <GEERU>
               && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
               && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
               && inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU
               // </GEERU>
               && inventDim.InventSerialId   == _inventDim.InventSerialId;
               Orig <-- */
               && inventDim.InventSerialId   == _inventDim.InventSerialId
               && inventDim.InventStatusId   == _inventDim.InventStatusId
               && inventDim.LicensePlateId   == _inventDim.LicensePlateId;
               //Disable invent dimension extensibility <--
    }
    #inventDimDevelop

    return inventDim;
}

5. Review all customization and standard code that use removed\added dimensions fields in where statement. This could be done using cross references.

That’s all!

To read more about promoting and demoting inventory dimensions please refer to this msdn article.

Also additional improvements were made in AX 7, for more details read this blog post.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s