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

1

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

Is there a situation where we should not use any of them or even use only one of them? After all, they all play 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();
    
asked by anonymous 05.12.2018 / 20:11

2 answers

0

Well, I did some testing and I came to a conclusion.

Here is the breakdown:

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

Faster

The fastest way to insert into the database is implementing with Insert_RecordSet .

Legend:

  • InsertRegular->buffer.insert()
  • InsertList->RecordInsertList
  • InsertUOW->UnitOfWork
  • Insert_recordSet->Insert_RecordSet

Codeusedintesting

classPerformanceTestes_COL{MuBufferbuffer;introws;inttimeStart;inttimeEnd;inti;strtimeConsumed;}privatevoidprocess(){rows=10000;this.InsertTest();this.RecordInsertListTest();this.UOWTest();this.insertRecordSetTest();}privatevoidInsertTest(){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)); 
}
  

Better

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

For example: If a customization requires data handling with some IF's and business logic, it will no longer be possible to use Insert_RecordSet since even the latter is 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 we will be working on and code to implement and process the data according to Microsoft's best practices. If this requires an appropriate mapping for different buffers it is convenient to implement with UnitOfWork . If only one business logic is required during iteration of a query , we should use RecordInsertList (2 ° faster) and not deal with a simple buffer.insert() , this will bring a great gain in data processing.

My recommendation is that you do not use buffer.insert() unless the customization is too simple or you run out of all other insert options, believe me, your server will thank you!

Useful links: Insert - RecordInsertList - UnitOfWork - Insert_RecordSet

I hope I have helped and clarified the subject.

    
20.12.2018 / 18:05
0

Come on, I'll try to explain below:

-insert_recordset: copies data from one or more tables directly to a destination table, so it only takes a trip from the server. This turns out to be faster than inserting values into the array (but the insertion though being "slower" is more malleable in manipulating the data).

-recordInsertList: serves as a buffer, and what would 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, thus having more varieties, for example, allowing the insertion of more than one record in the database at a time, making the communication between the application and the bank is less.

    
10.12.2018 / 18:27