DataAreaId and Partition and Very Slow Queries

Forgetting to add DataAreaId in a SQL query is an issue I see a lot while resolving performance issues for support. Please review the following article which will serve as a reminder to add DataAreaId and Partition when calling direct SQL against a Dynamics AX database.

One common mistake I see programmers make that has a very large impact on performance is leaving DataAreaId, and Partition out of direct SQL calls to a Dynamics AX database. In a recent case, we improved the performance of a query from 20 minutes to 5 seconds by changing the query to use DataAreaId, and adding one index. There are many reasons to use SQL to query the Dynamics AX database directly. A few examples are SSRS reports and integrations.

Note: Partition was added in Dynamics AX 2012 R2 as a way to separate the data in tables so users can not see any data from other partitions.  This allows multiple companies to share a database, and it also is added to every index automatically.  For companies running a version of Dynamics AX prior to Dynamics AX 2012 R2, disregard any references to Partition.   

Many programmers get used to not including DataAreaId or Partition in a query because when they call a statement from X++, DataAreaId and Partition are not needed. Additionally, if there is only one company, the query will return the same results as it does from within Dynamics AX.  For example, the following X++ select statement finds all open sales orders.

select * from SalesTable

  join Salesline

  where SalesTable.SalesId == Salesline.salesid  &&

SalesTable.SalesStatus = 1

Dynamics AX automatically adds the DataAreaId (and partition in 2012R2). This will result in the query in SQL looks more like the following:

select * from SalesTable

  join Salesline

  on SalesTable.SalesId = Salesline.salesid

  where

SalesTable.DataAreaId = 'ceu'

and SalesLine.DataAreaId = 'ceu'

and SalesTable.SalesStatus = 1

and SalesTable.Partition = 5637144576

and SalesLine.Partition = 5637144576

The issue with calling a SQL Query without adding DataAreaId and Partition is that most indexes on Dynamics AX tables start with DataAreaId and Partition. When you do not use DataAreaId and Partition, SQL cannot traverse the index to answer your query. This is similar to trying to find everyone in the phone book with the first name “John”. The only way to do this is to read every row in the phone book. In comparison finding “John Smith” is very easy because the phone book is sorted by last names then first names.

Execution plans in SQL Server is how SQL retrieves the data for a query. As shown below, the execution plan for the query without DataAreaId and Partition has an estimated subtree cost of 1.95 and shows SQL Server performing an index scan (read every record in the table). It also indicates that there are missing indexes to perform the query.

In the next example, the execution plan for the query with DataAreaId and Partition has an estimated subtree cost of 0.2, and performs two index seeks.

In this example, the query is 10 times faster simply because DataAreaId and Partition were added to it, but as the size of the database grows, the performance difference between the two query options would also grow.

Chris Dragon