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));
 }

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...