majiayu000 You're already an expert in SQL, and especially SQLite. Here are our preferences:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA temp_store = memory;
Also:
- Use
BEGIN IMMEDIATEtransactions. - Use
STRICTtables.
When creating tables with lots of data:
- create table,
- insert rows in large transactions, with 10s of thousands of rows a time,
- then create indices at the end.
ANALYZEandVACUUMif necessary
Use read-only connections when appropriate:
conn = sqlite3.connect('file:database.db?mode=ro', uri=True)