MSSQL Sequencial RPC over WAN
can anyone tell me how to solve the problem of slow reponse of MSSQL over WAN.
We have a task to allow colleague in other sites to access our MSSQL DB. Between sites, they are connected via a 4M data link.
When execute query "select <fields> from <table>, there is no response time problem in local Lan. However, when the query is executed in other site, it will be decomposed into a number of RPCs (over 100) between the MSSQL and client. Owing to delay of each network request, the response is far from satisfactory.
Wednesday, August 04, 2004
Instead of sending the query over the network create a stored procedure on the server that does the query and sends the results back in polled groups, probably using a different stored procedure.
From your remote application exec the stored procedure.
Or you could use a client that uses progressive fetching over ODBC which achieves much the same thing.
Thursday, August 05, 2004
Try your query with Query Analyzer. If the response time is OK there, then the problem is in the way your app reads the data. This is normally because your app has selected the wrong cursor model.
Keep in mind that almost all database queries result in several RPC or TDS conversations because the default behaviour of the database engine is to provide the data as it reads it, rather than buffering it locally and firing back a single group. This is just more painful on the WAN because of the combination of bandwidth and latency limitations that don't exist in a pure LAN environment.
In a properly designed app, you should see a small performance degradation over a WAN link, but nothing that causes the app to be completely useless.
Things to look for:
- make your queries return as little data as possible.
- use a 'firehose' cursor to read the results in big gulps and get them off the database server as quickly as possible.
- use stored procedures to batch operations that have to be performed together, so you only hit the database server to invoke the query.
- when you have to send the same query multiple times with different data, use bound parameters. This means the statement is sent once, and all subsequent invocations only send the parameter data. If you are using the ODBC API directly (unlikely) use rowset binding to send several rows at once.
Of course, if this is a completely new application, consider making it at least three-tier, with a service layer in COM+ or whatever the flavour du jour is. This service layer sits on the LAN with the database server and does all the querying. The app talks to the service layer through RPC or DCOM, or web services and retrieves only the exact data it needs. This is a much better architecture for Windows apps that have to talk to SQL Server data over a WAN.
Thursday, August 05, 2004
Fog Creek Home