Question:
I have developed an application that processes about 40,000 records every time it runs. I am using ADO 2.1 and each record is returning about 100 columns of data, and then its being updated. If the SQL Server is located on the same machine as the program it runs in about 1 hour. If the SQL Server is located in another computer it runs in about 4 hours. Both SQL Servers are comparable in speed and memory, both have 100mps Nics, both on the same network.
My question is, is this normal behavior for a SQL Server or do I have some kind of networking issues or SQL Setup problems. I suspect it is just running slower because of the amount of data being transferred over the wire.
Answer:
It would seem that the network bandwidth would be the biggest limiting factor here. The only way to know what’s normal is to measure it and compare measures over time. Perfmon is the first place to start, then to the network admin to find out if they can shed any light on what impacts performance.
You can also do the math to figure out the size of the update vs the width of the pipe, and therefore figure out how long the update should take.
