Improving SSRS Report Performance with Set-Based Operations - part 1
Introduction
Ax reports were migrated to Microsoft SSRS in Ax2012, mostly in the form of Report Data Provider classes (RDP). We want to share with our customers and ISVs, through this series of blogs, with some best practices to improve SSRS report performance.
Our goal is to improving report performance without introducing much disruptions or risks to the existing reports. So, the recommended patterns of changes are mostly focus on optimizing data access and reducing duplicates in the scope of your current report designs. In some cases, a report needs to be redesigned for better performance, which is outside the scope of this discussion.
Using Set-Based Operations
Set-based operations in Ax are exponentially faster than the equivalent line based operations. Most RDP reports today access data line by line. In Ax2012 R2, we updated a few reports with set-based data access and observe better performance.
A typical line based logic in a RDP class processReport() method looks like this:
queryRun = new QueryRun(query); while (queryRun.next()) { custTable = queryRun.get(tableNum(CustTable)); custTrans = queryRun.get(tableNum(CustTrans)); this.fetchLedgerTrans(custTrans, custTable.name()); ... } |
And another example – a method in a RDP:
private void insertTaxTransFromTaxGeneralJournal(Query _query) { MainAccount mainAccount; GeneralJournalEntry generalJournalEntry; TaxTrans taxTrans; TaxTransGeneralJournalAccountEntry taxTransGeneralJournalAccountEntry; QueryRun queryRun = new QueryRun(_query); while (queryRun.next()) { taxTrans = queryRun.get(tableNum(TaxTrans)); taxTransGeneralJournalAccountEntry = queryRun.get(tableNum(TaxTransGeneralJournalAccountEntry)); generalJournalEntry = GeneralJournalEntry::findByGeneralJournalAccountEntry( taxTransGeneralJournalAccountEntry.GeneralJournalAccountEntry); mainAccount = queryRun.get(tableNum(MainAccount)); if (taxTransGeneralJournalAccountEntry.TaxTransRelationship == TaxTransRelationshipType::UseTaxPayable) { this.insertTaxTransTempTable(taxTrans, mainAccount, -taxTrans.TaxAmount, generalJournalEntry.JournalNumber); } else { this.insertTaxTransTempTable(taxTrans, mainAccount, taxTrans.TaxAmount, generalJournalEntry.JournalNumber); } } } |
You can re-write it into X++ set-based operations to transform data from source tables into the target tempDB table through selects and joins. A general approach of doing this is to exam the Query object range conditions and trying to express them in terms of set-based expressions. Relations in the query object need to be expressed as join clause in the set-based statement. An example for the above line-based code would be something similar to this:
insert_recordset taxTransTmp ( AccountingDate, Voucher, TaxAmountMST, CustVendAC, TaxAccountName, LedgerJournalId, MainAccountId, MainAccountName ) select TransDate, Voucher, sum(TaxAmount), TaxCode from taxTrans group by TransDate, Voucher, TaxCode where taxTrans.TransDate >= fromDate && taxTrans.TransDate <= toDate && taxTrans.Voucher >= fromVoucher && taxTrans.Voucher <= toVoucher && taxTrans.TaxDirection == TaxDirection::UseTax && (taxTrans.SourceDocumentLine == 0 ||( taxTrans.TaxOrigin == TaxOrigin::Payment || taxTrans.TaxOrigin == TaxOrigin::TaxReversed || taxTrans.TaxOrigin == TaxOrigin::CashDisc)) join TaxName from taxTable group by TaxName where taxTrans.TaxCode == taxTable.TaxCode join taxTransGeneralJournalAccountEntry where taxTrans.RecId == taxTransGeneralJournalAccountEntry.TaxTrans && taxTransGeneralJournalAccountEntry.TaxTransRelationship == TaxTransRelationshipType::UseTaxPayable join generalJournalAccountEntry where taxTransGeneralJournalAccountEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId join JournalNumber from generalJournalEntry group by JournalNumber where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId join davc where taxTransGeneralJournalAccountEntry.LedgerDimension == davc.RecId join MainAccountId, Name from mainAccount group by MainAccountId, Name where mainAccount.RecId == davc.MainAccount && mainAccount.LedgerChartOfAccounts == LedgerChartOfAccounts::current() && mainAccount.MainAccountId >= fromMainAccount && mainAccount.MainAccountId <= toMainAccount; |
However, not all line based RDP providers can be in set-based, for mainly two reasons: first, the use of dynamic parameters in reports. Second, complicated line based logic in X++ code that can’t be expressed in set-based expression, such as exchange rate calculations. In later posts, we will show how to improve the performance using partially set-based approach, which we call it the hybrid pattern.
The two constrains are easy to identify. Here are the details.
Identifying Dynamic Parameters Constraint
When dynamic parameter is enabled for a report, end users can define and modify the query ranges in the report query object. The Report framework runtime will execute the query with user modified query ranges. Such runtime feature is not supported in today’s X++ set-based operations, where all filter conditions are precompiled. A simple way to find out if a report is enabled with dynamic parameter is to run the report and see if the query form has the “Select” button:
Or in VS report designer see if the property is checked.
Of all the 1100 RDP reports shipped in Ax2012, about 700 of them have dynamic parameters enabled.
Ax2012 R3 added support for set-based insert with dynamic parameters. Please refer to Part 6 of this posting series for details.
Identifying Code Logic Constraint
An RDP class typically contains code logic to retrieve and/or calculate field values for each target record line, in a line-by-line way. Generally speaking, such X++ code can’t be expressed in set-based operation clauses. For example, CurrencyExchangeHelper class has the logic that can’t be written in the form of X++ set-based clause expression. When this happens, the line-based code in question has to stay.
In a few cases, a field value that comes from complicate code logic may already exist in some other related table field. If you can identify that duplicating field, you should use that field value. A good example is in the Ax2012 VendAccruedPurchaseDP:
private void insert() { ... vendAccruedPurchasesTmp_NA.ItemId = purchLine.ItemId; vendAccruedPurchasesTmp_NA.ReferenceId = purchTable.PurchId; vendAccruedPurchasesTmp_NA.ItemName = inventTable.itemName(); vendAccruedPurchasesTmp_NA.VendName = dirPartyTable.Name; ... vendAccruedPurchasesTmp_NA.insert(); } |
The table method itemName() contains the logic that can’t be expressed as set-based. A proposed option of using set-based in this case is to use PurchLine.Name.
In many cases, table methods, such as find(), are used only to get values, line-by-line, through a database select statement. Such select statement can become an exist-join clause to the set-based operation. This is discussed later in this blog.
Replacing Line-based code with Set-based
Finally, if none of the above two constrains applies to a report, you should be able to transform the report into set-based. We identified a few reports shipped in Ax2012 to be the candidates. Here are some of them:
TaxLedgerReconciliation, VendLedgerReconciliation, BankLedgerReconciliation, CustLedgerReconciliation, LedgerAccountBalanceSheetByDim, BankAccountStatement, etc.
In Ax2012 R2, we optimized three reports by using some set-based operations: LedgerTrialBalance, VendAccruedPurch, and LedgerTransStatement. We will continue improving SSRS reports in the future releases.
(to be continued)