Application performance with SQLJ
The static SQL that SQLJ uses can often run faster than the dynamic SQL that JDBC uses.
To process an SQL statement, the database server performs these steps:
- Checks the SQL statement for syntax errors and generates any resulting error messages.
- Ensures that the database objects in the statement exist and have appropriate data types.
- Optimizes the syntax of the SQL statement, determining the optimal access path to read or write the data that the statement requires. For a simple SQL statement, optimization can be simple, but for a complex SQL statement operating over many tables with many indexes, the optimization process can have many options.
- Runs the SQL statement.
With dynamic SQL, the database server repeats these steps for each SQL statement at run time (some servers can cache the results of the first two steps for dynamic SQL statements that are run repeatedly). The processing that is associated with these steps impacts the performance of dynamic SQL.
With static SQL, much of the preliminary work is performed at design time. Each time that the workbench builds an SQLJ file, the SQLJ translator performs the syntax and consistency checks, and creates a Java source file that corresponds to the SQLJ file. You can the use a wizard in the workbench to create and run SQLJ customization scripts. The customization script creates packages that contain information about the static SQL statements to bind the Java classes to the database. Then, when you run the application, the preliminary work has been done, which can lead to better performance.
Parent topic
Developing SQLJ applications
Related concepts
Introduction to SQLJ