Set based operations for better performance

Set based operations with insert_recordset, update_recordset and delete_recordset are executed with a single SQL statement and a single round trip to the SQL Server database. That's why they are so fast. Even if millions of records are affected, only a single SQL statement is sent to the backend database SQL Server.

For example, let's say you are inserting data into a table using the insert_recordset statement but the insert method of the target table contains some code. In this case, when using a set based operation, by default the system will fall back to a "row by row" operation meaning that each row will result in a separate SQL statement and round trip to the database server.

That means that there is no significant performance gain compared to a while loop statement (row by row operation). If the insert method contains code and you want to force a set based operation, and the code in the insert method can be skipped, you can use the .skip methods on the table buffer of the target table. E.g. .skipDataMethods(true). See the table below, it's for AX2012 but I believe it's 100% still relevant for F&O.

Maintain Fast SQL Operations
Some text sourced from: https://learn.microsoft.com/en-us/previous-versions/dynamicsax-2012/developer/maintain-fast-sql-operations?WT.mc_id=BA-MVP-5003976