Posted by Erland Sommarskog on 04/23/07 21:31

Lee (lee.jenkins.ca@gmail.com) writes:
> I have a project need to move more than 100,000 records from one
> database table to another database table every week. Currently, users
> input date range from web UI, my store procedure will take those date
> ranges to INSERT records to a table in another database, then delete
> the records, but it will take really long time to finish this action
> (up to 1 or 2 hours).

It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
process is not well implemented, or that there are indexes missing. Yes,
you can gain speed by using BCP, but you also add complexity to the
solution that I can't really see should be needed with the volumes you
indicate?

Would it be possible for you to post the definition of the tables, including
indexes and the stored procedure?


--
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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]