Posted by One of the benefits of Microsoft Dynamics AX is that the schema is fairly easy to navigate around. Every day I thank Microsoft for not going down the obfuscation route. Have you ever worked on ACCPAC? It’s a nightmare. Peoplesoft isn’t that much better.
Most AX systems I’ve worked on have some external reporting running on the database. Because of the somewhat sensible schema, anyone using MsQuery can get in there and query the data. That’s how most reporting systems have kicked off, I’ve found.
Initially the reports run fine, but over time they start to become slower and slower. Eventually they get to the point where they don’t run.
The main reason is because the DataAreaId filter in the query has been left out in the WHERE clause.
DataAreaId
The DataAreaId is the “area of data” within AX. The system includes this column in all its tables and is the first column specified in the all the indexes.
Most developers leave it out because within the X++ code there is no need to include it. Dynamics AX automatically includes it in its queries.
If you leave this filter out of your queries they will do a table scan every time.
For my customers, the reason why the queries ran well at the beginning was because of the low volume of data. A table scan over thousands of records is OK. But when you get to hundreds of thousands and millions, it’s going to be slow.
A typical slow query on the BankAccountTrans table
SELECT *
FROM dbo.BankAccountTrans
WHERE AccountId = '12345'
With the DataAreaId filter added to the WHERE clause
SELECT *
FROM dbo.BankAccountTrans
WHERE AccountId = '12345'
AND DataAreaId = 'cfe'
Partition
With the release of Microsoft Dynamics 2012 R2, the concept of a Partition was introduced. This allows multiple companies to share a single Dynamics database. It does this by segmented the tables with a Partition column, so that users can’t data from other users.
If you’re working on a Dynamics AX release of 2012 R2 or more recent, you’ll also need to include the Partition in your query filters as well.
SELECT *
FROM dbo.BankAccountTrans
WHERE AccountId = '12345'
AND DataAreaId = 'cfe'
AND Partition = 3436761213
Applying these small changes to your queries and reports will result in immediate performance improvements. Not to mention taking a huge load off your database.