SQL Server Query Transportation Time Based on Latency and Internet Download Speed
-
I had asked this question elsewhere and was advised to ask here instead. I am wondering if anyone knows how internet connection latency and download speed affect query execution time in SQL Server Management Studio. Is that product similar to using a Data Reader instead of a Data Adapter? Is there a flag I can set to make it more like a Data Adapter? I am trying to get better performance on a remote office, especially since Disaster Recovery can be better handled from the headquarters. I ran a test in SQL Server Management against the same database across the country. One internet connection had 4x the download speed according to speedtest.net, but a larger latency in ms. The difference is that one connection was a slow ethernet connection and the other a faster wireless connection. Same locations, multiple tests with similar results. Somewhat surprisingly, the connection with the faster download speed took 50% longer to return a recordset in the 5-20 MB range. Is this because querying in SQL Server Management Studio is similar to executing a query with a Data Reader instead of a DataAdapter? If not, what is the issue?
-
I had asked this question elsewhere and was advised to ask here instead. I am wondering if anyone knows how internet connection latency and download speed affect query execution time in SQL Server Management Studio. Is that product similar to using a Data Reader instead of a Data Adapter? Is there a flag I can set to make it more like a Data Adapter? I am trying to get better performance on a remote office, especially since Disaster Recovery can be better handled from the headquarters. I ran a test in SQL Server Management against the same database across the country. One internet connection had 4x the download speed according to speedtest.net, but a larger latency in ms. The difference is that one connection was a slow ethernet connection and the other a faster wireless connection. Same locations, multiple tests with similar results. Somewhat surprisingly, the connection with the faster download speed took 50% longer to return a recordset in the 5-20 MB range. Is this because querying in SQL Server Management Studio is similar to executing a query with a Data Reader instead of a DataAdapter? If not, what is the issue?
Hi, Don't know if SSMS is more like a data reader than a data adapter and I would guess that it depends on how are you using it. For example if you create a query and run it in a query window you'll get results you cannot modify much like with data reader. On the other hand if you open a table for editing then you can modify the results so the functionality is closer to a data adapter. Concerning the network latency issue, there's one parameter you can define before you connect, that's affecting the speed quite a lot. If you click the options in the connect dialog, you'll find the network packet size which can be modified. If you have a latency issue, I'd guess that you could increase the packet size even to 16'383 bytes which is the maximum (instead of the default 4'096). If you like you can change this default packet size in the instance settings.
The need to optimize rises from a bad design.My articles[^]
-
Hi, Don't know if SSMS is more like a data reader than a data adapter and I would guess that it depends on how are you using it. For example if you create a query and run it in a query window you'll get results you cannot modify much like with data reader. On the other hand if you open a table for editing then you can modify the results so the functionality is closer to a data adapter. Concerning the network latency issue, there's one parameter you can define before you connect, that's affecting the speed quite a lot. If you click the options in the connect dialog, you'll find the network packet size which can be modified. If you have a latency issue, I'd guess that you could increase the packet size even to 16'383 bytes which is the maximum (instead of the default 4'096). If you like you can change this default packet size in the instance settings.
The need to optimize rises from a bad design.My articles[^]
-
Thanks! I got a 2x to 6x speedup after raising the packet size to 32767. Now the query execution and transportation time is faster with the faster internet connection with slightly higher latency.