Using SQLite
Sometimes people tell me that sqlite is too slow to use in applications. I don’t think that’s a valid reason for discarding sqlite. But you have to keep things in mind while working with sqlite. Some of the things I learned while working on Mailody:
Use the ‘begin transaction’ and ‘commit transaction’ calls. When you are writing a lot of data, start with begin transaction, execute all queries and end with commit transaction. This will make a huge difference. From slogish, continuous flicker of the harddisk led, to blazingly fast.
In C++ it seems to be a rule to not letting the method know more then it absolutely needs to know. Well, you need to break with that rule sometimes for sqlite. Say you know 950 of the 1000 different things you need to know from the database. You would be tempted to query only the 50 remaining bits. If that requires 50 seperate select queries, then you might just be better of querying for all 1000 items in one go and get the remaining 50 items from the result of that query. So prefer to make one query which results a bit to much than multiple queries that result in exactly what you need.
Last tip that I want to share, is setup a caching mechanism. I’ve made a QMap<QString, QStringList> in which I store all queries with their results. On every insert, update or delete command I clear this cache. On every select, I first look in the QMap if the result is already there, if not then execute the actual query.
It can give you an enormous boost in speed if you keep this cache in mind. For example when you want to know if a particular flag (a label, or the fact if it is a delete message), you might just want to make the query so that it returns all flags. That will be stored in the cache. If you want to query another flag, the result is already in the cache and it returns very fast.
Of course you can setup multiple caches so the are only cleared when you want. Also you should centralise the database access. If you open a database link from each class seperatly, you might not profit a lot from a cache.
I hope you can use these tips…
Doesn’t sqlite have its own caching mechanism already? I would think a cache managed by the db would be better.. (for handling dirty data and whatnot)
a quick search reveals this this.. not sure if it’s relevant or not though..
sure that can be usefull, but still means you need to think in the future. Say you know you need these two queries:
select blue from colors
select red from colors
than in my cache and sqlite’s cache you should write:
select blue,red from colors
and using c++ to deliver blue or red.
The latter saves one query to the database. Not sure id the sqlite cache does the same internally, I just wanted to point that out how you should think…
I only mentioned the sqlite cache because you mentioned having to clear your cache on update/insert/etc..
I imagine you need your own cache to do the types of optimizations you’re talking about though.
SQLite isn’t really good at optimizing the queries. Well, it’s better to say bad at optimizing queries.
Here are two queries :
SELECT a.name, b.town FROM a, b WHERE a.id = b.id;
SELECT a.name, b.town FROM b, a WHERE a.id = b.id;
These two queries are the same from a logical point of view. And if there is just small optimisations from the SQL engine, it’s the same. But with SQLite, it’s not the same. If a contains 50 records and b contains 6000 records, the first query will be blazingly fast compared to the second query (more than 10 times faster in my app)
“”"Use the ‘begin transaction’ and ‘commit transaction’ calls.”"”
It’s what I’ve been talking about in Dublin, and discussed e.g. in the Lab with Akonadi devs.
About caching: you can discover KexiDB and its Cursor class.
Try also QHash, which is claimed to be faster than QMap on lookups.
I use sqlite on an embedded system, and one thing that is also very interesting is this: if you want to check the existance of a record, for example are there records between x and y, you can speed it up by not using count(*) but rather ‘select 1 from a where …’ which makes sqlite not go into the record data.
of course add ‘limit 1′, so: ‘select 1 from a where a.x > 100 limit 1′