Uploading records with BCP

Question

Post reply

Uploading records with BCP

Alex Kachtanov

SSC Veteran

Points: 200
More actions
August 30, 2004 at 8:29 am

#109430

Hi everyone,

May be that's wrong place to ask this question, but it's about performance - so I'll try it here.

I have a table (about 4 million records) and I'm using BCP to upload records to that table. Basically multiply client's applications are loading records to that table simultaneously (sometimes). Client is written on .NET. Server has 4 processors. The table has many indexes (including PK). SQL Server 2000.

When one client's process is loading records to that table all other client's processes are waiting for the transaction to be committed and on server side I can see that 1 of 4 processors are busy 100% (total 25% of CPU usage for 4 processor machine).
If that client is loading let's say 80000 records - it takes a long time (minutes).

My questions are:
1. How to speed up the loading? I cannot remove indexes from that table (because in the same time another processes may need that data to read). Also I cannot split the file before loading - all records must be loaded in the same transaction ("all or nothing").

2. If I create one temporary table for every loading process and then after loading I run "insert into" statement to move the records from temp table to that big table - how much it will reduce the waiting time for other processes? Is it possible to force "insert into" statement to use multiply processors (parallel processing)?

3. Is there anyway to create temporary table before BCP starts loading data to that table (basically I need to have an access to BCP's session and be able to execute DDL pre-script - I guess it's impossible - but let's try)?

Any idea or suggestions?

Thanks,
Alex

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

Ordered Data Files:

Using ROWS_PER_BATCH:

Controlling the Locking Behavior:

Search for all these topic from BOL.

Question 1: While doing a BCP,foreign key constraint will not be checked by default. If this is the case you cannot ensure you BCPing the data with integrity.

Check BOL for opting -h for BCP utility.

Removing index and rebuilding the index will improve the performance dramatically. You can do that using a procedure. Just call the procedure before BCP,Run the BCP, again call the procedure to re-build the index.

Question 2:

Not a wise idea. Because BCP is the fastest way to upload data because as it uses the SQL Server ODBC driver bulk copy functions.

Apart from that you can use the following hints TABLOCK,ROWS_PER_BATCH.

Thanks,

Ganesh

bbdpres SSCommitted Points: 1876 More actions · Answer 2

4 million rows doesn't sound like a lot, but did you consider partitioning the data by client? Then you could BCP concurrently and each transaction is still isolated.

Clay_G Default port Points: 1454 More actions · Answer 3

You probably wont get much faster overall (I mean the time the last users' data is completed) without dropping indexes or constraints, but you say the data must remain available. If you want to juggle things so the users feel better, thats different. For example, you could load into one table per user, and have a background process that imports new data into the main table. Of course that delays the actual update.