Chapter 4 Performance and Tuning

Improving jConnect performance

There are a number of ways to optimize the performance of an application using jConnect:

Use TextPointer.sendData( ) methods to send text and image data to an Adaptive Server database. See "Sending image data".
Create precompiled PreparedStatement objects for dynamic SQL statements that are used repeatedly during a session. See "Performance tuning for prepared statements in dynamic SQL".
Batch updates improve performance by reducing network traffic; specifically, all queries are sent to the server in one group and all responses returned to the client are sent in one group. See "Support for batch updates".
For sessions that are likely to move image data, large row sets, and lengthy text data, use the PACKETSIZE connection property to set the maximum feasible packet size.
For TDS-tunneled HTTP, set the maximum TDS packet size and configure your Web server to support the HTTP1.1 Keep-Alive feature. Also set the SkipDoneProc servlet argument to true.
Use protocol cursors, the default setting of the LANGUAGE_CURSOR connection property. See "LANGUAGE_CURSOR connection property" for more information.
If you use TYPE_SCROLL_INSENSITIVE result sets, only use them when the result set is reasonably small. See "Support for SCROLL_INSENSITIVE result sets in jConnect" for more information.

Additional considerations for improving performance are described below.

BigDecimal rescaling

The JDBC 1.0 specification requires a scale factor with getBigDecimal( ). Then, when a BigDecimal object is returned from the server, it must be rescaled using the original scale factor you used with getBigDecimal( ).

To eliminate the processing time required for rescaling, use the JDBC 2.0 getBigDecimal( ) method, which jConnect implements in the SybResultSet class and does not require a scale value:

public  BigDecimal getBigDecimal(int columnIndex) 
 	throws  SQLException

For example:

SybResultSet rs = 
 	(SybResultSet)stmt.executeQuery("SELECT 
 	numeric_column  from T1");
 while (rs.next())
 {
 	BigDecimal  bd rs.getBigDecimal( 
 		"numeric_column");
       ...
 }

REPEAT_READ connection property

You can improve performance on retrieving a result set from the database if you set the REPEAT_READ connection property to false. However, when REPEAT_READ is "false:"

You must read column values in order, according to column index. This is difficult if you want to access columns by name rather than column number.
You cannot read a column value in a row more than once.

Character-set conversion

Bandwidth reduction when unichar or univarchar dataypes are enabled

When communicating with version 12.5 or later of Adaptive Server in which unichar and univarchar datatype support has been turned on for Unicode data, jConnect sends all character parameter data in the Unicode format. This means that ASCII strings such as the word "dog," which would normally require three bytes to transmit, will require six bytes because Unicode data, at the time of this writing, requires two bytes per character.If your client application is going to send character data that is in a one-byte-per-character character set (such as ASCII or iso_1), consider using the setAsciiStream method to send it. This can result in a reduction of network bandwidth (that is, you will be sending fewer bytes across the wire). However, jConnect and the database server must perform internal conversions to handle this data.

SunloConverter

If you are using multibyte character sets and need to improve driver performance, you can use the SunIoConverter class provided with the jConnect samples. This converter is based on the sun.io classes provided by the Java Software Division of Sun Microsystems, Inc.

The SunIoConverter class is not a pure Java implementation of the character-set converter feature, and therefore is not integrated with the standard jConnect product. However, we have provided this converter class for your reference, and you can use it with the jConnect driver to improve character-set conversion performance.

Based on Sybase testing, the SunIoConverter class improved performance on all VMs on which it was tested. However, the Java Software Division of Sun Microsystems, Inc. reserves the right to remove or change the sun.io classes with future releases of the JDK, and therefore this SunIoConverter class may not be compatible with later JDK releases.

To use the SunIoConverter class, you must install the jConnect sample applications. See the Sybase jConnect for JDBC Installation Guide for complete instructions on installing jConnect and its components, including the sample applications. Once the samples are installed, set the CHARSET_CONVERTER_CLASS connection property to reference the SunIoConverter class in the sample (jConnect 4.x) or sample2 (jConnect 5.x) subdirectory under your jConnect installation directory.