Hi,

I was wondering if there is a 'reasonably priced' product out there that will let me import and export data faster than bcp. I work @ a small company so can't afford expensive software in the 1000s. Thanx for your response.

============== Minh Q. Nguyen

-- Anonymous, May 15, 1998

Answers

Re: Faster BCP?

Minh,

For free you can use fast bcp. It is about 6 times faster than slow bcp.

Fast BCP requires:

1) Database option SelectInto/BulkCopy = True

2) No indexes on table into which data to be BCP'd. And that includes indexes created by Primary Key and Unique constraints.

3) Order of data in input file must be identical to the sort order of any clustered index which is later to be applied to the table.

You might also consider PLATINUM Fast Load and PLATINUM Fast Unload for Microsoft SQL Server. I'm sorry I can't find the cost. It may be out of your budget.

From their (www.platinum.com) web site you get the picture that these things read and write directly to SQL Server devices bypassing normal access routines. While it does produce faster results than bcp, it needs the database to be in single-user mode, which for most of systems is unacceptable. The speed up is reported to be (drum roll please) 6 times. I presume that is compared to slow bcp. Perhaps all that fast load does is makes your table and data ready to run fast bcp, runs fast bcp, and puts back the indexes. I agree that if that is all that it does it is not worth thousands.

Good luck,

Eric

-- Anonymous, May 16, 1998