This will be the first in a series of blog posts covering various aspect of application development on Maemo
. I develop the Advanced Geocaching Tool
on Maemo using Python and PyGTK.
Today, I'd like to share what I learned about SQLite database performance. AGTL is based on SQLite. All information about geocaches is saved in the database. This includes notes the user can add to a geocache. Whenever the "details view" of AGTL is closed, this information is written to the disk. This action took about half a second - which isn't exactly slow, but annoyingly enough. So I fired up google and tried to find out why the SQLites writes were so slow.
Whenever SQLite performs a write, it blocks until the data is successfully written to the disk. This makes perfect sense, as you don't want a corrupted database when there's a sudden power loss. On the embedded platforms, on which AGTL runs, such an event is rather unlikely. Furthermore, if the database gets corrupted, we can just delete it and create a new one. That's why I added the following line to the AGTL database initialization: c.execute('PRAGMA synchronous=OFF')
And to speed up things a little bit more (just in case...), I also added: c.execute('PRAGMA temp_store = MEMORY')