Performance tips and tricks

This section lists tips for improving the performance of your Derby application. For a more in-depth discussion of performance, see Tuning databases and applications.

Be sure to visit the Resources section of the Derby Web site for up-to-date performance tips and tricks.

• 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.
• Create indexes, and make sure they are being used
  Indexes speed up queries dramatically if the table is much larger than the number of rows retrieved.
• Ensure that table statistics are accurate
  Ensure that table statistics are accurate, since missing or out-of-data statistics can result in poor query plan selection.
• Increase the size of the data page cache
  Increase the size of the data page cache and prime all the caches.
• Tune the size of database pages
  Tune the size of database pages. Using large database pages has provided a performance improvement of up to 50%. If you use large database pages, increase the amount of memory available to Derby.
• Avoid expensive queries
  Some expensive queries can, and should, be avoided.
• Use the appropriate getXXX and setXXX methods for the type
  For performance reasons, use the recommended getXXX method when retrieving values, and use the recommended setXXX method when setting values for parameters.
• Tune database booting/class loading
  System startup time can be improved by reducing the number of databases in the system directory.
• Avoid inserts in autocommit mode if possible
  To improve insert performance, avoid inserts in autocommit mode if possible.
• Improve the performance of table functions
  To improve the performance of table functions, force more efficient join orders for queries which use table functions.
• Configure Derby to use an in-memory database
  Tune the Java heap and Derby page cache size when using an in-memory database.
• Increase the concurrency of sequences
  If you use a sequence generator (created with the CREATE SEQUENCE statement), you can increase its concurrency by doing the following.
• Shut down the system properly
  If your application shuts down the database in an orderly fashion, it can reduce the amount of work that the database has to do to start up again after an unexpected exit.
• Put Derby first in your classpath
  The structure of your classpath can affect Derby startup time and the time required to load a particular class.