optimizing bcp performance

I mistakenly posted this in the "programming" forum- reposting here as it
makes more sense to be in this forum...
We have a conversion process where we bcp out a table and then run a C++
program against it and then later bcp the data into a new table.
For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
in takes about 87 minutes.
Is there any performance considerations to be aware of that could possibly
make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
there was a "select into/bulkcopy" option. Is there anything similar in SQL
2000?
For the bcp out, is there a way to reduce locking (maybe grab a table lock).
Any input would be appreciated.
Thanks in advanceyes,
Drop indexes on the table
change the database recovery mode to bulk logged ( In SQL Enterprise
Manager)
Do the bcp in (Use the TABLOCK hint)
Recreate the indexes
change back to full recovery
Do a transaction log backup..
You can also increase the load time by dividing the input file into multiple
files and firing off several concurrent BCPs. This is available when you use
the TABLOCK hint.
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#7Ac88OuDHA.2308@.TK2MSFTNGP09.phx.gbl...
> I mistakenly posted this in the "programming" forum- reposting here as it
> makes more sense to be in this forum...
> We have a conversion process where we bcp out a table and then run a C++
> program against it and then later bcp the data into a new table.
> For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
> in takes about 87 minutes.
> Is there any performance considerations to be aware of that could possibly
> make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
> there was a "select into/bulkcopy" option. Is there anything similar in
SQL
> 2000?
> For the bcp out, is there a way to reduce locking (maybe grab a table
lock).
> Any input would be appreciated.
> Thanks in advance
>