Chapter 6 Migrating jConnect Applications

Programmer's Reference jConnect for JDBC 6.0

Chapter 5 Performance and Tuning

Chapter 5

Performance and Tuning

This chapter describes how to fine-tune or improve performance when working with jConnect.

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 "Updating image data in the database".
Create precompiled PreparedStatement objects for dynamic SQL statements that are used repeatedly during a session. See "Performance tuning for prepared statements in dynamic SQL".
Use batch updates to 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, use them only when the result set is reasonably small. See "Using TYPE_SCROLL_INSENSITIVE result sets in jConnect" for more information.

Additional considerations for improving performance are described in the following sections.

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.

SunIoConverter character-set conversion

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, Sybase has 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. Therefore, this SunIoConverter class may not be compatible with later JDK releases.

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

If you are using a database with its default character set as iso_1, or if you are using only the first 7 bits of ASCII, you can gain significant performance benefits by using the TruncationConverter. See "jConnect character-set converters".

Performance tuning for prepared statements in dynamic SQL

In Embedded SQL(TM), dynamic statements are SQL statements that need to be compiled at runtime, rather than statically. Typically, dynamic statements contain input parameters, although this is not a requirement. In SQL, the prepare command is used to precompile a dynamic statement and save it so that it can be executed repeatedly without being recompiled during a session.

If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit.

If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database.

Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application. This is discussed under "Choosing prepared statements and stored procedures".

You can use jConnect to optimize the performance of dynamic SQL statements on a Sybase database as follows:

Create PreparedStatement objects that contain precompiled statements in cases where a statement is likely to be executed several times in a session.
Create PreparedStatement objects that contain uncompiled SQL statements in cases where a statement is used very few times in a session.

As described in the following sections, the optimal way to set the DYNAMIC_PREPARE connection property and create PreparedStatement objects can depend on whether your application needs to be portable across JDBC drivers or whether you are writing an application that allows jConnect-specific extensions to JDBC.

jConnect 4.1 and later provide performance tuning features for dynamic SQL statements.

Choosing prepared statements and stored procedures

If you create a PreparedStatement object containing a precompiled dynamic SQL statement, once the statement is compiled in the database, it effectively becomes a stored procedure that is retained in memory and attached to the data structure associated with your session. In deciding whether to maintain stored procedures in the database or to create PreparedStatement objects containing compiled SQL statements in your application, resource demands and database and application maintenance are important considerations:

Once a stored procedure is compiled, it is globally available across all connections. In contrast, a dynamic SQL statement in a PreparedStatement object needs to be compiled and deallocated in every session that uses it.
If your application accesses multiple databases, using stored procedures means that the same stored procedures need to be available on all target databases. This can create a database maintenance problem. If you use PreparedStatement objects for dynamic SQL statements, you avoid this problem.
If your application creates CallableStatement objects for invoking stored procedures, you can encapsulate SQL code and table references in the stored procedures. You can then modify the underlying database or SQL code without have to change the application.

Prepared statements in portable applications

If your application runs on databases from different vendors and you want some PreparedStatement objects to contain precompiled statements and others to contain uncompiled statements, proceed as follows:

When you access a Sybase database, make sure that the DYNAMIC_PREPARE connection property is set to "true."
To return PreparedStatement objects containing precompiled statements, use Connection.prepareStatement in the standard way:
```
PreparedStatement ps_precomp = 
   Connection.prepareStatement(sql_string);
```
To return PreparedStatement objects containing uncompiled statements, use Connection.prepareCall.
Connection.prepareCall returns a CallableStatement object, but because CallableStatement is a subclass of PreparedStatement, you can upcast a CallableStatement object to a PreparedStatement object, as follows:
```
PreparedStatement ps_uncomp = 
   Connection.prepareCall(sql_string);
```
The PreparedStatement object ps_uncomp is guaranteed to contain an uncompiled statement, because only Connection.prepareStatement is implemented to return PreparedStatement objects containing precompiled statements.

Prepared statements with jConnect extensions

