We post it second because it is a low hanging fruit for better performance. One example helps explains this pattern. Here is a code excerpt from CustTransOpenPerDate report.

perDate = contract.parmPerDate();

while (queryRun.next())

    {

        if (queryRun.changed(tablenum(CustTable)))

        {

            custTable = queryRun.get(tablenum(CustTable));

        }

        custTrans = queryRun.get(tablenum(CustTrans));

 

        if (custTrans.TransDate <= perDate   &&

           (custTrans.Closed   == dateNull() ||

            custTrans.Closed   > perDate))

        {

         …

            this.insertCustTransOpenPerDateTmp();

        }

    }

 

In one case, there are over 8000 CustTrans lines fetched but only 41 lines are added to the report. In this example, more lines are retrieved less restrictively and filtered later in code instead. Most lines are discarded anyway, resulting in excessively unnecessary DB access.

The fix is to remove the if-condition inside the main loop and add perDate to the queryRun object data range.

perDate = contract.parmPerDate();

// new code:

qbdsCustTrans = SysQuery::findOrCreateDataSource(queryRun.query(), tableNum(CustTrans));

qbdsCustTrans.addRange(fieldNum(CustTrans, TransDate)).value(queryRange(null, perDate));

qbdsCustTrans.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull()));

qbdsCustTrans.addRange(fieldNum(CustTrans, Closed)).value(strFmt('>%1', queryValue(perDate)));

 

while (queryRun.next())

    {

        if (queryRun.changed(tablenum(CustTable)))

        {

            custTable = queryRun.get(tablenum(CustTable));

        }

        // delete old code

        custTrans = queryRun.get(tablenum(CustTrans));

 

        if (custTrans.TransDate <= perDate   &&

           (custTrans.Closed   == dateNull() ||

            custTrans.Closed   > perDate))

        {

         …

            this.insertCustTransOpenPerDateTmp();

        }

    } 

 

The key thing to look for this pattern is to see if every iteration of the main loop results in one or more insert/update to the report data temp table. If not, then you need to identify skipping conditions and see if the conditions can be represented in terms of the main loop query range expression.

Ax2012 R2 improved CustDueReportDetail report this way. Here are the high light of the old code. You can test yourself to see if how you would improve it using this pattern.

    while(queryRun.next())

    {

             // deleted irrelevant code here

            while select SettleAmountCur, DueDate, UtilizedCashDisc, TransDate

                from custSettlement

                where custSettlement.TransRecId == custTrans.RecId &&

                        ((asOfDate && custSettlement.TransDate > asOfDate) || !asOfDate)

            {

                if(!(setRecord.in(custTrans.RecId) || setRecord.in(custTrans.OffsetRecid)))

                {

                    transDate = asOfDate ? custTrans.TransDate : custSettlement.TransDate;

                    // deleted irrelevant code here

                    if(!(asOfDate && transDate >= asOfDate))

                    {

                       this.insertCustDueReportDetailTmp(

                             custSettlement.SettleAmountCur,

                             asOfDate && custTrans.DueDate ? custTrans.DueDate : custSettlement.DueDate,

                             custSettlement.UtilizedCashDisc,

                             transDate);

                     }

                }

            }

                // deleted irrelevant code here

                while select AmountCur, DueDate, PossibleCashDisc, TransDate

                    from custTransOpen

                    where custTransOpen.RefRecId == custTrans.RecId &&

                            ((asOfDate && custTransOpen.TransDate <= asOfDate) || !asOfDate)

                {

                    this.insertCustDueReportDetailTmp(

                            custTransOpen.AmountCur,

                            custTransOpen.DueDate,

                            custTransOpen.PossibleCashDisc,

                            custTransOpen.TransDate);

                }

        }

    }

 

Another case in this pattern is trying to do aggregation in SQL instead of in X++ code. LedgerAccountBlanceSheetByDim report can be re-written in set-based, but the line-based fix in this pattern also yields huge performance gain:

In method LedgerAccountBlanceSheetByDim.prepareTmpTableData(), a while loop iterates over GJAE table to total up the amount.

while select AccountingCurrencyAmount, isCredit from generalJournalAccountEntry

{ // code that add up the Amount...   

}

 

The improved code uses SQL aggregation, which improved the speed in multy folds:

    while select sum(AccountingCurrencyAmount) from generalJournalAccountEntry

        group by generalJournalAccountEntry.IsCredit, mainAccount.MainAccountId, mainAccount.RecId, dimensionAttributeLevelValueView.DisplayValue

        order by mainAccount.MainAccountId, dimensionAttributeLevelValueView.DisplayValue

{ // no more code to add up.

    ...

}