Inserting 1000's of rows per second.

I am currently writing an application that needs to insert rows into sql server once every 10th of a second..

This is causing a massive overhead on the server and without slowing the process down I am struggling to find a decent solution to the problem.

I have tried bulk inserting the rows using openXml but this causes CPU hammering whilst the XML is being parsed and inserted..

Any idea's ?

You might try Sequel's DTS or BCP applications.

:)

BCP or DTS are not what I am looking for :-(

The problem is :-

I need to do some relational work on the data that I am getting so therefore BCP will not help me at all..

A data transform task in DTS would do the job but it means I would have to parse in some kind of file (while I do not have)..

Basically what I am doing is downloading information from the web and storing it in an 'in memory' xml document... once the xml document reaches a certain amount of nodes I import it (via a stored procedure) into the database (using openXML and nText).

Also because of the multi-threaded nature of this application the database can be given multiple bulk inserts at any time..

Its a tough one but Im sure its possible.

Consider writing your application in something else than .NET and consider using a fast database server.

I know C# is a really great language, but when executing managed code the whole thing slows a bit down. Try using C++ to get enough speed on the client side, and try to get a really fast database server.

And also.. consider deleting as many indexes from the database as possible. Every index entry speeds up select statements, but slows down inserts

greetings UNI

Thanks for this but I came to the conclustion that BULK INSERT is the only thing I can do to insert this kind of data into a database...

I can't believe that SQL Server does not allow access to bulk insert via nText...

Gutted.

I now have an app that writes files to the sql server every 1000 records.. ugly but f**king fast and very CPU friendly.

:D