- Set-Based Database Execution: Transforming multi-table query loops into single SQL Server
INSERT INTO ... SELECTstatements. - Drastic Latency Reduction: Eliminating round-trip client-server chatter when populating reporting temporary tables.
- Dynamic Map Field Binding: Mapping target temporary table fields to specific query datasource unique IDs and field identifiers.
- Multi-Table Joins Support: Joining complex tables (e.g.
TrvExpTable,TrvExpTrans,ProjTable,HcmWorker,DirPerson) in a single set operation. - Production Best Practice: The gold standard for SSRS data provider population and high-volume batch data pipelines.
Overview: High-Performance Set-Based Operations
In the realm of enterprise ERP performance optimization, row-by-row iteration (e.g., executing while select loops with individual doInsert() calls) is one of the most common causes of batch bottlenecks and slow report execution.
The X++ query framework offers a powerful set-based operation known as Query::insert_recordset. This method allows developers to populate a target table directly from a multi-table Query object in a single round-trip to the SQL Server database.
“Executing set-based inserts via Query::insert_recordset reduces database latency by orders of magnitude, turning minutes of row-by-row inserts into a sub-second SQL operation.”
Complete X++ Implementation Code
The following production-tested code demonstrates building a multi-table query (joining TrvExpTable, TrvExpTrans, ProjTable, HcmWorker, and DirPerson), mapping fields via a Map container, and executing Query::insert_recordset:
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);
Architectural Breakdown & Best Practices
query.clearAllFields(): Always clear default fields before adding specific selection fields to minimize data payload.qbds.uniqueId(): Using the datasource's unique ID in the mapping map guarantees unambiguous binding across self-joins or multiple tables.- Database Constraints: Ensure that the target table does not have active database logging or row-level insert triggers that force the kernel to fall back to row-by-row mode.