Loading
In the realm of performance optimization, the AX query system offers a feature known as query::insert_recordset. This functionality enables the simultaneous insertion of multiple records into a table via a single SQL call, similar to the functionality offered by the insert_recordset command. This will also eliminate the need to loop, as well as roundtrips between the SQL server and the AOS server. Below is a quick demo how query::insert_recordset works:
Query query = new Query(queryStr(MDTrvExpenseDetailsQuery)); QueryRun queryRun; QueryBuildDataSource qbdsTrvExpTrans, qbdsTrvExpTable, qbdsProjTable, qbdsHcmWorker, qbdsDirPerson; Map fieldMapping; HcmPositionWorkerAssignment hcmPositionWorkerAssignment; HcmPosition hcmPosition; MDTrvExpenseDetailsContract contract = this.parmDataContract() as MDTrvExpenseDetailsContract; paymentStatus = contract.parmPaymentStatus(); // Prepare the data source fields for record set operation query.clearAllFields(); qbdsTrvExpTable = query.dataSourceTable(tableNum(TrvExpTable)); qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, ExpNumber)); qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, PaymentStatus)); qbdsTrvExpTrans = query.dataSourceTable(tableNum(TrvExpTrans)); qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, AmountMST)); qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, ApprovalStatus)); qbdsProjTable = query.dataSourceTable(tableNum(ProjTable)); qbdsProjTable.addSelectionField(fieldNum(ProjTable, ProjId)); qbdsProjTable.addSelectionField(fieldNum(ProjTable, Name)); qbdsHcmWorker = query.dataSourceTable(tableNum(HcmWorker)); qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, PersonnelNumber)); qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, RecId)); qbdsDirPerson = query.dataSourceTable(tableNum(DirPerson)); qbdsDirPerson.addSelectionField(fieldNum(DirPerson, Name)); //Add range on the Payment status of travel expenses table qbdsTrvExpTable.addRange(fieldNum(TrvExpTable, PaymentStatus)).value(queryValue(paymentStatus)); fieldMapping = new Map(Types::String, Types::Container); //Specify the mapping between target and source fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Amount), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, AmountMST)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ApprovalStatus), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, ApprovalStatus)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ExpNumber), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, ExpNumber)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PaymentStatus), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, PaymentStatus)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjId), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, ProjId)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjName), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, Name)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PersonnelNumber), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, PersonnelNumber)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Worker), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, RecId)]); fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Name), [qbdsDirPerson.uniqueId(), fieldStr(DirPerson, Name)]); Query::insert_recordset(mdTrvExpenseDetailsTmp, fieldMapping, query);