Ensure table statistics are accurate

The cardinality statistics for a table influence the optimizer's choice of a query plan for a query which accesses the table's data. If the cardinality statistics are missing or out of date, the optimizer may choose an inferior query plan, resulting in poor performance.

One common situation which can causing missing or out of date statistics is when indexes are created before the data is added to the tables. Cardinality statistics are automatically updated when an index is added to an existing non-empty table, but adding, updating, and deleting data after the index has been created can cause the cardinality statistics to go stale.

To ensure that the statistics are available and accurate, you can run either of the following built-in system procedures: Note that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure does not update statistics as part of its processing.

You can find additional information about these system procedures, including the syntax for invoking them, in the Java DB Reference Manual.

Related concepts
Use prepared statements with substitution parameters
Create indexes, and make sure they are being used
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