If you are not concerned about portability across drivers, you can write code that uses SybConnection.prepareStatement to specify whether a PreparedStatement object contains precompiled or uncompiled statements. In this case, how you code prepared statements can depend on whether most of the dynamic statements in an application are likely to be executed many times or only a few times during a session.

If most dynamic statements are executed infrequently

For an application in which most dynamic SQL statements are likely to be executed only once or twice in a session:

Set the connection property DYNAMIC_PREPARE to "false."
To return PreparedStatement objects containing uncompiled statements, use Connection.prepareStatement in the standard way:
```
PreparedStatement ps_uncomp = 
   Connection.prepareStatement(sql_string);
```
To return PreparedStatement objects containing precompiled statements, use SybConnection.prepareStatement with dynamic set to "true," as shown:
```
PreparedStatement ps_precomp = 
   (SybConnection)conn.prepareStatement(sql_string, true);
```

If most dynamic statements are executed many times in a session

If most of the dynamic statements in an application are likely to be executed many times in the course of a session, proceed as follows:

Set the connection property DYNAMIC_PREPARE to "true."
To return PreparedStatement objects containing precompiled statements, use Connection.prepareStatement in the standard way:
```
PreparedStatement ps_precomp = 
   Connection.prepareStatement(sql_string);
```
To return PreparedStatement objects containing uncompiled statements, you can use either Connection.prepareCall (see the third bullet under Prepared statements in portable applications ) or SybConnection.prepareStatement, with dynamic set to "false":
```
PreparedStatement ps_uncomp = 
   (SybConnection)conn.prepareStatement(sql_string, false);
```
```
PreparedStatement ps_uncomp = 
   Connection.prepareCall(sql_string);
```

Connection.prepareStatement

jConnect implements Connection.prepareStatement so you can set it to return either precompiled SQL statements or uncompiled SQL statements in PreparedStatement objects. If you set Connection.prepareStatement to return precompiled SQL statements in PreparedStatement objects, it sends dynamic SQL statements to the database to be precompiled and saved exactly as they would be under direct execution of the prepare command. If you set Connection.prepareStatement to return uncompiled SQL statements, it returns them in PreparedStatement objects without sending them to the database.

The type of SQL statement that Connection.prepareStatement returns is determined by the connection property DYNAMIC_PREPARE, and applies throughout a session.

For Sybase-specific applications, jConnect 6.0 provides a prepareStatement method under the jConnect SybConnection class. SybConnection.prepareStatement allows you to specify whether an individual dynamic SQL statement is to be precompiled, independent of the session-level setting of the DYNAMIC_PREPARE connection property.

DYNAMIC_PREPARE connection property

DYNAMIC_PREPARE is a Boolean-valued connection property for enabling dynamic SQL prepared statements:

If DYNAMIC_PREPARE is set to "true," every invocation of Connection.prepareStatement during a session attempts to return a precompiled statement in a PreparedStatement object.
In this case, when a PreparedStatement is executed, the statement it contains is already precompiled in the database, with place holders for dynamically assigned values, and the statement needs only to be executed.
If DYNAMIC_PREPARE is set to "false" for a connection, the PreparedStatement object returned by Connection.prepareStatement does not contain a precompiled statement.
In this case, each time a PreparedStatement is executed, the dynamic SQL statement it contains must be sent to the database to be both compiled and executed.

The default value for DYNAMIC_PREPARE is "false."

In the following example, DYNAMIC_PREPARE is set to "true" to enable precompilation of dynamic SQL statements. In the example, props is a Properties object for specifying connection properties.

...
 props.put("DYNAMIC_PREPARE", "true")
 Connection conn = DriverManager.getConnection(url, props);

When DYNAMIC_PREPARE is set to "true," note that:

