whats the fastest way to insert streaming data into a table, DB is MS SQL server 2008

Question

Hi! I've to write a c++ application that receives a huge data stream( many thousands of messages per second) and insert it into a MS sql server2008 db (into one or more tables) as fast as possible. I'm new to windows, and amongst so many ways described in msdn, I'm not able to judge the best way to do this, for example: 1. should I use ODBC (using the sql native driver) or OLE db ? is odbc slow? 2. should I try to do a sqlprepare an insert stmt? should I try to bunch a few inserts together? OR should I try a stored procedure? will this be faster? 3. someone's mentioned to explore if windows exposes a tabular data stream API directly (but i couldn't find one, and it's probably a bit more advanced for me right now.)

I'd like to try something fast to run, fast to develop (and hopefully simple). please help, any ideas most appreciated. many thanks!

Answer 1

In a similar application I've batched up the incoming data and inserted them using BULK INSERT, via the .Net 2 SqlBulkCopy class. It inserts on the order of 100,000 rows a second.

This is with Sql Server 2005; I'm not aware of any improved methods for such work in 2008.

Edit: As you can't use the .Net framework, an alternative would be use bcp_sendrow to insert the data, or write the incoming data to a file and use BULK INSERT commands or bcp.exe to do the heavy lifting.

Answer 2

Look at SqlBulkCopy - this allows fast inserts of multiple rows of data. You could buffer a few thousand rows and insert them periodically.