Performance Tuning Tips for SQL Server BCP

By : Brad McGehee Jan 18, 2007

Printer friendly

If you need to import or export very large quantities of data, it is often much faster to use bcp that it is to use DTS or SSIS. Of course, bcp lacks many features and options available for DTS and SSIS, and it a little more time-consuming to set up sometimes. But if you don't need to manipulate data, or if you deal with large amounts of data, or if you need a simple way to transfer data using a command line option, then you should investigate using bcp. [7.0, 2000, 2005] Updated 6-12-2006

*****

If appropriate, use the BCP native format instead of the character format to import and export data. It is much faster. Of course, this will limit you to BCP data between SQL Server databases with the same Character Sets and Dictionary Sort order. [6.5, 7.0, 2000, 2005] Updated 6-12-2006

*****

If appropriate, use the BCP fast mode to import data. The fast mode can save you substantial time for your data loads. Because there is no "fast mode" option to turn on, you must take all of the following steps to use the BCP fast mode:

·         The "select into/bulkcopy" database option must be set to "true".

·         The target table should not have any indexes.

·         The target table must not be published for replication.

·         Use the TABLOCK to lock the target table.

[7.0, 2000, 2005] Updated 6-12-2006

*****

If the SQL Server has multiple CPUs, and the data to be BCPed into a table can be divided into two or more sources, then consider having multiple instances of BCP run on separate clients, each of them importing the source data into the target table all at the same time. Assuming you don't run into any bottlenecks, this can substantially speed up data loads, although some preparation time is required to set this up. [7.0, 2000, 2005] Updated 6-12-2006

*****

If appropriate, use the BULK INSERT command instead of BCP to import data, as it is faster. For additional performance, consider locking the table being inserted into using the "table lock on bulk load" option that is available by using the sp_tableoption stored procedure. [7.0, 2000, 2005] Updated 6-12-2006

*****

Ever got mad about the bcp utility and its missing ability to add column headers to an output?

Here's a workaround for this limitation:

use pubs
if object_id('workaround')>0
drop view Workaround
go
create view Workaround as
select
au_id
, au_lname
, au_fname
, convert(char, contract) 'contract'
, 1 as SeqNo from authors
union
select
'au_id'
, 'au_lname'
, 'au_fname'
, 'contract'
, 0 as SeqNo
go
exec master..xp_cmdshell
'bcp "select au_id, au_lname, au_fname, contract from
pubs..Workaround order by SeqNo, au_id" queryout "C:\text.txt" -c -T -Sx'

As you see, you need to create a view with the columns to return, and literals to use as column headers. To ensure the literals will sort first, we introduce a virtual column named SeqNo and assign 0 to that column when selecting the literals. That way they will always be on top of our resultset when we use an ORDER BY SeqNo. However, as you can also see, the literals used here are at the same the biggest disadvantage of this method as you cannot use a SELECT … only some columns, but not all FROM the view. This won't have an effect on the select of the literals and will produce a wrong resultset. So, you might want to consider using DTS for such things, and this approach only for queries that rarely change. [7.0, 2000] Added 5-9-2005

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)