How can I speed up SQL Server applications running over slow links?

A. First we need to define what a "slow" link is. Typically this is anything from 64Kbit/sec and down. On links of this speed the size of a resultset and the number of network packets that are exchanged can make a significant difference to overall response times.

First, either do a network trace, or use SQL Trace/Profiler to see what exactly is being transferred during a typical client session. Then try the following :-

1. If large intermediate resultsets are being returned, then see if you can write the logic into a stored-procedure so that only the end results are returned. Try and reduce the number of sent/received pieces of SQL by using stored-procedures as much as possible.

2. If the connection uses ODBC and the overhead it creates running sp_serverinfo, sp_cursor, temporary stored-procedures etc. is causing the problem then use passthrough queries if possible and turn off the temporary stored-proc creation in the ODBC dsn properties.

3. Configure the db-lib/ODBC connection to use the tcp-ip sockets net-lib. This performs best over slow network connections and can make a significant difference.

4. Is the application using client-side cursors? Try v3 or above of ODBC which should give you transparent server-side cursors.

5. Don't return 1000 rows to the client if all they need to see on the screen is the first 20.

6. If there are large amounts of static data that need to be retrieved then consider replication to a client copy of Access, SQL 6.5 Workstation or with SQL 7.0 a local copy of SQL Server. Over slow links this should only really be used for mainly static data.

7. Don't send any SQL across the link at all. Use Citrix or NT Terminal Edition to run the application centrally and install ICA/RDP clients on the remote machines. The applications then all run locally on a server next to the SQL Server (the same box isn't recommended). The only thing that goes across the slow-link are screen-updates, which are optimised and compressed and so will often work satisfactorily on a 14.4Kbit/sec modem link. This also has the advantage that there is no longer any client code to maintain at the remote sites either. There are whitepapers on Citrix, Microsoft and Compaq's sites about sizing the server(s) you will need to run in this mode.