Optimizing SQL Anywhere 16 Performance Over a WAN
Optimizing SQL Anywhere Performance Over a WAN
This document discusses tuning performance for an SQL Anywhere network server 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:
- Measure the performance of the network you plan to run your application on.
- Based on the network performance, tune your application to reduce requests and/or to reduce the amount of data transferred.
- Based on the network performance, tune SQL Anywhere’s server options and connection parameters to maximize performance.
- 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 SQL 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 10 MB/s or more. On a WAN, the latency is typically significantly higher (perhaps 1 ms to 500 ms), and the throughput is typically lower (perhaps 6 KB/s to 2 MB/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.
Another way to get an estimate of both latency and throughput between two machines is to run a SQL Anywhere network server on one machine (ensure the database server is not already heavily loaded) and run the following on the second machine:
dbping -d -c <connection string to network server> -st 10
Note that for high throughput networks, the throughput values reported by dbping may be lower than the actual throughput of the network.
To get reasonable SQL 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).
- Move logic that requires many SQL statements from the application to one or more stored procedures or functions.
- If the same basic SQL statement is used more than once, consider preparing the statement once and executing it multiple times with different parameters.
- Ensure your application is not executing queries or SQL statements more often than necessary. If a particular query is executed more than once, consider changing the application to cache the results the first time the query is executed, and then use the cached values instead of re-executing the query.
- Combine queries that get one property, function, or variable value into one multiple-column query. Suppose you want to execute the following queries: SELECT current user , SELECT @@servername , and SELECT connection_property('BytesSent') . Instead of executing three queries, use a single query:
SELECT current user, @@servername, connection_property( 'BytesSent' )
- Avoid doing joins in the application with multiple queries if it is possible to do the join in the server. If an application executes a query, and then executes a second query using the result from the first query, you may essentially be joining multiple queries together in your application. If it is possible to do the join using one query instead of many, this can significantly improve performance. As a simple example, if your application executed the query SELECT T.x FROM T WHERE <conditions on T> ORDER BY <order> , and then for each value of T.x, did SELECT R.y FROM R WHERE R.z = <value of T.x> , then this can be combined into the single query SELECT T.x, R.y FROM T, R WHERE <conditions on T> AND R.z = T.x ORDER BY <order> .
- Avoid setting options after initializing a connection, avoid DDL (Data Definition Language), and avoid dropping connection variables. These can cause client statement caching to not be able to reuse a cached statement. These can also cause cached plans to not be able to be reused. Using DDL in a production system can result in a number of potential performance issues including overall slow performance during the DDL operation, and side effects such as procedures needing to be reloaded into the server's memory.
- Avoid many short running connections when a single long running connection could be used instead. In cases where this is not possible (such as from a web server), consider using connection pooling. SQL Anywhere has built-in connection pooling that can be configured to improve performance depending on the application (for the SQL Anywhere .NET Data Provider use the POOLING connection parameter, and for other client APIs use the ConnectionPool connection parameter).
- When working with third-party development tools (for example, PowerBuilder, Visual Basic, or Delphi), check whether there are any application-specific settings you can make to increase your performance. For example, in PowerBuilder, changing the BLOCK connection property sometimes improves application performance over a WAN.
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.
- Use bound columns to fetch data instead of using get data. This reduces the number of requests, especially when fetching the first row from a cursor.
- Combine SQL statements into a batch (a sequence of SQL statements separated by semicolons that are all executed as if they were one statement) where possible.
- Disable autocommit and commit explicitly only when necessary to eliminate extra commit requests (a commit or rollback should be performed in most cases before waiting on user input to avoid excessive blocking).
- Use wide fetches (also referred to as increasing the row set size) and wide inserts (also referred to as using arrays of parameter values). These fetch or insert multiple rows in one request, instead of using one request per row. Prefetch also fetches more than one row per request.
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.
- Consider using stored procedures for large database queries. This eliminates the need to send a large statement to the server across the network by allowing you to send a small CALL statement to execute the query.
- If you know that you will only be fetching the first row (or first few rows) of a query, add a FIRST or TOP n clause to the query. If you want to skip the first few rows in a query, use the START AT clause. These clauses prevent rows that you won't use from being transferred, particularly if prefetch is enabled. Adding these clauses can also help the query optimizer know how to execute the query more efficiently.
Tuning SQL Anywhere to improve WAN performance
The following suggestion can tune SQL Anywhere so your application runs better on a WAN, independent of your network’s latency and throughput.
- Consider increasing the liveness timeout if your connections are dropped because of liveness. Increasing this value does not improve performance, but you should increase this value if your connection keeps timing out due to liveness. The option can be set at the client (using the LivenessTimeout connection parameter) or the server (-tl). Using the LivenessTimeout connection parameter, you could change the liveness timeout only for WAN connections.
In most cases the following suggestions reduce the number of requests, which is particularly beneficial if your network has high latency:
- Consider changing the prefetch behavior. When your application fetches a row, SQL Anywhere may prefetch additional rows depending on the cursor type and other factors. Prefetch can reduce requests and significantly improve performance, particularly on high latency networks when a large number of rows are fetched from a cursor. Note that absolute fetches, relative negative or relative 0 fetches, rollbacks, and certain get data operations can cause prefetch rows to be discarded and re-fetched, which decreases performance. Prefetch is best used on forward-only, read-only cursors, when more than just the first row is fetched. If your application fetches many rows from each cursor and only uses fetch next operations, using the PrefetchRows and PrefetchBuffer connection parameters may improve performance, although the default values are usually sufficient. Prefetching many rows when the application only fetches part of the result set could potentially decrease performance. Additionally, an ODBC and JDBC applications can benefit from the PrefetchOnOpen connection parameter if many cursor opens are done. Avoid the use of value sensitive cursor types (including ODBC DYNAMIC and KEYSET and ESQL SENSTIVE and SCROLL types) unless they are absolutely necessary for correctness since these cursors cannot prefetch.
- Consider using the LazyClose connection parameter, particularly when many cursors are opened and closed. This eliminates an extra request when closing a cursor.
The following suggestions may be beneficial if your network has low throughput:
- Consider using communication compression (the server’s -pc option or the client’s Compression connection parameter). Using communication compression reduces the amount of data transferred, and the number of packets transferred, but it does not reduce the number of requests. Communication compression can be particularly beneficial on large fetches, multi-row fetches, or BLOB operations. Note that communication compression uses more CPU and memory on both the client and the server. As a result, it can cause poorer performance in some cases, and in most cases decreases performance on a LAN. By using the client’s Compression connection parameter, you can enable communication compression for WAN connections only.
- Consider using the ReceiveBufferSize and SendBufferSize TCP/IP protocol options on both the client and the server. These options pre-allocate memory in the protocol stack for sending and receiving TCP/IP packets. Preallocation of memory inside the protocol stack can increase client/server performance for network-intensive applications. The default value is machine dependent, and values in the range of approximately 65 536 to 262 144 bytes are reasonable for experimentation.
- If your application only uses the first row from a cursor, disabling prefetch using the DisableMultiRowFetch connection parameter can improve performance. Instead of using DisableMultiRowFetch, you may get better performance by changing the application to use the FIRST clause on queries where you only fetch the first row as suggested in the application tuning section above. Using DisableMultiRowFetch can cause poorer performance in many cases, including on faster networks.
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
dbsrv16 -x TCPIP(SendBufferSize=100000;ReceiveBufferSize=100000) -n server_name ...
Client connection parameters
Note that PrefetchOnOpen is for ODBC and OLE DB only. If you do not see any improvement from the SendBufferSize and ReceiveBufferSize, it is recommended that instead of the CommLinks=TCPIP(…) in the example below, you use HOST=w.x.y.z:port for simplicity. A host name or IPv6 address can be used instead of w.x.y.z, and :port is not necessary if the server is using port 2638 (the default).
"ServerName=server_name; Compression=Yes; CommLinks=TCPIP(Host=w.x.y.z:port; DoBroadcast=NONE; SendBufferSize=100000; ReceiveBufferSize=100000); PrefetchOnOpen=Yes; LazyClose=Yes; ..."
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 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 SQL 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 SQL 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:
- Changing the liveness timeout can cause severed connections to goundetected for long periods of time.
- Enabling communication compression often results in slower performance onLANs where the increased memory and CPU usage outweigh potentialthroughput gains.
- Changing the prefetch settings (PrefetchRows, PrefetchBuffer andDisableMultiRowPrefetch). All prefetch settings have the potential to increaseor decrease performance depending on the application and environment.