Hello AX community, 

I would like your inputs on a recent performance topic we had internally.

When we execute a select statement in X++ including a Group By, AX will generate a SQL query including an extra Order By on every field that we put in the x++ Group by.

For example, the following X++ statement:

DimensionFocusBalanceCalculationView balanceView;
    
select generateonly AccountingDate, Ledger, PostingLayer, FiscalPeriodType,
        FocusLedgerDimension, FocusDimensionHierarchy,
        sum(AccountingCurrencyAmount), sum(ReportingCurrencyAmount),
        sum(Quantity) from balanceView
    group by FocusLedgerDimension, FocusDimensionHierarchy,
        AccountingDate, Ledger, PostingLayer, FiscalPeriodType, IsCredit;
    
info(balanceView.getSQLStatement());


The info output will show the SQL Query (thanks to the generateOnly):

SELECT SUM(T1.ACCOUNTINGCURRENCYAMOUNT),SUM(T1.REPORTINGCURRENCYAMOUNT),SUM(T1.QUANTITY),T1.FOCUSLEDGERDIMENSION,T1.FOCUSDIMENSIONHIERARCHY,T1.ACCOUNTINGDATE,T1.LEDGER,T1.POSTINGLAYER,T1.FISCALPERIODTYPE,T1.ISCREDIT 
	FROM DIMENSIONFOCUSBALANCECALCULATIONVIEW T1 
WHERE ((((PARTITION=5637144576) AND (PARTITION#2=5637144576)) AND (PARTITION#3=5637144576)) AND (PARTITION#4=5637144576))
	GROUP BY T1.FOCUSLEDGERDIMENSION,T1.FOCUSDIMENSIONHIERARCHY,T1.ACCOUNTINGDATE,T1.LEDGER,T1.POSTINGLAYER,T1.FISCALPERIODTYPE,T1.ISCREDIT 
	ORDER BY T1.FOCUSLEDGERDIMENSION,T1.FOCUSDIMENSIONHIERARCHY,T1.ACCOUNTINGDATE,T1.LEDGER,T1.POSTINGLAYER,T1.FISCALPERIODTYPE,T1.ISCREDIT

If we look to the SQL execution Plan, we can see that the "Sort" operation is costing more than 80% of the total cost of the query...


Questions: 

  • We found a workaround, by forcing an order RecId in X++, that will bypass the extra Order By, but we can't customize every query of AX... so we are looking for a global solution!
  • Is there any Server Parameter that can disable the extra Order By clause in the generated query ?

Reproducible on:

  • MS Dynamics AX R2 CU9
  • MS Dynamics AX R3 CU13

Regards

FX