How to improve SQLite write performance

Performance optimization is a crucial thing for any application. Today I provide you with the to-do list of what should be done in order to improve SQLite write performance. Even so it’s just a quick reference it will show you the way.

• Use an in-memory database
• Use BEGIN TRANSACTION and END TRANSACTION
• Use indexes
• Use PRAGMA cache_size
• Use PRAGMA synchronous=OFF
• Compact the database
• Replace the memory allocation library
• Use PRAGMA count_changes=OFF

SQLite Optimization FAQ has more explanation. However, in order to pass ZCE test you must know the following:

Use PRAGMA synchronous=OFF

The Boolean synchronous value controls whether or not the library will wait for disk writes to be fully written to disk before continuing. This setting can be different from the default_synchronous value loaded from the database. In typical use the library may spend a lot of time just waiting on the file system. Setting “PRAGMA synchronous=OFF” can make a major speed difference.

Use PRAGMA count_changes=OFF

When the count_changes setting is ON, the callback function is invoked once for each DELETE, INSERT, or UPDATE operation. The argument is the number of rows that were changed. If you don’t use this feature, there is a small speed increase from turning this off.
This pragma may be removed from future versions of SQLite. Consider using the sqlite_changes() API function instead.

No related posts.