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.)
我要编写一个接收大量数据流(每秒数千条消息)的c ++应用程序,并尽快将其插入MS sql server2008 db(到一个或多个表中)。我是Windows的新手,在msdn中描述的很多方面,我无法判断最好的方法,例如:1。我应该使用ODBC(使用sql本机驱动程序)还是OLE db? odbc慢吗? 2.我应该尝试sqlprepare插入stmt吗?我应该尝试将几个插入物组合在一起吗?或者我应该尝试存储过程?这会更快吗? 3.有人提到要探索Windows是否直接公开表格数据流API(但我找不到一个,现在它对我来说可能有点先进。)
I'd like to try something fast to run, fast to develop (and hopefully simple). please help, any ideas most appreciated. many thanks!
我想尝试快速运行,快速开发(并希望简单)。请帮助,任何想法最感激。非常感谢!
2 个解决方案
#1
Look at SqlBulkCopy - this allows fast inserts of multiple rows of data. You could buffer a few thousand rows and insert them periodically.
查看SqlBulkCopy - 这允许快速插入多行数据。您可以缓冲几千行并定期插入它们。
#2
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.
在类似的应用程序中,我已经批量传入数据并使用BULK INSERT通过.Net 2 SqlBulkCopy类插入它们。它每秒插入100,000行的顺序。
This is with Sql Server 2005; I'm not aware of any improved methods for such work in 2008.
这是用于Sql Server 2005;我不知道在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.
编辑:由于您无法使用.Net框架,另一种方法是使用bcp_sendrow插入数据,或将传入的数据写入文件并使用BULK INSERT命令或bcp.exe来完成繁重的操作。
#1
Look at SqlBulkCopy - this allows fast inserts of multiple rows of data. You could buffer a few thousand rows and insert them periodically.
查看SqlBulkCopy - 这允许快速插入多行数据。您可以缓冲几千行并定期插入它们。
#2
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.
在类似的应用程序中,我已经批量传入数据并使用BULK INSERT通过.Net 2 SqlBulkCopy类插入它们。它每秒插入100,000行的顺序。
This is with Sql Server 2005; I'm not aware of any improved methods for such work in 2008.
这是用于Sql Server 2005;我不知道在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.
编辑:由于您无法使用.Net框架,另一种方法是使用bcp_sendrow插入数据,或将传入的数据写入文件并使用BULK INSERT命令或bcp.exe来完成繁重的操作。