D365FO – AX – Optimizing Record Inserts with RecordInsertList

Array inserts, sometimes referred to as bulk inserts, are implemented in the kernel. They buffer a group of rows and insert them in a single trip to the SQL data store. This vastly reduces the number of trips, and speeds up inserts by approximately a factor of 2 – 10.

You can use RecordSortedList or RecordInsertList to hold your rows until they are inserted.

Both classes have an insertDatabase method that is used to insert the records into the database as efficiently as possible. However, the insertDatabase method does not empty the list itself.

Array inserts can fall back to record-by-record operations in a number of situations.

You can also use insert_recordset to insert multiple rows on a single server trip.

It is faster than an array insert, but limited to simple manipulations that can be implemented as SQL expressions. Array inserts enable you to perform more manipulation on the data before it is inserted.

Example 1

RecordSortedList rsl;
 MyTable          myTable;
 ;
 rsl = new RecordSortedList(myTable.tableid);
 rsl.sortOrder(fieldname2id(myTable.tableId,'RecId'));
 myTable.field1 = 'Value1';
 rsl.ins(myTable);
 myTable.field1 = 'Value2';
 rsl.ins(myTable);
 rsl.insertDatabase();

Example 2

void tutorialRecordSortedList()
 {
     RecordSortedList recordSortedList;
     CustTable        custTable;
     ;
     recordSortedList = new RecordSortedList(tablenum(CustTable));
     recordSortedList.sortOrder(fieldnum(CustTable,AccountNum));
  
     ttsBegin;
         // Prepare record #1 for insertion
         custTable.AccountNum = '1000';     
         custTable.CreditMax = 10000.0;
         recordSortedList.ins(custTable);
  
         // Prepare record #2 for insertion
         custTable.AccountNum = '2000';     
         custTable.CreditMax = 500.0;
         recordSortedList.ins(custTable);
  
         // Prepare record #N for insertion
         custTable.AccountNum = 'N000';     
         custTable.CreditMax = 9999999.9;
         recordSortedList.ins(custTable);
  
         // All records inserted in 1 database operation
         recordSortedList.insertDatabase(); 
     ttsCommit;
 }
 void tutorialRecordInsertList()
 {
     MyTable myTable;
     RecordInsertList insertList = new RecordInsertList(myTable.TableId, true, true, true);
     int recordsCurrentlyInserted, i, recordsToInsert = 125;
     ;
     for (i = 1; i <= recordsToInsert; i++)
     {
         myTable.value = i;
         recordsCurrentlyInserted = insertList.add(myTable);
         info(strfmt("%1 records added, %2 records currently inserted.",
              i,recordsCurrentlyInserted));
     }
     recordsCurrentlyInserted = insertList.insertDatabase();
     info(strfmt("%1 records added, %2 records currently inserted.",
         recordsToInsert,recordsCurrentlyInserted));
 }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s