Parallel BCP
-
Hi,
I am trying to BCP-out & BCP-in a big table (In order to change the collation of the table). To improve the performance of the script, I was trying parallel BCP -out operations.
ie If the table is having 100 records, I try to perform 10 BCP -out operations parallely, by specifying the start record number & end record number for each BCP-out operation. But what I have observed is that only one BCP-out opeartion is active at a time. The next one starts only after the first one completes. The server where I am running this has got 2 processors.
Could anyone please explain me why this is happening. Can I get arround this problem by any means.
The BCP-In operation is taking much lesser time compared to the BCP-out, After setting the 'select into\bulk copy' property on. I am trying to bring the time taken for BCP-out also less. Any suggestions would be of great help.
Thanks.
-
How are you executing these scripts ? e.g. each in a separate DOS window ? I've done this in the past and on large tables I've noticed that script 1-10 would finish, before script 11-20 even though both were started at the same time (within a few seconds). My guess was that script 11-20 was reading up to the starting point specified in the -f parameter of bcp while script 1-10 was actually pulling data out. Still with this overlap execution was faster than one single bcp (the table I was working with at that time had 9+ million rows of data so I had 9 scripts). Another method you can use to avoid the 'reading until the -f starting point' would be to bcp out through views which you would define key ranges. This leads to some up front processing times to assemble things to be bcp'd out and potentially may cause some tempdb growth, but should execute faster overall. But I would not count on things executing in 1/10th the time since you have 10 scripts !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
HI Rudy,
Thanks for your reply. I think I am trying the same thing what you had tried earlier. Basically I wanted to BCP-out a big table. This was taking quite a lot of time. So I decided to BCP-out fractions of the table parallely with the help of -F & -L flags of BCP utility.
For example, if the total number of records in the table is 100, I will fire 10 BCP commands parallely as background processes using a shell script.
The First BCP command will BCP out records 1 to 10, the second one will BCP -out 11 to 20 & so on.
A roughlogic can be written as
Count=0
When count -lt 5 and $StartRecord -le $TotalRecords
do
BCP out -F $StartRecord -L $EndRecord & (Background process)
Set new $StartRecord and $EndRecord values.
Set new value for the variable Count.
done
When this script runs, what I have observed is that only one BCP is active at a time, though I can see all 5 BCP's in the task manager of my computer. I concluded this point by looking at the file size of output files. Only the output file of first BCP increases in size initially. When it is completed, the second one starts & so on...
I understand that in your case 10 BCP commands were active at a time, though the rest 10 had to wait till these finishes. But in m y case only one is active. Can you think of any reason why this would be happening ?
I have read somewhere that for parallel BCP -IN operation you require partitioned tables. Do you require partition tables for BCP -out as well. Was your table a partitioned one ?
-
Without digging too deep into the why's of this kind of behaviour - for which there may be many reasons, ranging from source table locking behaviour to UMS scheduling for the different bcp processes, I wonder - since it seems to be a one-time event - if it's worth the hassle with splitting and partitioning and whatnot...?
Like, how do you ensure that nothing gets lost in between? Or duplicated? Seems like one file, one go would be 'easiest'..
Just curious, how long time is 'long'? ie how long does it take, and how long would you like it to take?
/Kenneth
-
Hi Kenneth,
For your better understanding of the problem, I will explain you the whole story
Basically all this is needed for changing the collation of a database. So the steps followed by me are as follows.
1. Make a copy of the database
2. Drop the tables.
3. Change the collation of the database
4. Recreate the tables.
5. BCP out from the copy made using "-c raw" option.
6. BCP -in to the new database using -C <new code page> option.
These are the steps.
If we perform bcp-out & BCP -in operations of all tables one by one, it would require 24 hours on a testing PC having 2 processors & 2 GB RAM.
This much down time is not acceptable on the production environment.
Therefore I tried performing BCP-out of all tables in the foreground & BCP-IN in the background. This, as expected, reduced the total down time taken to 12 hours.
But the client was still not happy as their production would be down for 12 hours as they use the system 24 hours a day. They were ready for a 4-6 hours down time.
I checked all the options to make the BCP faster & observed that setting the 'Select into\bulk copy' property would help in making the things more faster.
The time taken by the current script to complete is 9 hours. I Observed the script & found out that its the BCP -out operation which is taking much more time (after setting the 'select into/bulk copy' property) as compared to the BCP -in operation. Also there is one BIG table which is delaying the whole process. Then it striked me that if I can BCP -out multiple parts of the table in parallel (as explained in my previous note), the time taken for the script can be brought down to a decent value. But because of the problem I am facing (Again as explained in the previous note...) this step is not working. I was checking whether anyone knew the reason why not all BCP commands are active at the same time. Any suggestion would be of great help.
Thanks.
-
There is a certain limit as how far you can 'parallellillize' it this way. In the end, you're bound to face a bottleneck somewhere. The most likely place I'd guess would be the disksystem. Remember that if you're placing the the files on the same partition, you'll have each file contending about the drives - this may be alleviated somewhat depending on the controller and how that is configured, but if stressed enough, you might reach max i/o capacity when writing sooner than later.
/Kenneth
-
Hi Kenneth,
Thanks for your reply. I will now check in that direction.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply