GuruGroup logo
X++ High-Performance Engineering

How to use query::insert_recordset in D365FO

Executive Summary & Key Architectural Takeaways
  • Set-Based Database Execution: Transforming multi-table query loops into single SQL Server INSERT INTO ... SELECT statements.
  • 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.”

— Muhammad Hammad, Dynamics 365 Technical Consultant
Performance Comparison: Row-by-Row vs. Query::insert_recordset
Row-by-Row while select Loop Transfers records across the network to AOS memory, creates cursor locks, and issues individual SQL INSERT statements. Catastrophic for datasets exceeding thousands of rows.
Set-Based Query::insert_recordset Compiles directly into an optimized SQL Server INSERT INTO ... SELECT statement executed entirely inside the database engine with zero network roundtrips.

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.
Ready to Move Forward?

Turn Your Business Goals into Measurable Results

Partner with Guru Group to design, deliver, and scale secure technology solutions for your organization.