DTS Object Transfer vs DTS Object Copy

[ppepin]

Here is my problem:

I have 2 Microsoft SQL Server 7.0 Databases on different servers. I have the same table in each database with the same exact schema. The table has no foreign keys defined and the only index is a clustered index on the Primary Key (8 fields). The table contains 5.1 million rows. So far, so good.

I am now trying to use Microsoft DTS to copy the table from one server to the other. I have truncated the data on the destination table prior to performing any copy. I am attempting to do this using the DTS Object copy and the DTS Object transfer mechanisms to determine which one would be easier and more efficient. And the results are staggering! Using the DTS Object copy mechanism, the entire table, all 5.1 million rows, are copied in approximately 20 - 25 minutes. Using the DTS Object Transfer mechanism, I had to cancel the transaction after 8 hours! Then, it took approximately 4 hours to actually cancel the transaction (I am assuming that rollbacks were occurring).

So my question is: What is the difference in my benchmarks between the DTS Object Copy and DTS Object Transfer functions? I don't understand why the big difference in times, so any explanations would be appreciated.

Thanks.

 

[tlbroadbent]

I'm not sure of the options you've selected but I suspect that one process ran quickly because it deleted and recreated the table, inserted the data and then created the primary key.

The 2nd process probably tried to insert the records into the existing table with its primary key in place. This process would generate entries in the transaction log which could greatly increase the time required.

You might try the DTS Object Transfer after dropping the primary key, just to see if this changes the performance. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ppepin]

Well, I tried your suggestion and received the same performance. With the Object Copy from the table now with no primary key, all 5.1 million rows were copied in about 20 minutes. Object Transfer was cancelled after 4 hours and it hadn't completed yet.

What is going on??????