BCP taking very long time.

I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"

With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.

The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?

The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)

According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.

In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.

|||

Tenzig,

Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?

|||

Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.