In this article, learn how to use update_recordset in D365. The update_recordset keyword in x++ is used when you need to update many records in a table quickly with the best performance. It is extremely fast because it generates a single SQL statement that is sent to the server once.
Alternatively, developers sometimes write a ‘while select‘ statement to loop through source tables. Then, inside the loop, they set the fields of a table buffer and call the update() method. While there is often code in the update method that requires this approach, the process is much slower than using update_recordset. This is because each time through the ‘while select’ loop, the system needs to select and update the data. Additionally, each update requires a round-trip message with the SQL server. In this article, I will explain when to use each approach.
How To Update Data In D365
Before learning how to use update_recordset in D365, I recommend you start by reading my article “How to Update Data In D365“.
To remind you, in x++ the most common approach is to populate table buffer variables with data. Then, call the ‘update‘ method on that table buffer.
At that point, the system creates a SQL statement that updates that data into the corresponding table in the database.
The system also sets specific system fields for us automatically, such as ModifiedDateTime and ModifiedBy, if those fields exist.
As shown, using table buffer variables is a great way for developers to update table data easily.
Single Call Versus Multiple Calls
Oftentimes, calling ‘update‘ on a table buffer is acceptable because only a single record is being updated. However, there are other times when it is a lot faster to use update_recordset in D365 to update the data. This is because calling update_recordset makes a single database call, rather than one for each record.
The best way to explain is to look at a few examples.
Multiple Calls Using Insert
First, create a runnable class (job) to update the CustTable table.
Second, name the job ‘tutorial_Update‘.
For this example, you may want to update the default Site for a particular group of existing customers.
class tutorial_update
{
public static void main(Args _args)
{
CustTable custTable;
while select forupdate custTable
where custTable.InventSiteId == 'EAST'
{
ttsbegin;
custTable.InventSiteId = 'WEST';
custTable.update();
ttsbegin;
info(strFmt("Customer %1 site changed to WEST", custTable.AccountNum));
}
}
}
Notice, the code loops through each record in the CustTable table where the InventSiteId is set to ‘EAST’, sets the InventSiteId field to ‘WEST’, then calls the ‘update‘ method.
In this example, every time the update method is called, the system generates a SQL statement and executes it against the database. The update method gets called every time the while loop is entered.
Therefore, pretend there are a million records in the CustTable table that meets the specified criteria. In this scenario, the system would have to generate and execute 1 million distinct SQL statements. Sending data back and forth between the system and the database with every run. This would be horribly slow and take a long time to complete.
Single Call Using Update_Recordset
Now, let us look at how to use update_recordset in D365 to update the same data, but a whole lot faster.
Create a new runnable job (class), and name it ‘tutorial_update_recordset‘.
Next, populate the class with the following code.
class tutorial_update_recordset
{
public static void main(Args _args)
{
CustTable custTable;
custTable.skipDataMethods(true);
update_recordset custTable
setting InventSiteId = 'WEST'
where custTable.InventSiteId == 'EAST';
info("All customers from EAST site changed to WEST using update_recordset.");
}
}
Explaining Update_Recordset
To understand how to write an update_recordset in D365, let us review each part.
- First, use the syntax “update_recordset <table buffer variable name>” to specify which table to update.
- Second, tell the system which fields in that table buffer to update. Write “setting <field one name> = ‘<some value or variable>’, <field two name> = ‘<some value or variable>’, …“.
- Thirdly, add a ‘where‘ condition to filter which records are updated. Most likely, you will not want to update every record in the table. Additionally, table joins can also serve as filters. For a reminder, read my article on How To Write An X++ Select Statement.
Finally, notice that the update_recordset in D365 is part of a single line of code. The system converts this x++ code into a single SQL statement and runs this command on the database.
While the system still needs to update all records that meet the criteria, it can do so far more efficiently and quickly when it understands the whole job it needs to accomplish. In the other approach, the database receives many separate commands.
D365 update_recordset example
Additionally, if you want to see a real example of an update_recordset in D365, you can search the source code. Specifically, look at the class BatchJobAlertsGenerator. Then, look at the method ‘addAlert‘.
In this method, sets the AlertsProcessed field to the enum value ‘BatchAlertsProcess::Processed‘ using the D365 update_recordset method.
For another example, see the method named addInstalledLanguages on the LanguageTable table.
Join Statements
Lastly, there is one more common trick when writing an update_recordset in D365 that is important to know. When writing the update_recordset statement, you can optionally join additional tables to further control which records are updated. Doing so, just like a select statement with D365 joins, acts as a filter for which records are updated.
This is helpful when you are updating fields on one table, but the criteria for which records should be updated are on a related table.
See the method ‘updateVirtualCompanyRecords‘ on the table NumberSequenceReference, for an example.
Microsoft Documentation
For further information and examples on how to use update_recordset in D365 see Microsoft’s documentation.
Additionally, if you are interested, view the documentation on the TSQL ‘update‘ statement that the system converts the update_recordset into.
Code In The Update Method
Importantly, if there is code in the ‘update‘ method of the table, update_recordset will revert to updating each row rather than performing a set-based update in a single database call. Therefore, it is important that you check the table being updated for code in the ‘update‘ method. If the code in the ‘update’ method does not need to be run for your specific update, then call <table buffer>.skipDataMethods(true);. This tells the system NOT to run the code in the ‘update’ method, which allows it to perform a set-based update in a single database call.
Additionally, you may need to use the override methods skipDeleteActions, skipDatabaseLog, or skipEvents to ensure the record set operation is not converted to a lower record-by-record operation. See this this chart from Microsoft for more details on when these overrides may be needed.
Conclusion
Using an update_recordset in D365 can significantly reduce the time it takes the system to update a table. It is especially effective compared to looping through many records. However, there are still some scenarios where complex logic is needed, especially when determining which value to set each field to, which may require a ‘while loop‘.
Leave a Reply