0

I have a couple of questions regarding SqlBulkCopy.

  1. I need to insert a few million records to a table from my business component. I am considering the following approaches:

    1. Use SqlBulkCopy to insert data directly into the destination table. Because the table has existing data and index (which I cannot change), so I won't get bulk logging behavior and cannot apply TabLock.
    2. Use SqlBulkCopy to insert data into a heap in temp db in one go (batchsize = 0). Once completed, use a stored procedure to move data from temp table to destination table.
    3. Use SqlBulkCopy to insert data into a heap in temp db but specify a batchsize. Once completed, use a stored procedure to move data from temp table to destination table.
    4. Split data and use multiple SqlBulkCopy to insert into multiple heaps in temp db concurrently. After each chunk of data is uploaded, use a stored procedure to move data from temp table to destination table.

    Which approach has shortest end to end time?

  2. If I use SqlBulkCopy to upload data into a table with index, will I be able to query the table at the same time?

Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431
intangible02
  • 993
  • 1
  • 9
  • 19
  • 1
    *Which approach has shortest end to end time?* Impossible to say without knowing your table schema (for the temporary and non-temporary tables) and various other things. Here's some related reading: http://sqlblog.com/blogs/alberto_ferrari/archive/2009/11/30/sqlbulkcopy-performance-analysis.aspx – ta.speot.is Aug 03 '13 at 05:50
  • 2
    Why are you asking strangers on the internet which is fastest when you could test it yourself and get actual numbers with the exact hardware that you are going to be deploying with to compare? – Scott Chamberlain Aug 03 '13 at 05:52
  • Thanks for the link. Will go through the docs. Regarding the table schema, it is pretty simple. Three int column followed by three float column, the three int columns forms the primary key. The temp table has the exact schema except that no index is added. – intangible02 Aug 03 '13 at 05:58

0 Answers0