SQLite has many PRAGMA statements that can help increase performance, but almost all of them require using pramas:
disable synchronous commits: PRAGMA synchronous = OFF
journal in-memory instead of on-disk:PRAGMA journal_mode = MEMORY
use mmap: pragma mmap_size = <bytes>;
increase page size: pragma page_size = <bytes>;
WAL (already available in dataset): pragma journal_mode = WAL;
There are many other pragmas that allow further tuning of SQLite.
However, there is no easy way right now to pass in these PRAGMAs when SQLite is opened. With this PR, we re-use the functionality of passing in pragmas on database creation (which we currently use for WAL), but extend this to support all PRAGMAs.
I like this a lot, since it's just a generalisation of the existing WAL flag. Would you maybe just add it to the docstring and even make it accessible from dataset.connect()?
SQLite has many PRAGMA statements that can help increase performance, but almost all of them require using pramas:
PRAGMA synchronous = OFF
PRAGMA journal_mode = MEMORY
pragma mmap_size = <bytes>;
pragma page_size = <bytes>;
pragma journal_mode = WAL;
There are many other pragmas that allow further tuning of SQLite.
However, there is no easy way right now to pass in these PRAGMAs when SQLite is opened. With this PR, we re-use the functionality of passing in pragmas on database creation (which we currently use for WAL), but extend this to support all PRAGMAs.
sources: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://blog.devart.com/increasing-sqlite-performance.html