September 1, 2009 at 9:43 am
I have a table that gets a "snapshot" created at month end and these rows get moved into a historical table that has 2 years worth of data in it. The table is over a million records, so the historical table is about 24+ million rows. I have tried a few things, dts, select insert; both seem to perform equally bad, about 2 hours to load the data. I am on Sql Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)). I was thinking about trying BCP and BULK Insert, but that really seems ridiculous to export to a file and then re-import into the database since the data is already in the database. I did try adding a key to the one table and moving the rows 50K at a time and committing on my test db server and performance did not seem to improve. Any thoughts?
September 1, 2009 at 10:08 am
What may help is dropping the indexes that may exist on the historical table prior to inserting the new data and then recreating the indexes after the insert.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply