vendredi 26 février 2016

sqlite: update time slows down after idle period

I'm using a preparedStaement to do do a batch update in a sqlite DB.

At startup the time for 1000 rows is about 250ms for execution which is a good time. Further executions yields the same time. Problem is, that after a pause longer than 3-4 minutes, the execution time goes up to about 1200ms without ever going back to a lower value. I tried endless times, also with intermediated other SQL queries but always the same result.

1200ms is too long I think or is it a typical time? But then, why can the updates be done in 250ms at all?

This is the code how the sql is executed::

IHDSReader r = data.getReader();

// update catalogItem set FileName = ?, PathGUID = ? where 
// catalogItem.GUID = ?
String sqlQuery = (String) r.getFirstValueOrNull("//sql");

List<IHDSReader> rows = r.getNodes("//statement");
PreparedStatement statement = null;

Connection connection = null;
try {
    Class.forName("org.sqlite.JDBC");
    String url = "jdbc:sqlite:G:/Development/SQL/myDB.cat.db";
    connection = DriverManager.getConnection(url);

    connection.setAutoCommit(false);
    statement = connection.prepareStatement(sqlQuery);
    for (IHDSReader row : rows) {
        statement.setObject(1, row.getFirstValueOrNull("param[1]"));
        statement.setObject(2, row.getFirstValueOrNull("param[2]"));
        statement.setObject(3, row.getFirstValueOrNull("param[3]"));
        statement.addBatch();
    }
    long start = System.currentTimeMillis();
    statement.executeBatch();
    connection.commit();
    System.out.println("MoveFilesDataAccessor PSBatch: time for DB: "
            + (System.currentTimeMillis() - start));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        try {
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}   

Does somebody has an idea why this behaviour is happening?
Are there actions to be taken after an update like VACUUM or rebuilding an index?

The primary key of the table is a guid stored as String.

The table has an index 'CREATE UNIQUE INDEX idx_ak_catalogItem0 on catalogItem(PathGUID,FileName)'

JDBC driver is xerial sqlite-jdbc-3.8.11.2.jar on Windows without changing any default settings.

Thanks a lot, Stefan

Aucun commentaire:

Enregistrer un commentaire