Pi and More

01.05.10

Speeding up SQLITE in AGTL

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')
Kommentar schreiben
  • Bugs in AGTL bitte hier melden.
    Please report any bugs in AGTL here.
  • Wer eine Antwort erwartet, sollte eine echte E-Mail-Adresse angeben.
    If you expect a reply from me, please provide a working e-mail address.
  • Ich kann keine individuelle Problemlösung zu Websicherheit oder regulären Ausdrücken geben!
Name:
Website: http:// optional
E-Mail: wird nicht veröffentlich
Spam-Check: 30 pl us 4 ist gleich