RecordInsertList Performance

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)
100 328 0
1,000 672 110
5,000 3,254 437
10,000 6,547 859
20,000 13,781 1,953
40,000 26,016 3,687
100,000 649,84 8,563
1,000,000 664,453 85,172
10,000,000 6,572,797 853,781

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)
100 304
1,000 1,488 9,090
5,000 1,536 11,441
10,000 1,527 11,641
20,000 1,451 10,240
40,000 1,537 10,848
100,000 1,538 11,678
1,000,000 1,507 11,740
10,000,000 1,521 11,712

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.

Advertisements

One thought on “RecordInsertList Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s