Do not set this to a high value in production. The cache_size specifies how many memory pages SQLite is allowed to hold in the memory.A write to SQLite, may not mean it is flushed to the disk. By turning off synchronous, SQLite does not care about writing to disk reliably and hands off that responsibility to the OS.This disables the atomic commit and rollback capabilities of SQLite. Turning off journal_mode will result in no rollback journal, thus we cannot go back if any of the transactions fail.The internet is filled with many SQLite optimisation posts. The next logical step was to look for database optimisations and I started diving into the amazing world of SQLite. Secondly, I wanted the code to be simple and near to the daily usage version. The script I had written is very simple, so I assumed there isn’t much room for optimisation.
SQLITE 3 INSERT ROW PYTHON FULL
Here is the full code SQLite Optimisations With this simple change, the running time was reduced to 10 minutes. I tried different sizes of batch inserts, found out 100,000 to be a sweet spot. Each transaction guarantees that it is written to disk thus could be slow. In SQLite, each insertion is atomic and is a transaction. This version took close to 15 minutes, sparked my curiosity and made me explore further to reduce the time. In this script, I tried to insert 10M rows, one by one, in a for loop. The standard library provides a nice SQLite module, using which I wrote my first version. Python is my go to language for any kind of scripting. No need of using true random methods, pseudo-random methods from stdlib are just fine.It may use my machine resources to the fullest: 100% CPU, 8GB Memory and gigabytes of SSD space.That is, it is fine if the process crashes and all the data is lost.
0 kommentar(er)
