Not really. Other than using tcp-ip sockets net-lib there's nothing
much you can do from the config side.
Q. How can I speed up SQL Server applications running over slow
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 to see what exactly
is being transferred during a typical client session. Then try the
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, MS and Compaq's sites about sizing the
server(s) you will need to run in this mode.
Neil Pike MVP/MCSE
Protech Computing Ltd
(Please post ALL replies to the newsgroup only unless indicated