Hi all,
I have been doing some programming with VB.NET and SQL-DMO, specifically
using BulkExport. What I noticed is that of the three methods I measured,
OSQL with select *, BulkExport with DMO and CMD Line BCP - BCP still seems
consistently faster than any other method to get data out of a table to a
file - I am wondering why this is.. I expect OSQL is writing a row at a
time to the file, so I can see why it is the slowest. If I had to guess, I
would think that BCP fetches 1000 rows at a time out of the source table
into a buffer before writing to disk. One would think that bulkexport would
do the same, but it seems it is measurably slower. I have yet to try the
ODBC API (and DTS), but I am curious if anyone may know the answer as to the
speed of BCP..

Thanks.

Jack (jack@mail.com) writes:
[quoted text, click to view]

The ODBC API should give you the same speed. BCP is just a command-line
interface to that API...

I think the more interesting question is why BulkExport with SQL-DMO is
so slow, but since I never use DMO, I can't tell. But since it's COM-based
I suspect that is uses OLE DB for connection, and OLE DB only has bulk-in
not bulk-out. So maybe SQL-DMO uses SELECT statements? A Profiler trace
should reveal what is going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Erland,
Thanks for the reply - BulkExport is indeed slower - I have used it several
times over the years and had the same results. I did a profiler trace and
it seems to me that BCP and BulkExport are both doing select statements,
"SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city],
[state], [zip], [contract] from pubs..authors" On large tables, BulkExport
takes at least twice as long as BCP, and OSQL Select output to File takes
twice as long as BulkExport. I will compile and run some tests with the
ODBC API - maybe that will do the trick

Jack