Monday, October 26, 2015

The following pragma settings will greatly enhance transactional insert performance for SQLite.
This is especially useful in scenario's where you are only copying data from a source to destination in a single process.

PRAGMA journal_mode = OFF;
PRAGMA locking_mode = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA count_changes = OFF; 

Using the above pragma's with transaction based inserts provides between 50-100K inserts per second.

The following pragma's may improve your select query performance.

PRAGMA PAGE_SIZE = 4096; /* Note that when changing this value, you must call vacuum! to rebuild your database using this new page size */
PRAGMA default_cache_size = 4103072; -- 1024768 bytes X 4 = 4MBytes
PRAGMA cache_size = 4103072; -- 1024768 bytes X 4 = 4MBytes

The page_size pragma is interesting as it is designed to match your HDD/SSD drive partition's cluster size.
Typically newer OS's like Windows 7+ use a cluster size of 4096 bytes. SQLite I have found defaults to 1024 bytes which is not optimal.

posted on Monday, October 26, 2015 5:01:28 PM (South Africa Standard Time, UTC+02:00)  #    Trackback
Related posts: