How to improve the performance of SQLite

SQLite is a lightweight database, do not need any installation can be used, and open source, this as a developer of us is undoubtedly a big gospel.

But in the process of using SQLite, you might have a headache for that incredibly slow performance because the performance of the modification is so low. Performance is low because the functionality of SQLite is not known enough, SQLite has a lot of default settings resulting in performance degradation. Below I will uncover the mystery of improving SQLite performance

1, increase the cache value, the server allows the case, as far as possible to increase the value of the cache;

For example: PRAGMA cache_size = 256 * 1024; (The SQLite default page size is 1kb,cache_size if it is a positive number, it indicates how many pages the cache is set to, and how many kilobytes is set if it is a negative number)

2, manual transactions, SQLite by default for each SQL statement is automatically added transactions, if not manually adjust the transaction, SQLite efficiency is very low;

Start transaction: BEGIN TRANSACTION (BEGIN)

COMMIT TRANSACTION: Commit TRANSACTION (end)

ROLLBACK TRANSACTION: ROLLBACK TRANSACTION (rollback)

3, Set journal log mode, the latest version of the SQLite log mode default is truncate, in addition to modification, other performance is quite considerable, if the changes are more, we recommend using persist log mode. Of course, the condition allows for better use of memory. Before you use other log modes, consider accidentally disconnecting a database connection.

For example: PRAGMA journal_mode = memory; (Set log mode to memory mode)

4, for the frequent use of the operation, you can use a prepare, in SQLite, prepare is a need to consume some resources, and these resources accumulated for the system is also a big cost. In the case of design permitting, it is recommended to use prepare this mode more than once.

Note: The reset Sqlite3_stmt object is required before each bind

5, if the use of a one-time database, you can use the memory database ": Memories:", remember, letters must be all lowercase.

How to improve the performance of SQLite