Switch to standard view 
  Sybase logo
 
 
 



Optimizing Adaptive Server Anywhere Performance Over a WAN


Download whitepaper here


Introduction

This document discusses tuning performance for an Adaptive Server Anywhere network server (version 8 or higher) that is running over a WAN. TCP/IP is the protocol of choice for WAN implementations and is the main focus of this document. Note that network performance tuning is an iterative process of determining what works best with a particular application and network.

The recommended steps for optimizing the performance of your application are:

  1. Measure the performance of the network you plan to run your application on.

  2. Based on the network performance, tune your application to reduce requests and/or to reduce the amount of data transferred.

  3. Based on the network performance, tune Adaptive Server Anywhere’s server options and connection parameters to maximize performance.

  4. Consider the implications of your tuning on LANs or same-machine operation.

In many cases, tuning an application can have a more significant performance impact than tuning Adaptive Server Anywhere.

Measuring network performance

Latency and throughput can be used together to describe the performance of a network. Latency refers to the time delay between when one machine sends a packet of data and the second machine receives the data (for example, if the second machine receives the data 10 ms later than the first machine sent it, the latency is 10 ms). Throughput refers to the amount of data that can be transferred in a given time (for example, if a one machine sends 1000 KB of data, and it takes 5 seconds for all of it to be received by the second machine, the throughput is 200 KB/s). On a LAN, latency is typically less than 1 ms, and throughput is typically more than 1 MB/s. On a WAN, the latency is typically significantly higher (perhaps 5 ms to 500 ms), and the throughput is typically significantly lower (perhaps 4 KB/s to 200 KB/s).

You can measure network latency between two machines by the round trip time reported by the system’s ping utility. The round trip time is the latency to transfer data from one machine to a second machine plus the latency to transfer data from the second machine back to the first machine. You can measure network throughput by copying a file of a known size of at least 200 KB from one machine to a second machine and timing the copy. This copy could be performed as a regular file copy, using FTP, or by downloading a file using an Internet browser.

To get reasonable Adaptive Server Anywhere performance on a network that has high latency, but reasonable throughput, the number of requests made by the client must be minimized. If a network has reasonable latency, but low throughput, the amount of data transferred between the client and server must be minimized.

Tuning your application to improve WAN performance

Changing your application to implement the following suggestions generally reduces both the number of requests and the amount of data transferred. These suggestions can also help improve application performance in any environment (standalone, LAN, and WAN).

SELECT current user, @@servername, connection_property( ’BytesSent’ )

Reducing the number of requests

The following suggestions may reduce the number of requests, which is particularly beneficial if your network has high latency. These suggestions may also improve performance on other networks.

Reducing the amount of data transferred on the network

The following suggestions generally reduce the amount of data transferred, which is particularly beneficial if your network has low throughput. These suggestions are unlikely to decrease performance on other networks.

Tuning Adaptive Server Anywhere to improve WAN performance

The following suggestions can tune Adaptive Server Anywhere so your application runs better on a WAN, independent of your network’s latency and throughput.

In most cases the following suggestions reduce the number of requests, which is particularly beneficial if your network has high latency:

The following suggestions may be beneficial if your network has low throughput:

Here are some suggested settings you can use to start tuning. Start with these settings and adjust them in your test environment to see how they affect performance. Try adding and removing the server options and connection parameters mentioned above and see how they affect performance. It is not an exact science and requires some trial and error to get the best performance in your particular network environment with your particular application.

For information about potential LAN performance implications, see "Performance implications for LANs and same-machine operation" below.

Database server command-line options

dbsrv9 -x TCPIP(SendBufferSize=100000;ReceiveBufferSize=100000) -n server_name ...

Client connection parameters

Note that PrefetchOnOpen is for ODBC and OLE DB only.

"ServerName=server_name; Compression=Yes; CommLinks=TCPIP(Host=w.x.y.z; DoBroadcast=NONE; SendBufferSize=100000; ReceiveBufferSize=100000); PrefetchRows=50; PrefetchBuffer=1000; PrefetchOnOpen=Yes; LazyClose=Yes; ..."

Note

The options listed here are communication oriented. There are other options to consider that can make the server more efficient in all environments (for example, cache size and database page size). Refer to your SQL Anywhere Studio documentation for more information.

Performance implications for LANs and same-machine operation

If your application is also going to be running in a LAN environment, don’t forget to test your Adaptive Server Anywhere communication options there as well, as there may be a need to adjust these options for that environment.

All of the application tuning suggestions mentioned in this document typically improve or at least maintain performance in LAN and same-machine configurations.

The following Adaptive Server Anywhere WAN tuning suggestions may cause poorer performance on a LAN or for same-machine operation. They should only be enabled for WAN clients, or after LAN testing has verified they also increase performance on the LAN:



Back to Top
© Copyright 2010, Sybase Inc.