CODEWITHSUNDEEP
sql-serverbcp
John Humphreys
John Humphreys

Reputation: 39324

Improve speed of BCP - command line options?

I'm importing a huge amount of data using BCP, and I was curious about what options it has to improve the speed of this operation.

I read about parallel loading in a couple of places, and also saw that you can tell it to not bother to sort the data or check constraints (which are all viable options for me since the source is another database with good integrity).

I haven't seen examples of these options being used though (as in, I don't know what command line switch enables parallel loading or contraint checking disabling).

Does anyone know a good resource for learning this, or can someone give me a couple trivial examples? And please don't point me to the BCP parameters help page, I couldn't make heads or tails of it with regard to these specific options.

Any help is greatly appreciated!

Upvotes: 1

Views: 11607

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

You need to read The Data Loading Performance Guide. There is no magical command line switch 'load faster', is a very complicated balance of doing the right thing in the right context. It depends on whether you load a heap or a B-Tree, whether there is already data or the table is empty, whether it has secondary indexes, whether minimally logging is possible in the database recovery model, whether the table is partitioned or not, whether the data is pre-sorted or not and that is just the surface. The linked white paper has all the details.

Upvotes: 5

scottm
scottm

Reputation: 28699

It looks like the parallel loading you are talking about is just running multiple instances of the BCP utility against the same table. You would be responsible for partitioning the data before hand. You use it by specifying the TABLOCK table hint. From MSDN:

Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

So it's really just a special lock for BCP.

To further increase performance, you may read further into the -a flag on the BCP parameters page.

-a allows to to specify a larger packet size (between 4096 and 65535) to increase the amount of data sent at one time to the server per network packet.

I would also suggest using the -e flag with an error if you intend to run multiple BCP processes to help keep track of any errors encountered.

Upvotes: 6

Related Questions