BCP Hint ! Rows_Per_Batch ! How do you use ?
-
Hi All,
I have got my BCP command like this:
But the rate at which it transfers data is quite slow. I mean around 1 hour for only 5 million records.
I want to specify the Rows_Per_Batch option, to see if that would boost thing, I am not quite sure of where it would be in my code.
SET @Dynamic_SQL = 'bcp ' + My_Database + '..'+@TabletobeOutput+' IN "' + @MyDumpDirectory + @File + '" -q -t, -r\n -T -c '
Can someone recomend where this should be ?
-
I'm about 95% certain that the order of the switches (params) doesn't matter, but just in case, the order specified in BOL would place the -b switch after the filename and before the -q switch.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
5 million records should take 5 minutes or less for BCP and even faster for BULK INSERT (it does less data checking). Changing the batch size to 20000 will help get you there but I'm thinking that something else is wrong...
- Does the target table have triggers?
- How many indexes are there?
- Does it have a clustered primary key (very, very bad for bulk inserts unless the incoming data is presorted... this could very well be your main problem)?
- Have you followed the rules in BCP in Books Online to use non-logged inserts?
- If you are specifying both the field terminator and the row terminator, there is likely no need for the -c parameter.
- Is the target table being used by others while you insert?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
"5 million records should take 5 minutes or less for BCP....."....you missed out on the provisio....'depending on your hardware'. Nowhere is the hardware/disksubsystem described...this has some impact on the performance (probably not to the extent of the problem described)
-
It could very well be to that extent...
Another dimension missing is 'row'.. How big is a row? As long as a string, huh?
For all we know it could be 8 bytes or 8k wide. Which then means this file may be 40 Megs or 40 Gigs.
It may also be that the file is located somewhere 'out there' on a network..
/Kenneth
-
Nowhere is the hardware/disksubsystem described If his server has less horsepower than my desktop (P4, 1.8Ghz, @MB Ram, Dual IDE 80Meg disks), then he really does have other problems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply