Monday, November 10, 2008

DB Record Insertion Rate: A Trivial Experiment

I was recently involved in preliminary investigation for a new project that requires (at least what I thought was) a very high rate of record insertion into a database. In a nutshell, the ability to insert a minimum of 200 records a second up to a maximum of 2000 records a second is required. Even though the records are small, at 80 bytes each, spread across 4 fields, I wasn't confident the DB I had at my disposal would meet these requirements. So I ran a quick experiment. My experiment involved writing a Groovy script that was used to insert 17 million records in a Microsoft SQL Server 2000 DB, both running on my laptop. My laptop has an Intel Core 2 Duo (1.8Ghz) CPU and 3 GB of ram. Each record consisted of 120 bytes, which was split into 5 fields when inserted into the DB. The script inserted the records in batches of 1000 using SQL similar to the following (with 'ValueX' replaced by content to fill out the required 120 bytes) [See Note 1]: INSERT INTO Messages (Col1, Col2, Col3, Col4, Col5 SELECT 'Value1' , 'Value2', 'Value3', 'Value4', 'Value5' UNION ALL SELECT 'Value1' , 'Value2', 'Value3', 'Value4', 'Value5' UNION ALL SELECT 'Value1' , 'Value2', 'Value3', 'Value4', 'Value5' UNION ALL SELECT 'Value1' , 'Value2', 'Value3', 'Value4', 'Value5' .... Results:
  • Approximately 1400 records per second was inserted.
  • The size of DB did not effect insertion rate; i.e. insertion rate did not reduce as the DB got larger.
  • Maximum memory usage was about 2 GB.
  • The DB required about 6GB of disk space (includes index files).
The insertion rate does not meet my upper bound, but is not too bad given the experiment was executed on my underpowered laptop. Note 1 I could not use the more sensible row value constructor syntax [1] INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b, ...]), (value2a, [value2b, ...]), ... since it is not supported by MS SQL Server 2000. Thank goodness this is supported in SQL Server 2008. References [1] Insert (SQL), Wikipedia