/**
* Make sure Derby's index cardinality statistics are up to date. Otherwise, the optimizer may
* choose a bad execution strategy for some queries. This method should be called if the size of
* the tables has changed significantly.
*
* <p>This is a workaround for the problems described in <a
* href="https://issues.apache.org/jira/browse/DERBY-269">DERBY-269</a> and <a
* href="https://issues.apache.org/jira/browse/DERBY-3788">DERBY-3788</a>. When automatic update
* of index cardinality statistics has been implemented in Derby, the workaround may be removed.
*
* <p>Without this workaround, poor performance has been observed in {@code get()} due to bad
* choices made by the optimizer.
*
* <p>Note that this method uses a system procedure introduced in Derby 10.5. If this procedure
* does not exist, this method is a no-op.
*/
private void updateIndexCardinalityStatistics(ConnectionResource conn) throws SQLException {
DatabaseMetaData dmd = conn.getMetaData();
if (procedureExists(dmd, "SYSCS_UTIL", "SYSCS_UPDATE_STATISTICS")) {
try (PreparedStatement ps =
conn.prepareStatement("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(?, ?, NULL)")) {
ps.setString(1, SCHEMA);
for (String table : TABLES) {
ps.setString(2, table);
retry:
for (int i = 0; ; i++) {
try {
ps.execute();
// Successfully executed statement. Break out of
// retry loop.
break retry;
} catch (SQLException sqle) {
handleSQLException(sqle, i);
conn.rollback();
}
}
conn.commit();
}
}
}
}
/**
* If this is a Derby database, force a checkpoint so that the disk space occupied by the
* transaction log is freed as early as possible.
*/
private void checkpointDatabase(ConnectionResource conn) throws SQLException {
DatabaseMetaData dmd = conn.getMetaData();
if (procedureExists(dmd, "SYSCS_UTIL", "SYSCS_CHECKPOINT_DATABASE")) {
try (Statement s = conn.createStatement()) {
s.execute("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
}
conn.commit();
}
}