The use of RecordInsertList has been well documented for increasing the system performance of inserts records.But I was curious how the use of RecordInsertList would scale and what impact it would have on performance.
To understand the impact on performance and scale-ability I created a table with one field. I then wrote a job that would insert X number of records into that table using first the standard insert method, then use the RecordInsertList method. Each pass would record the number of milliseconds to complete. I did this for 100 records then 1000, 5000, 10000, 20000, 40000, 100000, 1000000 and 10000000.
The results to this test are below:
|Records||Insert Method (time ms)||RecordInsertList (time ms)|
If we calculate the number of records per second (( Milliseconds / Records) * 1000) we can see the relative performance of the two methods for the different volume of transactions.
|Records||Insert (records/sec)||RecordInsertList (records/sec)|
As we can see above the insert and recordinsertlist are both very consistant in there relative performance with no drop off as the number of transactions increase, for the insert we get about 1,500 transactions per second, while the recordinsertlist get around 11,000 per second.
This confirms the recordinsertlist is the faster option to insert volume of transactions, but what I had not expected was the scale-ability of this method. I had expected as the transaction count increased there would have been a performance degradation in handling the buffer, but we can clearly see that even up to 10 million inserts the transaction rate is consistent.
This test was using a table with only one field. Trying the same tests with a wider table (added ten 60 wide fields), while the throughput was slower for both the inserts and RecordInsertList at 1,350 records per second and 5,800 records per second, the throughput was consistent.
This does also show that the wider the table the slower the performance for inserts and the smaller the gap between the different methods. This just enforces the need to keep your tables narrow and your database design reasonable well normalized.