The first question I'll ask on this is how much network bandwidth exists between the two servers? If for whatever reason, these servers are on 10/100 Mb network cards, that alone might be enough to cause you a ton of grief. You need to have gigabit network cards or fiber network cards to reduce the network latency. If you have any 10/100 switches or routers in between, then that hardware would become a bottleneck, and it would be far better to have gigabit network switches or routers. Just 5 million rows seems like a rather small number of rows to have such a long transfer time, so unless you have a bunch of varchar(max) or nvarchar(max) columns, there's something else going on. So question number two is: how much data does the table contain? You can right-click on the table from SSMS and choose Properties, then click on Storage. You'll see the number of rows and the amount of data space. Question three: What are the server's specs in terms of RAM and CPU ? Post back and chances are the true problem gets revealed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)