Improving SSRS Report Performance with Hybrid of Line-based and Set-based Processing – Part 4
This pattern applies to a wide range of reports. It is the most applied pattern so far as based on our experience. The idea is to isolate line-based code to the minimum and leverage set-based operations to the maximum at the same time. Here is a list of approaches.
Working with Dynamic Parameters
As it is mentioned in an earlier blog (part 1), Ax2012 set-based operations are X++ language elements compiled into SQL at compile time. They do not take, as input, the dynamic query ranges from users at run time. To leverage set-based operations, what you can do is to keep the original line-base loop but move every possible set-based process outside this loop. We improved the trial balance report In Ax2012 R2 this way.
In the trial blance report, it first detects of any dynamic parameter is added by calling a new method queryHasRangesOrFilters(). If it is, the main queryRun.next() loop is executed but only to collect those filtered line ides from GL table. Then, the rest of the process are changed to set-based.
The line-by-line loop on queryRun.next() can’t be replaced whenever dynamic parameter is enabled. But you should make this loop lean, only to process those that must be line-based and leave the rest to set-based processing. Vend report in Ax 2012 R2 is another example.
Note: Ax2012 R3 enables set-based insert with dynamic parameters. See post Part 6 on this series for detail.
public void processReport() { QueryRun queryRun; queryRun = new QueryRun(this.parmQuery()); ttsbegin; while(queryRun.next()) { vendorListBasicTmp.clear(); vendTable = queryRun.get( tableNum(VendTable)); this.insertIntoTempTable(); } ttscommit; }
private void insertIntoTempTable() { vendorListBasicTmp.VendAccount = vendTable.AccountNum; vendorListBasicTmp.Name = vendTable.name(); vendorListBasicTmp.Phone = vendTable.phone(); vendorListBasicTmp.Email = vendTable.email(); vendorListBasicTmp.Telefax = vendTable.telefax(); vendorListBasicTmp.Telex = vendTable.telex(); vendorListBasicTmp.InvoiceAccountName = vendTable.invoiceAccountName(); vendorListBasicTmp.ItemBuyerGroupId = vendTable.ItemBuyerGroupId; vendorListBasicTmp.Currency = vendTable.currencyName(); vendorListBasicTmp.CashDisc = vendTable.cashDiscName(); vendorListBasicTmp.PriceGroupName = vendTable.priceDiscGroupName(); vendorListBasicTmp.LineDiscName = vendTable.lineDiscName(); vendorListBasicTmp.MultiLineDiscName = vendTable.multiLineDiscName(); vendorListBasicTmp.EndDiscName = vendTable.endDiscName(); vendorListBasicTmp.TaxGroupName = vendTable.taxGroupName(); vendorListBasicTmp.DeliveryMode = vendTable.dlvModeName(); vendorListBasicTmp.AccountName = vendTable.customerAccountName(); logisticsPostalAddress = DirParty::primaryPostalAddress(vendTable.Party); vendorListBasicTmp.Address = logisticsPostalAddress.Address; vendorListBasicTmp.CountyName= LogisticsAddressCounty::find(logisticsPostalAddress.CountryRegionId,logisticsPostalAddress.State,logisticsPostalAddress.County).Name; vendorListBasicTmp.StateName = LogisticsAddressState::name(logisticsPostalAddress.CountryRegionId,logisticsPostalAddress.State); vendorListBasicTmp.CountryName = LogisticsAddressCountryRegion::name(logisticsPostalAddress.CountryRegionId); vendorListBasicTmp.PaymentTerm = PaymTerm::find(vendTable.PaymTermId).Description; vendorListBasicTmp.insert(); }
|
All the method calls in insertIntoTempTable() method are database find() type of methods, which can be expressed as join clauses in a set-based statement. To use set-based data access when dynamic parameter is enabled, you first store all the VentdTable line recIDs, and available field valuess, in the temp table without calling any method. The second step is to do set-based update over the first results to join with other tables to get the rest of the field values.
Please note that replacing a line-base table display method duplicates the business logic in the method code in the form of set-based expressions. Such denormalization is a tradeoff for performance. Developers need to make the call based on the each scenario.
Table Methods of Database Operation
As mentioned above in Vend report, a table method of database operation, such as look up or aggregate, can be replaced with a join clause in the set-based operation. One typical example is find() method that most tables have. Basically, the loop up logic in the table function needs to be repeated by an exist-join clause in the set-based statement, such as this code snip from VendAccruedPurchasesDP_NA.
physicalDate = AccountingEvent::findByAccountingEventType( SourceDocumentLine ::find( vendPackingSlipTrans.SourceDocumentLine).SourceDocumentHeader, AccountingEventType::Original).AccountingDate; financialDate = AccountingEvent::findByAccountingEventType(SourceDocumentLine::find( vendPackingSlipTrans.SourceDocumentLine).SourceDocumentHeader, AccountingEventType::Finalize).AccountingDate; if ( (physicalDate && physicalDate <= cutOffDate) && (financialDate > cutOffDate || !financialDate)) { bSend = true; } else { if ( (physicalDate > cutOffDate || !physicalDate) && (financialDate <= cutOffDate && financialDate)) { bSend = true; } } |
The look up and the subsequent comparison need to be converted to table join clauses of the insert_recordset statement, such as this:
#LOCALMACRO.OrigAndFinalizeExist exists join origEvent where origEvent.SourceDocumentHeader == vendPackingSlipTransSDL.SourceDocumentHeader && origEvent.Type == AccountingEventType::Original exists join finalEvent where finalEvent.SourceDocumentHeader == vendPackingSlipTransSDL.SourceDocumentHeader && finalEvent.Type == AccountingEventType::Finalize && ((origEvent.AccountingDate <= cutOffDate && finalEvent.AccountingDate > cutOffDate) || (origEvent.AccountingDate > cutOffDate && finalEvent.AccountingDate <= cutOffDate)) |
Set-based approach in the Nested Loops
Many report RDP classes have nested loops. The outer loop iterates over the result of queryRun object line-by-line. And then for each line there is a nested inner loop, which is mostly un-related to the dynamic parameter as the outer loop does. In most cases, such a nested loop can be in set-based to reduce the X++ code complicity from O(n*n) to O(n).
Report examples of this type are CustBillofExchange, TaxPurchLedger, CustLedgerTrans, etc.
Sometimes, the nested loop is wrapped in a private method call. Here is an example from LedgerTransStatementDP class:
private LedgerJournalAC getOffsetAccount(TmpDimTransExtract _tmpDimTransExtract) { ... while select MainAccountId from mainAccount group by mainAccount.MainAccountId where mainAccount.MainAccountId != _tmpDimTransExtract.AccountMain && mainAccount.LedgerChartOfAccounts == LedgerChartOfAccounts::current() exists join dimensionAttributeValueCombination where dimensionAttributeValueCombination.MainAccount == mainAccount.RecId exists join generalJournalAccountEntry where dimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension exists join generalJournalEntry where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry && generalJournalEntry.Ledger == Ledger::current() exists join subledgerVoucherGeneralJournalEntry where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId && subledgerVoucherGeneralJournalEntry.Voucher == _tmpDimTransExtract.Voucher && subledgerVoucherGeneralJournalEntry.AccountingDate == _tmpDimTransExtract.TransDate { // An account was found that is not the account on the current calculation group. // Add the offset account to the set. Because it's a set each unique account is only added once. offsetAccountSet.add(mainAccount.MainAccountId);
if (offsetAccountSet.elements() > 3) { // Use 'Multiple' when there are more than 3 offset accounts. offsetAccount = "@SYS78137"; break; } }
if (offsetAccount == '') { // Generate the string to display with the offset accounts that were found. setEnumerator = offsetAccountSet.getEnumerator(); while (setEnumerator.moveNext()) { if (strLen(offsetAccount) != 0) { offsetAccount += ', '; } offsetAccount += setEnumerator.current(); } } return offsetAccount; } |
This method is called per line. The equivalent set-based code as implemented in Ax2012 R2 appears quite different:
private void processOffsetAccount(LedgerTransStatementTmp _ledgerTransStatementTmp) { LedgerTransStatementOffsetAccountTmp offsetAccountTmp; ... #LOCALMACRO.JoinGJAE exists join dimensionAttributeValueCombination where dimensionAttributeValueCombination.MainAccount == mainAccount.RecId exists join generalJournalAccountEntry where generalJournalAccountEntry.LedgerDimension == dimensionAttributeValueCombination.RecId && generalJournalAccountEntry.GeneralJournalEntry == offsetAccountTmp.GeneralJournalEntry && generalJournalAccountEntry.RecId != offsetAccountTmp.GeneralJournalAccountEntry; ENDMACRO this.setUserConnection(offsetAccountTmp); // Start by pushing all data to process into a seperate temp table where // the individual account values can be dropped into individual columns insert_recordset offsetAccountTmp (GeneralJournalEntry, GeneralJournalAccountEntry) select GeneralJournalEntry, GeneralJournalAccountEntry from _ledgerTransStatementTmp where _ledgerTransStatementTmp.createdTransactionId == this.parmCreatedTransactionId(); // Fill in the first offset account (where possible) update_recordSet offsetAccountTmp setting OffsetAcct1 = mainAccount.MainAccountId join mainAccount #JoinGJAE // Fill in the second offset account (where possible) update_recordSet offsetAccountTmp setting OffsetAcct2 = mainAccount.MainAccountId join mainAccount where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1 #JoinGJAE // Fill in the third offset account (where possible) update_recordSet offsetAccountTmp setting OffsetAcct2 = mainAccount.MainAccountId join mainAccount where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1 && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct2 #JoinGJAE
// Remove all cases where more than three accounts exist update_recordSet offsetAccountTmp setting IsMoreThanThree = NoYes::Yes, OffsetDescription = "@SYS78137" // Multiple exists join mainAccount where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1 && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct2 && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct3 #JoinGJAE // Concatenate the values into a total update_recordset offsetAccountTmp setting OffsetDescription = offsetAccountTmp.OffsetAcct1 where offsetAccountTmp.OffsetAcct1 && offsetAccountTmp.IsMoreThanThree == NoYes::No; update_recordset offsetAccountTmp setting OffsetDescription = offsetAccountTmp.OffsetDescription + ',' + offsetAccountTmp.OffsetAcct2 where offsetAccountTmp.OffsetAcct2 && offsetAccountTmp.IsMoreThanThree == NoYes::No; update_recordset offsetAccountTmp setting OffsetDescription = offsetAccountTmp.OffsetDescription + ',' + offsetAccountTmp.OffsetAcct3 where offsetAccountTmp.OffsetAcct3 && offsetAccountTmp.IsMoreThanThree == NoYes::No; // Copy the descriptions back to the source table update_recordSet _ledgerTransStatementTmp setting OffsetAccount = offsetAccountTmp.OffsetDescription where _ledgerTransStatementTmp.createdTransactionId == this.parmCreatedTransactionId() join offsetAccountTmp where offsetAccountTmp.GeneralJournalAccountEntry == _ledgerTransStatementTmp.GeneralJournalAccountEntry && offsetAccountTmp.GeneralJournalEntry == _ledgerTransStatementTmp.GeneralJournalEntry; } |
Notice the use of a temp table, LedgerTransStatementOffsetAccountTmp, which stores the intermediate result. Such a table is often needed for set-based approach.
The set-based approach is not straightforward, but it is much faster than the line-based method call.
Aggregation and Sequencing
In a line-based processing loop, it is easy to aggregate a total or a rolling total in a variable. It is done differently in the set-based approach.
To get the summary in the temp table, it is often done in two steps: inserting all the detail lines first and followed by inserting summary lines in the same table. You can add a flag field to the table, such as isFinalRecord, to separate the aggregated values with detail values. In the end, you delete all the detail lines before rendering the report. In LedgerTransStatement report (R2 code), the set based operations are in stages: retrieve and process the raw detail records, and then grouping. In the end in recorderRows() method, only aggregated lines are kept:
// Remove the original records that created the balances delete_from newTmp where newTmp.IsFinalRecord == NoYes::No && newTmp.createdTransactionId == this.parmCreatedTransactionId(); |
A rolling total, or a current amount, depends on the order of the result set. We can define an incremental id field to tag the temp table based on the order-by. Then do set-based with aggregation on those that has the id value smaller than the current row.
See Ax6.2 code example in LedgerTrialBalanceDP.updateAccumulatedAmounts():
private void updateAccumulatedAmounts(LedgerTrialBalanceStagingTmp _ledgerTrialBalanceStagingTmp) { LedgerTrialBalanceTmpAccumulated tmpAccumulated; LedgerTrialBalanceStagingTmp ledgerTrialBalanceStagingTmpReference;
this.setUserConnection(ledgerTrialBalanceStagingTmpReference); ledgerTrialBalanceStagingTmpReference.linkPhysicalTableInstance(_ledgerTrialBalanceStagingTmp);
this.setUserConnection(tmpAccumulated);
insert_recordset tmpAccumulated (LedgerTrialBalanceTmp, AccumulatedDebit, AccumulatedCredit) select RecId from _ledgerTrialBalanceStagingTmp group by _ledgerTrialBalanceStagingTmp.RecId join sum(AmountDebitTrans), sum(AmountCreditTrans) from ledgerTrialBalanceStagingTmpReference where ledgerTrialBalanceStagingTmpReference.PrimaryFocus == _ledgerTrialBalanceStagingTmp.PrimaryFocus && ledgerTrialBalanceStagingTmpReference.RecId <= _ledgerTrialBalanceStagingTmp.RecId;
update_recordSet _ledgerTrialBalanceStagingTmp setting Accumulated = tmpAccumulated.AccumulatedDebit - tmpAccumulated.AccumulatedCredit join tmpAccumulated where tmpAccumulated.LedgerTrialBalanceTmp == _ledgerTrialBalanceStagingTmp.RecId;
// Clear out the AmountDebitTrans and AmountCreditTrans from opening transactions update_recordSet _ledgerTrialBalanceStagingTmp setting OpeningBalance = _ledgerTrialBalanceStagingTmp.AmountDebitTrans - _ledgerTrialBalanceStagingTmp.AmountCreditTrans, AmountDebitTrans = 0, AmountCreditTrans = 0 where _ledgerTrialBalanceStagingTmp.TransactionType == FiscalPeriodType::Opening; } |
Working with Complicated Code Logic
We already know that some line-based method calls contains only database look up logic. They can be combined into join clauses in the set-based operations. However, whenever there is line-based code logic, it is often hard or impossible to rewrite it into set-based without affecting fundamental logic of the report and data schema. Examples are rounding and exchange rate code. They will remain as line-based.
For example, display method InventTable.itemName() has code logic that can’t be in set-based. Reports that use it must keep the call in the line-based loop. The general task for developers is to identify what code can be in set-based and what must stay as line-based.