Performance and Tuning

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.05 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.