Share
Report
425Hello 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
*This post is locked for comments
All responses (
