How BCP Utility can help performance

Question

How BCP Utility can help performance

Data Sheet

SSC-Addicted

Points: 445
More actions
September 21, 2007 at 7:39 am

#181002

Does importing data using BCP utility does not LOG operation in transaction log?
I just needed to know what are the benefits of using BCP utility for performance point of view.

Thanks,
Data sheet

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

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

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 1

Yes bcp logs transactions (as long as the recovery model is full), but in my tests bcp was about 3 times faster then DTS/SSIS.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

...And, BULK INSERT, even with the use of a format file, even beats BCP. That's because BULK INSERT is a wee bit less functional than BCP... for example, BULK INSERT won't throw rejected rows into a file for you.

But, either one, BCP or BULK INSERT, just absolutely fly compared to most anything else.

--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)

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

I'll chime in that BCP is optimized to move data extremely quickly. It's limited, but definitely the fastest way to insert/extract data.

If you set the bulk recovery model (instead of full), then it's still logged, but just the pages inserted are logged, not the individual rows. This gives you some speed enhancements.

You can change the mode, run the bcp, then change it back. be sure you do this around a full backup.

noeld SSC Guru Points: 96590 More actions · Answer 4

The Reason BULK INSERT is faster than bcp is because it runs IN-PROCESS that is why M$ uses "INProcess Loader" term to refer to it and "Out-of-process Loader" to refer to BCP.

* Noel