Chapter 4 Performance and Tuning

Performance tuning for prepared statements in dynamic SQL

In Embedded SQL, 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 between 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 will be 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 is likely to 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 between 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 is to run 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 since CallableStatement is a subclass of PreparedStatement, you can upcast a CallableStatement object to a PreparedStatement object, as in the following example:
```
PreparedStatement ps_uncomp = 
 	Connection.prepareCall(sql_string);
```
The PreparedStatement object ps_uncomp is guaranteed to contain an uncompiled statement, since only Connection.prepareStatement( ) is implemented to return PreparedStatement objects containing precompiled statements.

Prepared statements in applications 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 is likely to 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 very few times

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:"
```
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 5.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 downcast to a SybConnection object to allow the use of SybConnection.prepareStatement( ). The SQL string passed to SybConnection.prepareStatement( ) will be 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.