Inserting 1000's of rows per second.

[DecksTerrorT]

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 ?

[wolfofthenorth]

You might try Sequel's DTS or BCP applications.

[DecksTerrorT]

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.

[Illuvatan]

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

[DecksTerrorT]

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.