Thursday, May 14, 2015

All about Update_recordset

AX 2012 introduces many feature in regards to the performance enhancement and provides ways to access (insert, delete, update) database with less overhead (less database calls).

If we talk about updating record(s) in database, few points come into our mind. One is looping through all records and update them in database (will create total number of records calls to database, and will degrade performance). How about to make only one call to database and update all records in this one call. 

AX 2012 provides way to use X++ SQL statements to enhance performance. This option is update_recordset which enables you to update multiple rows in a single trip to the server.

SQL server statement
UPDATE CUSTTABLE
SET BLOCKED = 0
WHERE CUSTTABLE.ACCOUNTNUM = '';

X++ SQL Server
static void update_recordSetJob(Args _args)
{
    CustTable custTable; // Table buffer declaration
   
    update_recordSet custTable
    setting Blocked = 0
    where custTable.AccountNum == "";
}

We can also use join statements in update_recordset

update_recordSet custTable
setting Blocked = 0
Join custTrans
where custTable.AccountNum == custTrans.AccountNum &&
      [...some other criteria];