Not all dynamic statements can be precompiled under the prepare command. The SQL-92 standard places some restrictions on the statements that can be used with the prepare command, and individual database vendors may have their own constraints.
If the database generates an error because it is unable to precompile and save a statement sent to it through Connection.prepareStatement, jConnect traps the error and returns a PreparedStatement object containing an uncompiled dynamic SQL statement. Each time the PreparedStatement object is executed, the statement is re-sent to the database to be compiled and executed.
A precompiled statement resides in memory in the database and persists either to the end of a session or until its PreparedStatement object is explicitly closed. Garbage collection on a PreparedStatement object does not remove the prepared statement from the database.

As a general rule, you should explicitly close every PreparedStatement object after its last use to prevent prepared statements from accumulating in server memory during a session and slowing performance.

SybConnection.prepareStatement

If your application allows jConnect-specific extensions to JDBC, you can use the SybConnection.prepareStatement extension method to return dynamic SQL statements in PreparedStatement objects:

PreparedStatement SybConnection.prepareStatement (String sql_stmt, 
   boolean dynamic) throws SQLException

SybConnection.prepareStatement can return PreparedStatement objects containing either precompiled or uncompiled SQL statements, depending on the setting of the dynamic parameter. If dynamic is "true," SybConnection.prepareStatement returns a PreparedStatement object with a precompiled SQL statement. If dynamic is "false," it returns a PreparedStatement object with an uncompiled SQL statement.

The following example shows the use of SybConnection.prepareStatement to return a PreparedStatement object containing a precompiled statement:

PreparedStatement precomp_stmt = 
   ((SybConnection) conn).prepareStatement( "SELECT * FROM 
   authors WHERE au_fname LIKE ?", true);

In the example, the connection object conn is cast to a SybConnection object to allow the use of SybConnection.prepareStatement. The SQL string passed to SybConnection.prepareStatement is precompiled in the database, even if the connection property DYNAMIC_PREPARE is "false."

If the database generates an error because it is unable to precompile a statement sent to it through SybConnection.prepareStatement, jConnect throws a SQLException, and the call fails to return a PreparedStatement object. This is unlike Connection.prepareStatement, which traps SQL errors and, in the event of an error, returns a PreparedStatement object containing an uncompiled statement.

ESCAPE_PROCESSING_DEFAULT connection property

By default, jConnect parses all SQL statements submitted to the database for valid JDBC function escapes. If your application is not going to use JDBC function escapes in its SQL calls, you can set this connection property to "false" to circumvent this parsing. This may give a slight performance benefit.

Cursor performance

When you use the Statement.setCursorName method or the setFetchSize( ) method in the SybCursorResultSet class, jConnect creates a cursor in the database. Other methods cause jConnect to open, fetch, and update a cursor.

Versions of jConnect earlier than 4.0 can create and manipulate cursors only by sending SQL statements with explicit cursor commands to the database for parsing and compilation.

jConnect version 4.0 and later create and manipulate cursors either by sending SQL statements to the database or by encoding cursor commands as tokens within the TDS communication protocol. Cursors of the first type are "language cursors;" cursors of the second type are "protocol cursors."

Protocol cursors provide better performance than language cursors. In addition, not all databases support language cursors. For example, Adaptive Server Anywhere databases do not support language cursors.

In jConnect, the default condition is for all cursors to be protocol cursors. However, the LANGUAGE_CURSOR connection property gives you the option of having cursors created and manipulated through language commands in the database.

LANGUAGE_CURSOR connection property

LANGUAGE_CURSOR is a Boolean-valued connection property in jConnect that allows you to determine whether cursors are created as protocol cursors or language cursors:

If LANGUAGE_CURSOR is set to "false," all cursors created during a session are protocol cursors, which provide better performance. jConnect creates and manipulates the cursors by sending cursor commands as tokens in the TDS protocol.
By default, LANGUAGE_CURSOR is set to "false."
If LANGUAGE_CURSOR is set to "true," all cursors created during a session are language cursors. jConnect creates and manipulates the cursors by sending SQL statements to the database for parsing and compilation.
There is no known advantage to setting LANGUAGE_CURSOR to "true," but the option is provided in case an application displays unexpected behavior when LANGUAGE_CURSOR is "false."


	Expand Search