June 9, 2005 at 1:56 pm
We used DTS (sql 2000) to import several hundred thousand records into a table. It took around 2 minutes for the process. A developer read (somewhere) that it we added an index to the destination table this would improve the speed. Sounded crazy to me as there was no reason for an index and no natural candidate. Anyway, we added an Identity field to this table and found the same DTS package now completed in about 30 seconds!
And idea why this is the case?
TIA,
Bill
June 9, 2005 at 1:58 pm
Can we see the insert statement??
June 9, 2005 at 3:45 pm
Did you "DBCC DropCleanBuffers" before each test so that you are comparing apples to apples ?
Did the 1st import (without index) require the database to auto-grow ?
Did you drop & recreate the table between tests, so that pages weren't already allocated in the indexed test ?
June 10, 2005 at 3:49 am
if you're confident about the state of the data, I'd test without indexes in the destination table. applying indexes to each insert/delete row adds overhead to the process. then have a process that moves the rows to production indexed tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply