SQLite Updating ONE Record Is Very (relatively) Slow

I know about SQLite's 'problem' when inserting/updating many rows, but that's not the case here. I'm updating ONE field in ONE row, indexed by PK, in a table with ~ 250 records. Th

Solution 1:

Yes, SQLite has an option like MySQL's innodb_flush_log_at_trx_commit:

PRAGMA synchronous=OFF

and it works like a charm. No ACID, yes speed. For some incredible reason the UPDATE now takes < 1ms.

There is also improving the journal_mode:

PRAGMA journal_mode=MEMORY
or
PRAGMA journal_mode=OFF

Both are very fast and not ACID. Rollback isn't an issue, so both are good in this case. OFF is the fastest, because it doesn't create a journal at all (?).

Solution 2:

SQLite is good option handle lightweight dataset. Yes, it is much more slower than any database while inserting/updating data. One can speed up these operations by committing queries by oneself. Please go through demo code below. I have referred JAVA code with JDBCTemplate Spring framework to perform my database operations. Please handle the required exceptions using try-catch bolcks

conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement(query_string);
for(Object[] temp:argsListForInsertQuery)
{
    stmt.setString(1, (String)temp[0]);
    stmt.setString(2, (String)temp[1]);
    stmt.setString(3, (String)temp[2]);
    stmt.setString(4, (String)temp[3]);
    stmt.addBatch();
 }
 stmt.executeBatch();
 conn.commit();
 conn.setAutoCommit(true);
 conn.close();

Solution 3:

In my case I had a couple of foreign key constraints referencing that primary key from other (pretty large) tables. So, for each update, SQLite had to seq scan all of them to find referencing rows and to make sure those constraints are satisfied.

I suggest you create an index for each foreign key constraint on it's source column(s). That way on each update SQLite will be able to use these indices to check foreign key constrains much faster.

In my case updating my PK was redundant (byproduct of the algorithm), so I did away with it and updates became many times faster. If you have a statement like one below, you are (probably) just wasting resources for useless checks:

UPDATE table SET id=69, ... WHERE id=69;