ODBC 应用与SQL Anywhere Server或SQL Anywhere 独立引擎的互连

核心提示： ·定制PB与SQL Anywhere的程序发布·如何在局域网上设置Sybase SQL AnyWhe·利用VB驱动pcAnyWhere进行自动文件传·ODBC 应用与SQL Anywhere Server或SQL·让JavaME程序真正Run An

·定制PB与SQL Anywhere的程序发布
·如何在局域网上设置Sybase SQL AnyWhe
·利用VB驱动pcAnyWhere进行自动文件传
·ODBC 应用与SQL Anywhere Server或SQL
·让JavaME程序真正Run Anywhere
·Sybase及SQL Anywhere SQL语句小结
·VB驱动pcAnyWhere进行文件传输

SUMMARY:

This document contains information on the various methods to optimize performance over a WAN

Document ID:	44606	Last Revised:	12/10/99
Topic:	App Development	Document Type:	Tip
Product:	SQL Anywhere	Version:	5
Platform:	PC	Operating System:	OS/2, Windows 3.1, Windows 95, Windows NT

Document:

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

　　IPX/SPX protocols have some significant performance limitations in a routed (wide-area) network, which is why Novell has been modifying them with "packet burst" and "SPX II" changes. TCP/IP is the protocol of choice for WAN implementations and is the main focus of this document.

　　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.

　　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. Note that setting this value too large can cause multiple IP packets to be created.

　　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:

　　IPX/SPX protocols have some significant performance limitations in a routed (wide-area) network, which is why Novell has been modifying them with "packet burst" and "SPX II" changes. TCP/IP is the protocol of choice for WAN implementations.

　　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.