Optimizing SQL Anywhere performance over a WAN

Document:

　　You can do the following things to optimize performance over a WAN.

　　Set your timer values to an appropriately high level. Remember that the client and the server exchange packets to test for liveness and to determine if the query is complete yet. The switches to consider are:

-tr for active request termination timeouts. The server will only wait so long for a request to be retransmitted from the client before it will timeout the request and the connection. Increase this value if this problem is occurring in your setup. This switch is no longer necessary in Adaptive Server Anywhere 6.0

-ts for default client retry timeouts. This tells the client how long to wait for a response from the server before it resends the request. Increasing this value will cause fewer resends from the client which should reduce the amount of information passing between the client and server. If you increase this, you should also consider increasing the server request timeout period (see the -tr switch for the server). This switch is no longer necessary in Adaptive Server Anywhere 6.0

-tl for liveness checking. Increasing this value will not improve performance but you should increase this value if your connection keeps timing out due to liveness. The switch can be set at the client or the server. Any setting on the client will override the server setting for that particular client.

　　Consider using the -r parameter to disable multi-row fetching if you are only working with the first few rows in your result sets. This switch can be set by the server or by each individual client. It will cause the client to fetch only one row at a time instead of several.

　　Consider using the -b switch to limit the number of packets used in a multirecord fetch if you are retrieving a large number of rows.

　　Consider using the -s switch to increase the amount of buffer space used for network buffers.

　　Consider using the -x tcpip{receivebuffersize=nnnnn} parameter. This option preallocates memory in the protocol stack for receiving TCP/IP packets destined for the SQL Anywhere client or server. Preallocation of memory inside the protocol stack may increase client/server performance for network-intensive applications.

　　Consider using the -x tcpip{sendbuffersize=nnnnnn} parameter. This option preallocates memory in the protocol stack for sending TCP/IP packets. Under supported platforms, sending of packets is done asynchronously and preallocation of a memory block may allow the protocol stack and client/server to operate concurrently for a longer period of time, thus achieving better performance for network-intensive applications.

　　Consider using the host=x.y.z.w, timeout=X and dobroadcast=NO tcpip parameters to increase connection speed.

　　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 execut the query.

　　Consider combining several column values into one value (eg. Using the string() function) in the select statement if your result set has a large number of columns.

　　When working with 3rd party development tools (eg. PowerBuilder, Visual Basic, Delphi), check to see if there are any application specific settings you can make to increase your performance. Eg. In PowerBuilder, setting the BLOCK connection property to 1 and setting the staticbind connection property to 1 has led to an increase in application performance over a WAN for many customers.

　　Here are some suggested command line switch settings from which you can start tuning. Start with these and adjust them in your test environment to see how they affect performance. Add/remove switches mentioned above and see how they affect performance. It is not an exact science and will require some trial and error to get the best performance in your particular network environment with your particular application. If your application is also going to be running in a LAN environment, don't forget to test your settings there as well, as there may be a need to adjust the communications options for that environment.

 SQL Anywhere Server Command Line switches
     dbsrv50 -x TCPIP -p 1490  -tl 2000  -tr 2000
     
     SQL Anywhere Client Command line switches
     dbclient -b 32  -s 1000  -p 1490  -ts 300,3000  -tl 2000  
     -x TCPIP{host=xxx.xxx.xxx.xxx;Timeout=20;DOBROADCAST=NO;receivebuffersize=100000;sendbuffersize=100000}

　　NOTE: The options listed here are communication oriented. There are other options to consider that will make the server more efficient in all environments (eg. Cache size and database page size). See the documentation for details.

　　You can do the following things to optimize performance over a WAN:

　　Set your timer values to an appropriately high level. Remember that the client and the server exchange packets to test for liveliness and to determine if the query is complete yet. (These are the packets your customer is seeing). The switches to consider are -tl and -tr.

　　Set the packet size on the client and server (-p) to the largest value that makes sense for the WAN/protocol combination (only your network administrator knows for sure) The objective is to allow fetches to return as many rows as practical in a packet. We will only use as much of a packet as makes sense.

　　Consider using the -x tcpip{receivebuffersize=nnnnn} parameter

　　Consider using the -x tcpip{sendbuffersize=nnnnnn} parameterAll in all this is a trial-and-error exercise to determine the correct combination for the application/network environment.

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18