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.