BCP process

I have a question about BCP in
When i do a BCP in from a file which has 3 mill records, the BCP insert goes
in very fast with notification showing 1000 records at a time but when all
rows are inserted after 3 mill records it takes considerable time for BCP in
to get over.
What is it doing at each stage '
Ps- Table has a primary key so i assume its a slow BCP
SanjaySanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...

quote:

---

> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert

---

goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.

quote:

---

> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>

---

|||You mean its doin a COMMIT right...
But why is it taking that long i thought a commit is just like including a m
arker
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
Also which one is better in speed using -b1000 or doing it in one big transa
ction
Sanjay|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...

quote:

---

> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a

---

marker

quote:

---

> Checkpoint is actuallly the process which writes committed transaction to

---

the disk so that should be the one taking time.

quote:

---

> Also which one is better in speed using -b1000 or doing it in one big

---

transaction

quote:

---

> Sanjay
>

---

|||examnotes (sanjayg@.hotmail.com) writes:

quote:

---

> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '

---

I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp