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.
Page rendered at Tuesday, 20 March 2018 15:31:16 (South Africa Standard Time, UTC+02:00)
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.