SQLite is very fast on read operations and slow on write operations comparing to databases like MySQL. However SQLite can be tuned by changing few settings. You can find some article about it on the net, here I'll show some results of using two settings changes.
I put this script in a Django project folder which has "Pages" application with "Page" model. This code will add 10 entries to the table. Execution time was measured with "time" command:
time python test.py
To compare vanilla results with some changes I've added (before "data" string) this code:
Which allows to test influence of two settings changes. PRAGMA temp_store = MEMORY - store temporary files in RAM, and PRAGMA synchronous=OFF - do not wait for the I/O operation to finish. Tests were done on a XFS partition on Gentoo Linux with 2.6.21 kernel and pysqlite 2.3.3.
NOTE: Changing PRAGMA settings may result in database corruption.
Asynchronous mode is much faster.
- No changes - 1.915 sec
- PRAGMA synchronous=OFF - 0,783 sec
- PRAGMA temp_store = MEMORY - 1,842 sec
- Both changes - 0,764 sec
Next, for 100 entries I tested some selects:
Setting changes did not have effect on the script execution time (~0,76 sec)
Next I tested updates, for 100 entries:
Difference is quite big. To add more variables I've run that code with SQLite database placed in RAM (tmpfs):
- No changes - 12.567 sec
- Both changes - 2,032 sec
mount -t tmpfs tmpfs folder
Database file was placed in tmpfs mount folder. As a comparison I used also a second EXT3 partition:
As you can see, speed of SQLite write operation depends on the speed of the file system. XFS isn't fast compared to EXT3, which in asynchronous mode is nearly as fast as RAM.
- XFS - 12.567 sec
- EXT3 - 1,215 sec
- EXT3, both changes - 0,620 sec
- RAM - 0,611 sec
One of reddit users, "multani" pointed out that using transactions for several write operations will bring much speed increase without the need of using PRAGMA changes. More on reddit page.
We can use a middleware to execute those two queries (better way probably exists):