Use prepared statements with substitution parameters

Use prepared statements with substitution parameters to save on costly compilation time. Prepared statements using substitution parameters significantly improve performance in applications using standard statements.

In Derby, as with most relational database management systems, performing an SQL request has two steps: compiling the request and executing it. When you use prepared statements (java.sql.PreparedStatement) instead of statements (java.sql.Statement) you can help Derby avoid unnecessary compilation, which saves time.

In general, any query that you will use more than once should be a prepared statement.

For more information, see Avoiding compiling SQL statements.

Using prepared statements can result in significant performance improvement, depending on the complexity of the query. More complex queries show greater benefit from being prepared.

Related concepts
Create indexes, and make sure they are being used
Ensure that table statistics are accurate
Increase the size of the data page cache
Tune the size of database pages
Avoid expensive queries
Use the appropriate getXXX and setXXX methods for the type
Tune database booting/class loading
Avoid inserts in autocommit mode if possible
Improve the performance of table functions
Configure Derby to use an in-memory database
Increase the concurrency of sequences
Shut down the system properly
Put Derby first in your classpath