Hmm. 2 billion records had a throughput of around 4K rec/sec. That maps to 5 almost 6 days.
>> Is there a reason for other smaller tables to have better throughput
Most likely it is the 'width' of the tables defining the speed. Both the number of columns and and number of bytes matter. With the bytes include the average size for the LOBs (VARCHAR(MAX))
Have you tried a much higher commit rate? 100K, 300K? Just use a tet test with your worst table. For each of 10K, 30K, 100K, 300K Start, give it a minute or 5 to stabilize, watch the records/sec and kill.
Can use use Replicate server (Linux? Windows) tools to see the resource consumption for the load/unload threads? Are they the bottleneck using close to a single CPU perhaps?
Give those slow tables higher priority to get them started early. If you are stuck with multiple days for one major table (RESB?) then you might as well drop DOWN the number of streams to 2 or 3 to reduce the contention for resources as the other tables will surely easily finish while the slowest one is loading.
Can you use parallel load with data ranges for the large table? In that case increase the number of streams accordingly.
Finally be sure to check with the source and target DBA's and possibly network folks to determine the most likely bottleneck. I suspect the source, but who knows. Look for top-SQL resource attributes. Look for actual gigabits/sec on the network vs network connection ratings.
Good luck,
Hein
