Database insert performance

Question

We are planning to implement a system for logging a high frequency of market ticks into a DB for further analysis. To simply get a little what kind of storage performance we can get on the different DB solutions I created a little application for inserting a basic row of tick information. When running the same code on a couple of different DBs we got some interesting results.

The data being inserted is very simple like follows :

CREATE TABLE [dbo].[price](
    [product_code] [char](15) NULL,
    [market_code] [char](10) NULL,
    [currency] [nchar](6) NULL,
    [timestamp] [datetime] NULL,
    [value] [float] NULL,
    [price_type] [char](4) NULL
) ON [PRIMARY]

Microsoft SQL Server :

Total test time : 32 seconds. 3,099 prices per second.

MySQL Server :

Total test time : 18 seconds. 5,349 prices per second.

MongoDB Server :

Total test time : 3 seconds. 25,555 prices per second.

The purpose of this testing is simply to get a little indication of what kind of "raw performance" can be expected of the systems in the bottom. When actually implementing a solution we would of course do buffering, bulk inserts etc.

We only care about the speed of the inserts, as the querying is done "offline" later.

Does anyone have any suggestions for other databases that could fit? I will be trying with HDF5 and MonetDB later tonight too. Its required to have multi client access.

Thanks for any suggestions!

UPDATED :

Sorry, but i did a major edit of my question before positing, and it seems i left out the server versions and some details of the hardware. All tests were on an 8 core server with 12GB RAM running Windows 2008 x64.

Microsoft SQL Server 2008 Enterprise x64. MySQL 5.1.44 running as InnoDB table. MongoDB 1.2.4 x64

The current test is a simple loop of row inserts into the DBs with real historic data from NASDAQ compiled in a CSV file already imported to memory. The code was in C# NET4 x64.

The MS SQL and MySQL servers were "tuned" to perfect settings, while the MongoDB was just set up with defaults. The SQL tables are set up with no indices, as the purpose of the DB is simple as a staging ground before being transfered into the main analysis system.

Many suggested Bulk inserts, however its a difficult way of doing it as we have several clients pushing single ticks into the DB independently from live streams. To allow for such methods, we would have to expand the layer in front of the DB beyond what we have a chance to test for right now. However I imagine something will have to be done for the final architecture, as the numbers we are getting from everything except the MongoDB is not enough to handle the number of inputs needed.

UPDATE 2: SSD drives are indeed great for just this, and we are using this ourselves. However the final product will be installed at a few different customers which all provide their own iron.. and getting servers from the IT department with SSD is still hard... :(

UPDATE 3:

I tried the BulkCopy approach suggested. Performance for the same loop as the others, but first into a DataTable and then BulkInsert into the SQL Server resulted in the following :

Microsoft SQL Server (Bulk) :

Total test time : 2 seconds. 39401 prices per second.

Answer 1

I can only really comment on sql-server, but there are some things to try:

• command batching (i.e. do multiple INSERT in a single hit to the db)
• bulk insert (via SqlBulkCopy)

either should give significant improvements on single-row inserts (the latter being fastest)