SQL Server 2008 on a separate machine is very slow

Question

We have an application which gets the data from a MS SQL database residing on a server in the headquarter over an internet connection.

Now the problem is that the data is pretty large which makes the query to take large time to transfer the data from the head quarter to the client.

Does SQL provide anything method to speed up queries if your application is geographically separated from the database (e.g. zipping up the data before it starts to transfer) ?

Answer 1

Performance is not only determined by the bandwidth, the latency is also a very strong factor here. Try to minimize the number of requests. You could combine some queries, to give two (or more) resultsets.

Answer 2

Following quotes from a similar question might provide you with some pointers.

There is never a reason to return a large result to start with. For those particular scenarios that need large amounts of data shipped to the client, there are better solutions than T-SQL queries (log shipping, service broker, replication).

and

You have a couple options that I am aware of:

1. Use a third party tool like SQLNitro.
2. Move to Windows Server 2008 and SQL Server 2008 where they have made various TCP stack improvements, as outlined here: Appendix A: A Closer Look - Examining the Enhancements in Windows Server 2008 and SQL Server 2008 That Enable Faster Performance