Which of these options is better and faster for an INSERT in the bank?

Question

Regarding performance, which of these is the "best" and fastest ?

Is there any situation where we should not use any of them or even use only one of them ? After all, everyone plays the same role.

• Recordinsertlist
• insert_recordset
• Unitofwork

Recordinsertlist

RecordInsertList insertList = new RecordInsertList(tableNum(TableExample));
// Alguma lógica de négocio...

insertList.add(buffer1);
insertList.add(buffer2);
insertList.add(buffer3);

insertList.insertDataBase();

insert_recordset

insert_recordset buffer (myNum, mySum)
select myNum, sum(myValue) from outroBuffer
    group by myNum 
    where outroBuffer.myNum == 100;

Unitofwork

UnitOfWork uow = new UnitOfWork();

// Alguma lógica de negócio ...
uow.insertOnSaveChanges(buffer1);
uow.insertOnSaveChanges(buffer2);
uow.insertOnSaveChanges(buffer3);
uow.saveChanges();

Answer 1

Well, I ran some tests and came to a conclusion.

Follows the detailing:

• "Which of these options is better and faster ..."

Faster

The fastest way to get into the database is to implement with Insert_RecordSet.

Caption:

• Insert Regular -> buffer.insert()
• Insert List -> RecordInsertList
• Insert UOW -> UnitOfWork
• Insert_recordset -> Insert_RecordSet

Code used in tests

class PerformanceTestes_COL 
{   
    MuBuffer    buffer;
    int         rows;
    int         timeStart;
    int         timeEnd;
    int         i;
    str         timeConsumed; 
}

private void process() {
    rows = 10000;

    this.InsertTest();
    this.RecordInsertListTest();
    this.UOWTest();
    this.insertRecordSetTest(); 
}

private void InsertTest() {
    buffer.clear();

    timeStart = timeNow();

    for (i = 0; i < rows; i++)
    {
        buffer.InventSiteId             = "001";
        buffer.ItemId                   = "161634";
        buffer.RetailVariantId          = "53458";

        buffer.insert();
    }

    timeEnd = timeNow();

    timeConsumed = timeConsumed(timeStart, timeEnd);
    info(strFmt("Insert Regular (%1) rows - time: %2", rows, timeConsumed)); 
}

private void RecordInsertListTest() {
    RecordInsertList    list = new RecordInsertList(tableNum(MixItemCovCopy_TestPerformance_COL));
    ;

    buffer.clear();

    timeStart = timeNow();

    for (i = 0; i < rows; i++)
    {
        buffer.InventSiteId             = "002";
        buffer.ItemId                   = "161634";
        buffer.RetailVariantId          = "53458";

        list.add(buffer);
    }

    list.insertDatabase();

    timeEnd = timeNow();

    timeConsumed = timeConsumed(timeStart, timeEnd);
    info(strFmt("Insert List (%1) rows - time: %2", rows, timeConsumed)); 
}

private void UOWTest() {
    UnitofWork      uow = new UnitofWork();
    ;

    buffer.clear();

    timeStart = timeNow();

    for (i = 0; i < rows; i++)
    {
        buffer.InventSiteId             = "003";
        buffer.ItemId                   = "161634";
        buffer.RetailVariantId          = "53458";

        uow.insertonSaveChanges(buffer);
    }

    uow.saveChanges();

    timeEnd = timeNow();

    timeConsumed = timeConsumed(timeStart, timeEnd);
    info(strFmt("Insert UOW (%1) rows - time: %2", rows, timeConsumed)); 
}

private void insertRecordSetTest() {
    // Buffer 2 contém 10000 registros
    Buffer2      buffer2;
    ;

    buffer.clear();

    timeStart = timeNow();

    insert_recordset buffer (InventSiteId, ItemId, RetailVariantId)
    select InventSiteId, ItemId, RetailVariantId from buffer2;

    timeEnd = timeNow();

    timeConsumed = timeConsumed(timeStart, timeEnd);
    info(strFmt("Insert_recordSet (%1) rows - time: %2", rows, timeConsumed)); 
}

---

Best

The best way to insert in the bank is .. depends on! Because each case can be treated differently where it will not be possible to use one of the possibilities I mentioned in the answer.

For example: If a customization requires a processing in the data with some IF’s and business logic it will no longer be possible to use the Insert_RecordSet because even though this is the faster, this form of insertion is not very malleable to treat the records before or at the time of the Insert.

Knowing this, we should evaluate the customization in which we will work and encode to implement and process the data according to the best practices of Microsoft. If this requires an appropriate mapping for different buffers it is convenient to implement with the UnitOfWork. If only a business logic is needed during the iteration of a query no doubt that we must use RecordInsertList (2° faster) and not deal with a simple buffer.insert(), this will bring a great gain in data processing.

My recommendation is not to use buffer.insert() unless the customization is too simple or if all the other insertion options are exhausted, believe me, your server will thank you!

Useful links: Insert - Recordinsertlist - Unitofwork - Insert_recordset

I hope I’ve helped and clarified the matter.

Answer 2

Come on, down I’ll try to explain:

-insert_recordset: copies the data from one or more tables directly to a destination table, so it only makes one journey from the server. This ends up being faster than a value insertion in the matrix (but the insertion despite being more "time consuming" are more malleable in the manipulation of the data).

-recordInsertList: serves as a buffer, and what would be buffer? It is a region of physical memory that is used to temporarily store some kind of data. The recordInsertList is more for a class that works with the most varied methods, so it has more varieties, for example, allow the insertion of more than one record in the database at a time, making the communication between the application and the database smaller.