Malaysia: Best way inserting query to temporary table to improve performance

[Blog bot]

Источник: http://axmas.blogspot.com/2017/07/us...in-r2-for.html
==============

Use Query::insert_recordset in R2 for report best practice

insert_recordset and update_recordset will expedite the report performance, and to insert data from query to temp table, have to use Query::insert_recordset.

Understand that from forum it is only available in R3. However I have tried in R2 and surprised it is working.
My code show as below:

[SysEntryPointAttribute]
public void processReport()
{
SFA_MembershipTransContract contract;
SFA_MembershipTrans membershipTrans;
RetailTransactionTable retailTable;
RetailTransactionSalesTrans retailSalesTrans;
RetailTransactionPaymentTrans retailPaymentTrans,retailPaymentTrans2,retailPaymentTrans3,retailPaymentTrans4;
Query query,q;
QueryRun queryRun;
RecordInsertList insList;
QueryBuildDataSource qbds, qbds2;
QueryBuildFieldList fieldList;
Map fieldMapping;
SFA_RetailSalesTransTmp salesTransTmp;
SFA_RetailSalesTransCountTmp STCountTmp;
SFA_RetailPaymentTransTmp paymTransTmp,paymTransTmp2,paymTransTmp3,paymTransTmp4;
;

query = this.parmQuery();

qbds = query.dataSourceTable(tableNum(SFA_MembershipTrans));
queryRun = new QueryRun(query);


qbds = query.dataSourceTable(tableNum(SFA_MembershipTrans));

q = new Query(this.parmQuery());
fieldList = qbds.fields();
fieldList.addField(fieldNum(SFA_MembershipTrans, RecId));
fieldList.addField(fieldNum(SFA_MembershipTrans, Remarks));

fieldList.dynamic(QueryFieldListDynamic::No);

fieldMapping = new Map(Types::String, Types::Container);
fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, MemberTransRefRecId), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, RecId)]);
fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, Remarks), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, Remarks)]);

query::insert_recordset(tmpTbl,fieldMapping,q);

ttsBegin;
update_recordset tmpTbl setting BatchID = membershipTrans.BatchID,BatchName = membershipTrans.BatchName,CardId = membershipTrans.CardId, CardType = membershipTrans.CardType,
ClubHouse = membershipTrans.ClubHouse, ICNum = membershipTrans.ICNum, InvoiceId = membershipTrans.InvoiceId, MemberStatus = membershipTrans.MemberStatus,
MemberTypeCode = membershipTrans.MemberTypeCode, ModuleType = membershipTrans.ModuleType, NumberOfMonths = membershipTrans.NumberOfMonths, NumberOfYears = membershipTrans.NumberOfYears,
PeriodEndDate = membershipTrans.PeriodEndDate, PeriodStartDate = membershipTrans.PeriodStartDate, PlanDate = membershipTrans.PlanDate, ProcessDate = membershipTrans.ProcessDate,
Processed = membershipTrans.Processed, PromotionId = membershipTrans.PromotionId, ReasonCode = membershipTrans.ReasonCode, ReasonId = membershipTrans.ReasonId,
ReceiptId = membershipTrans.ReceiptId, RenewalType = membershipTrans.RenewalType, RetailLoyaltyCustId = membershipTrans.RetailLoyaltyCustId, SalesId = membershipTrans.SalesId,
SFA_Card = membershipTrans.SFA_Card, SourceType = membershipTrans.SourceType, TransDate = membershipTrans.TransDate, Valid = membershipTrans.Valid, PaymentVoucher = membershipTrans.PaymentVoucher
join membershipTrans where membershipTrans.RecId == tmpTbl.MemberTransRefRecId;

insert_recordset salesTransTmp (receiptId,netAmountInclTax)
select receiptId, sum(netAmountInclTax) from retailSalesTrans
group by retailSalesTrans.ReceiptId
join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId;

insert_recordset STCountTmp (receiptId,NoCount)
select receiptId, count(RecId) from retailSalesTrans
group by retailSalesTrans.ReceiptId
join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId && retailSalesTrans.ItemId like "MER-0*";

update_recordset salesTransTmp setting NoCount = STCountTmp.NoCount
join STCountTmp where STCountTmp.ReceiptId == salesTransTmp.ReceiptId;

update_recordset tmpTbl setting AmountMST = salesTransTmp.netAmountInclTax
join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId;

update_recordset tmpTbl setting PaymReference = "Multiple"
join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId && salesTransTmp.NoCount > 1;


insert_recordset paymTransTmp (receiptId,amountMST)
select receiptId, sum(amountMST) from retailPaymentTrans
group by retailPaymentTrans.receiptId
join tmpTbl where retailPaymentTrans.ReceiptId == tmpTbl.ReceiptId;

update_recordSet paymTransTmp setting SFA_PaymMode1 = retailPaymentTrans.SFA_PaymMode, SFA_PaymMode = retailPaymentTrans.SFA_PaymMode
join firstonly retailPaymentTrans where paymTransTmp.receiptId == retailPaymentTrans.ReceiptId;

update_recordSet paymTransTmp setting SFA_PaymMode2 = retailPaymentTrans2.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans2.SFA_PaymMode
join firstonly retailPaymentTrans2 where retailPaymentTrans2.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans2.SFA_PaymMode != paymTransTmp.SFA_PaymMode1;

update_recordSet paymTransTmp setting SFA_PaymMode3 = retailPaymentTrans3.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans3.SFA_PaymMode
join firstonly retailPaymentTrans3 where retailPaymentTrans3.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode2;

update_recordSet paymTransTmp setting SFA_PaymMode4 = retailPaymentTrans4.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans4.SFA_PaymMode
join firstonly retailPaymentTrans4 where retailPaymentTrans4.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode2 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode3;

update_recordset tmpTbl setting PaymMode = paymTransTmp.SFA_PaymMode, PaymAmountMST = paymTransTmp.amountMST
join paymTransTmp where paymTransTmp.ReceiptId == tmpTbl.ReceiptId;

ttsCommit;
}


*Add correct index to temporary table will also improve the performance


My AX version between CU6 - CU7

Источник: http://axmas.blogspot.com/2017/07/us...in-r2-for.html