UA-44032151-3 page contents

Improve performance of your external applications querying Dynamics AX


Are you having performance issues with some of your external applications extracting data from Dynamics AX?

 

There are some rules in writing queries for Dynamics AX that you need to be aware.  These rules are implemented in Dynamics AX by the kernel when it executes queries against your SQL Server database.  The rule is that DATAAREAID (AX 2012 and previous) or PARTITION and DATAAREADID (AX 2012 R2 and above) must be part of your query against the database if those fields are in the tables that you are querying.

 

Example:

 

AX 2012 and previous

SELECT A.RECID

FROM LEDGERTRANS A

WHERE ((DATAAREAID=@P1) AND ((TRANSDATE=@P2) AND (DOCUMENTNUM=@P3)))

 

AX 2012 R2 and above

SELECT A.RECID

FROM LEDGERTRANS A

WHERE (PARTITION = @P1) AND (DATAAREAID=@P2) AND ((TRANSDATE=@P3) AND (DOCUMENTNUM=@P4)))

 

You will get an execution plan that looks like the following:

 

  

A very common problem that we see from external applications, is that they forget to add these columns to the queries which will cause an Index scan in the database causing a performance issue.

 

Example:

SELECT A.RECID

FROM LEDGERJOURNALTRANS A

WHERE (((TRANSDATE=@P2) AND (DOCUMENTNUM=@P3)))

 

 

As you can see from this plan we now are actually scanning the table.  This occurs because the external application has not followed the same rules as the Dynamics AX kernel and added the appropriate mandatory columns to the query. 

 

This is required because for tables that have DATAAREAID or PARTITION and DATAAREAID, all indexes on those tables begin with those fields.  So if you don’t add those fields to your query you will scan the index even if you only have 1 DATAAREAID or 1 PARTITION because of how the indexes are created in Dynamics AX.

 

A potential indicator of this will show in Performance Analyzer for Microsoft Dynamics as the following:

 

 

 

INDEX_NAME

INDEX_DESCRIPTION

INDEX_KEYS

USER_SEEKS

USER_SCANS

USER_LOOKUPS

I_30392REFRECIDIDX

NONCLUSTERED

PARTITION, DATAAREAID, REFRECID

429

70318

0

 

In this example, the index being scanned contains 3 columns.  One of the reasons you will get the scan is if you don’t provide all columns in your query.  There are other reasons such as poor criteria such as DATAAREAID like ‘%’.   So if you see a lot of this situation start looking for code coming from external sources as the root cause. 

 

You can use the following query in your DynamicsPerf database:

 

SELECT TABLE_NAME, INDEX_NAME, INDEX_DESCRIPTION, INDEX_KEYS, USER_SEEKS, USER_SCANS, USER_LOOKUPS

FROM INDEX_STATS_CURR_VW

ORDER BY TABLE_NAME, INDEX_NAME

 

Keep all of this in mind as you develop code external to Dynamics AX, that it follows the same rules as Dynamics.

 

 

Rod “Hotrod” Hansen

 

Skip to main content