insert_recordset, update_recordset, delete_from: X++ performance

In X++, insert_recordset, update_recordset and delete_from act on a set of records in a single database round trip, instead of a while select loop calling insert(), update() or delete() for each row. On large volumes, it is often the code change that brings the biggest performance gain.

But these statements automatically fall back to row-by-row processing in several cases: an overridden insert/update/delete method on the table, database log enabled, alerts set up, delete actions, non-SQL tables or valid-time-state tables. The skipDataMethods, skipDatabaseLog, skipEvents and skipDeleteActions methods let you ignore some of these factors, to be used knowingly.

insert_recordset copies data from one or more source tables into a target table in one round trip. The target field list must match, in order, the selected field list; missing fields get 0 or empty, and RecId is assigned by the kernel. Microsoft Learn example, aggregating by name:

ValueSumByName valueSumName;
NameValuePair  nameValuePair;

insert_recordset valueSumName (Name, ValueSum)
    select Name, sum(Value)
    from nameValuePair
    group by Name;

You can also join several tables and insert variable values (with firstonly for a single row). Literals such as 128 or "text" are not accepted as a source. If you need to transform each row before inserting, RecordInsertList or RecordSortedList (insertDatabase) also batch inserts into one call, with more flexibility but less speed than insert_recordset.

update_recordset changes several rows in one statement, like an SQL UPDATE … SET, and accepts joins to feed the values. Learn example:

CustTable custTable;

ttsBegin;
update_recordset custTable
    setting CreditMax = custTable.CreditMax + 1000
    where custTable.CreditMax > 0;
ttsCommit;

delete_from removes every row matching the condition, with optional exists join or notexists join. The contrast with a loop:

// Avoid: one SQL call per row
ttsBegin;
while select forUpdate tabWidget
    where tabWidget.quantity <= 100
{
    tabWidget.delete();
}
ttsCommit;

// Prefer: a single statement
ttsBegin;
delete_from tabWidget
    where tabWidget.quantity <= 100;
ttsCommit;

According to Microsoft Learn, the conversion to row-by-row processing happens in these cases:

• non-SQL tables (for example in-memory temporary tables): for all three statements;
• delete actions: for delete_from only, bypassed with skipDeleteActions;
• database log enabled on the table: bypassed with skipDatabaseLog;
• overridden insert, update or delete method: bypassed with skipDataMethods;
• alerts set up on the table: bypassed with skipEvents;
• ValidTimeStateFieldType property other than None: no bypass.

Note: if the conversion happens anyway, the skip* settings are ignored. And skipping data methods also skips the business logic they contain; only do it if you know that logic does not need to run.

• Writing an update_recordset on a table whose update() is overridden and assuming the operation is set-based: it runs as a loop.
• Adding skipDataMethods(true) to "speed up" without checking what the overridden method does: checks and related updates are skipped.
• Forgetting ttsBegin / ttsCommit around updates and deletes.
• Misaligning the field order between the target and the select of an insert_recordset.
• Filling a report's temporary table with an insert() loop: an insert_recordset is often possible. See SSRS report with RDP and Contract classes.

Purge, recalculation or copy batch jobs, filling report temporary tables, data migrations: see SysOperation batch job step by step. In a code review, whether the code was written by a person or generated by AI, "can this be set-based?" is one of the basic checks. Skalp AI generates X++ code with unit tests, delivered to your Azure DevOps and validated by your IT lead: see AI X++ development, building X++ without a consultant in 2026 and the Basic, Pro and Max plans.

• Microsoft Learn, "Insert data", updated 31 Mar 2026, accessed 11 Oct 2026.
• Microsoft Learn, "Update data", updated 31 Mar 2026, accessed 11 Oct 2026.
• Microsoft Learn, "Delete data", accessed 11 Oct 2026.
• Microsoft Learn, "Conversion of operations from set-based to record-by-record", updated 31 Mar 2026, accessed 11 Oct 2026.