1

I want to improve the performance of a console application which takes in a text file with millions of lines and currently inserts each line one at a time into a SQL table.

Using Parallel.For, I'd like to improve this, since each operation is independent of the next. Since each iteration executes a SQLCommand, I would like to know if this is a safe approach. I'm not sure how this works internally and don't want to cause performance issues on the database. For instance, could the database freeze or crash if the Parallel.For creates too many tasks for the sql server to handle?

matrix4use
  • 611
  • 1
  • 6
  • 20
  • 11
    This is what [SqlBulkCopy](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx) is for. – Crowcoder Oct 02 '17 at 20:40
  • 1
    Should be safe enough to do, but this is a perfect case for SqlBulkCopy as @Crowcoder mentioned. You'll need a sql user defined table type as your parameter, but it works great. – MAW74656 Oct 02 '17 at 20:51
  • I'll concur with SqlBulkCopy. It'll waaaayy out perform row by row insertion. Row by row insertion for millions of rows will cause a lot of network traffic. You just need to batch them so you don't run out of memory... you'll have to experiment with the size of the batch, but 5000 is usually where I start and go up or down as needed. – SledgeHammer Oct 02 '17 at 20:55
  • Any possibility to use bcp? – Steve Ford Oct 02 '17 at 21:28
  • `Parallel.For` is useful in circumstances where you have a lot of CPU use. Inserting rows in a database doesn't use a lot of CPU. – Enigmativity Oct 02 '17 at 22:12
  • OP, please see [this answer](https://stackoverflow.com/questions/46125818/store-multiple-values-from-select-statement-and-then-insert-into-different-table#46126189) for a trick for uploading more than one record at a time. This will probably get you better performance than a multithreaded approach. – John Wu Oct 02 '17 at 23:13
  • @Crowcoder your suggestion worked well for me. Thanks! Would you mind answering the question with that ... or how can I give you credit ? – matrix4use Oct 05 '17 at 21:21
  • There are already enough questions in SO answered by "use SqlBulkCopy" that it wouldn't provide much value to the community for me to answer. That said, it is fine for you to answer your own question if you wish. – Crowcoder Oct 06 '17 at 09:14

0 Answers0