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. 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? 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.
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 (?).
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();
上一篇: Sqlite为智能手机插入性能
下一篇: SQLite更新一个记录是非常（相对）慢