SQL WHERE clause perfromance issue AX2012R3

Question

I am trying to optimize a query. Note that I am using Microsoft Dynamics AX and traced this query to SQL. The query except for the index hints is being generated by Dynamics and I am wondering if I have to rewrite the whole statement in AX or if I can fix this by simply adding changes to indexes. The query is as follows:

SELECT        T1.PARTITION, T1.DECIMALPRECISION, T1.RECID, T1.RECVERSION, T1.SYMBOL, T1.SYSTEMOFUNITS, T1.UNITOFMEASURECLASS
    FROM            UNITOFMEASURE AS T1 WITH(INDEX(I_4436SYMBOLIDX))
    WHERE        T1.PARTITION = 5637144576 AND EXISTS
                                 (SELECT        'x' AS Expr1
                                   FROM            UNITOFMEASURECONVERSION AS T2 WITH(INDEX(I_4438CONVERSIONIDX))  

                                   WHERE        (T2.PARTITION = 5637144576 ) 
                                   AND (T2.PRODUCT = 5637297578 OR T2.PRODUCT = 0) 
                                   AND (T1.RECID = T2.FROMUNITOFMEASURE) 
                                   AND (T2.TOUNITOFMEASURE = 0) OR
                                                             (T2.PARTITION = 5637144576) AND (T2.PRODUCT = 5637297578 OR
                                                             T2.PRODUCT = 0) AND (T1.RECID = T2.TOUNITOFMEASURE) AND (T2.FROMUNITOFMEASURE = 0))

This query returns the following execution plan: You can see it reads 5 342544 records. The corresponding tables contain 1342 and 3984 records.

If i remove the first T1.PARTITION = 5637144576where clause, the query executes how it should. This is the updated query:

SELECT        T1.PARTITION, T1.DECIMALPRECISION, T1.RECID, T1.RECVERSION, T1.SYMBOL, T1.SYSTEMOFUNITS, T1.UNITOFMEASURECLASS
FROM            UNITOFMEASURE AS T1 WITH(INDEX(I_4436SYMBOLIDX))
WHERE        EXISTS
                             (SELECT        'x' AS Expr1
                               FROM            UNITOFMEASURECONVERSION AS T2 WITH(INDEX(I_4438CONVERSIONIDX))  

                               WHERE        (T2.PARTITION = 5637144576 ) 
                               AND (T2.PRODUCT = 5637297578 OR T2.PRODUCT = 0) 
                               AND (T1.RECID = T2.FROMUNITOFMEASURE) 
                               AND (T2.TOUNITOFMEASURE = 0) OR
                                                         (T2.PARTITION = 5637144576) AND (T2.PRODUCT = 5637297578 OR
                                                         T2.PRODUCT = 0) AND (T1.RECID = T2.TOUNITOFMEASURE) AND (T2.FROMUNITOFMEASURE = 0))

Now it uses the following execution plan:

Here's more information about the indexes used:

Can I optimize the performance by changing the indexes ? Or do I have to rewrite the functionality by building the query myself in Dynamics AX 2012.

Thanks in advance.

Gordon Linoff · Accepted Answer · 2019-11-18T16:16:14.523

Your query is a mess. The OR in the EXISTS is going to kill performance. So, use multiple EXISTS clauses. I think this is the logic:

SELECT . . .
FROM UNITOFMEASURE AS T1 
WHERE T1.PARTITION = 5637144576 AND
      (EXISTS (SELECT 1
               FROM UNITOFMEASURECONVERSION T2
               WHERE T2.FROMUNITOFMEASURE = T1.RECID AND
                     T2.PARTITION = 5637144576 AND
                     T2.PRODUCT IN (5637297578, 0)  AND
                     T2.TOUNITOFMEASURE = 0 
              ) OR 
       EXISTS (SELECT 1
               FROM UNITOFMEASURECONVERSION T2
               WHERE T2.TOUNITOFMEASURE = T1.RECID AND
                     T2.PARTITION = 5637144576 AND
                     T2.PRODUCT IN (5637297578, 0) AND
                     T2.FROMUNITOFMEASURE = 0
              )
      );

Then you want indexes on:

UNITOFMEASURE(PARTITION, RECID)
UNITOFMEASURECONVERSION(RECID, PARTITION, PRODUCT, FROMUNITOFMEASURE, TOUNITOFMEASURE)

I think the second index is useful for both subqueries.

Actually, if I interpreted the query correctly, it can be written as:

SELECT . . .
FROM UNITOFMEASURE AS T1 
WHERE T1.PARTITION = 5637144576 AND
      EXISTS (SELECT 1
              FROM UNITOFMEASURECONVERSION T2
              WHERE T2.FROMUNITOFMEASURE = T1.RECID AND
                    T2.PARTITION = 5637144576 AND
                    T2.PRODUCT IN (5637297578, 0)  AND
                    (T2.TOUNITOFMEASURE = 0 OR T2.FROMUNITOFMEASURE = 0)
             );

And the above indexes should work.

The query automatically generated by MS Dynamics AX 2012 R3 CU13 is indeed a mess. From your answer I can conclude that I will need a code change in the application. The issue at hand is occurring in a production environment, hence why i was looking for an alternative solution through indexation on the databases in SQL Server. I will look for an alternative solution in the application and release it through an ALM procedure. Thanks for taking your time to answer my question, i will mark your post as the answer. — Jeffrey Saelen, Nov 18 '19 at 17:22

SQL WHERE clause perfromance issue AX2012R3

1 Answers1