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 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 provides performance tuning features for dynamic SQL statements.
