Query Tuning in Ax

When I started at my present position, the database handling was in a very poor state.  Performance was slow, indexes were defragmented and users were complaining by the hour.  After sorting out the defragmentation issues, we were still having performance issues with a Portal that was bolted on to Ax.  Looking at the integration piece that was written, I found that that it was a mix of miscellaneous query calls done through the business connector and custom queries that were written as concatenated strings.  In other words, it was a mess.



In general, I find that the Dynamics Ax query parser sucks.  It tends to connect tables via cross joins and make the links in the where clause.  Aside from looking ugly, it may have impacts on performance.  Recently, my manager gave me an example where a query using one customer account number was fairly quick, but using another it was very slow (from 1 to 8 seconds).  When I took the query out of AX, I found that owing to the way that the query was built, it generated different query plans and used different indexes.  This was why the performance varied.



If you need to get data out of Ax for an external application, I tend to find that using stored procedures and functions are a preferable route.  The weakness, however, is that you at the mercy of business rule or logic changes in Ax that would force modifications in custom code.



Caveats aside, there are some special considerations when you need to optimize Ax based queries.



1) Do not use Ax query standards.  This is an out of the box view from Ax.
CREATE VIEW [dbo].[LOGISTICSLOCATIONPARTY] AS SELECT T1.LOCATION AS LOCATION,T1.PARTY AS PARTY,T1.ISPRIMARY AS ISPRIMARY,T1.PARTITION AS PARTITION,T1.RECID AS RECID,
T2.PARTITION AS PARTITION#2,T2.LOCATIONROLE AS LOCATIONROLE
FROM DIRPARTYLOCATION T1
CROSS JOIN DIRPARTYLOCATIONROLE T2
WHERE (T2.PARTYLOCATION=T1.RECID AND (T2.PARTITION = T1.PARTITION))

The cross join is where the problems start.  It is far better to take a more standard approach of using inner and left joins to get the data needed.

CREATE VIEW [dbo].[LOGISTICSLOCATIONPARTY] AS SELECT T1.LOCATION AS LOCATION,T1.PARTY AS PARTY,T1.ISPRIMARY AS ISPRIMARY,T1.PARTITION AS PARTITION,T1.RECID AS RECID,
T2.PARTITION AS PARTITION#2,T2.LOCATIONROLE AS LOCATIONROLE FROM DIRPARTYLOCATION T1
INNER JOIN DIRPARTYLOCATIONROLE T2 ON T2.PARTYLOCATION=T1.RECID
AND T2.PARTITION = T1.PARTITION


2) All tables in Ax 2012 use partition and (if exists) dataareaid.  In the custom code, most of the table joins used ignored this fact.  As a result, query performance was very slow.  The worst offenders took over 30 seconds to process.  By using dataareaids and partitions I was able to reduce processing time to less than a second.