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)