SQLite updating ONE record is very (relatively) sl

问题:

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. The query always takes ~ 200 ms. That sounds like very little, but it's huge.

Why does 1 very simple UPDATE query take 200 ms?? All reads are blazing fast.

I've tried:

• BEGIN and COMMIT -- no change, because it's just 1 statement
• PRAGMA journal_mode=PERSIST -- no change, apparently disk io isn't the problem?
• removing the UPDATE statement -- that works wonderfully for time!, but it's not very persistent

To compare to MySQL on the same system: 0.6ms in a very similar database.

I don't need transactional security (ACID?) or whatever you call that. If the computer crashes during this query, I'm fine with losing all changes. MySQL (InnoDB) has an option for this: innodb_flush_log_at_trx_commit. Does SQLite have something like that?

I'm using sqlite-3.7.9, if that matters.

回答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 (?).

回答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();