Based on the experience I gained while developing my Zaurus media player, here is a short compendium of optimization rules, tweaks and hints when using SQLite on an embedded system (may apply to other systems as well):
- Simplify the database schema as much as possible – even if that means redundant data or illogical structure
- Don’t generalize the database schema – generalization will mostly sacrifice performance and one can’t afford that on an embedded system with its tight restrictions, even if it is more convenient for the developer.
- Only use relations (via IDs etc.) where absolutely necessary. The overhead for lookup and joining tables is considerable, even with an index on the relation.
- Order the tables correctly in SELECTs. Put a table left-most if it is lacking an index on the relation. More details are here.
In general: Check the order of tables in the SELECT statement. A different permutation may be more optimal. Profile. - Prepare your statements and bind values where applicable. This way you can get rid of the parser and VM creation overhead in tight loops (e.g. when inserting and updating).
- Use transactions – even if you’re just reading the data. This may yield a few milliseconds.
- Use temporary tables for intermediate results. They are fast and stay in cache most of the time. Depending on how your SQLite instance is set up, data will only be swapped into an external file if the cache is saturated.
- Try to avoid using views for data you’re constantly accessing. If you can afford it, create temporary tables and insert data there. This will eliminate the overhead imposed by the view evaluation.
- Avoid sub-queries since they tend to create temporary tables and insertion of the intermediate results into those tables may be expensive.
- Try to use indices only on static data or data that changes rarely. Building an index on live or temporary data can be expensive performance-wise. Only do so if the time required for the data lookup considerably outweights the time required for building the index.
- Alternative to indices: hashkeys – Instead of using indices on very long strings, you may store the hash values of those strings as keys in the same table. A lookup via hash values may be a whole lot more efficient. This method is also very effective when you can’t afford the creation of an index due to performance reasons. Downside: You have to take care of the hashkeys. (See remarks in the comments below.)
- No useless indices. Create indices only if your queries actually use the indices on the table (check with EXPLAIN). Having useless indices around may pollute otherwise precious database cache space.
- Be cache-friendly. Depending on the memory conditions, creating temporary tables and indices may bash the cache. Reloading data back into the cache is expensive.
- Double-check your queries and profile them. The SQLite optimizer doesn’t perform as well as the optimizers of big DBs (Firebird / Interbase, PostgreSQL, Oracle etc.).
- Check compiler settings. A higher optimization setting in your C-compiler may very well yield a few tens of milliseconds. Make sure to inline functions (-O3 for GCC 2.95.x, -O2 for GCC 3.x.x and higher). Optimize for architecture and CPU. Omit stack frame pointers (-fomit-frame-pointer) if you’re not producing executables with debug symbols. This may free an additional register for the compiler to use.
- Disable unused SQLite features. This helps to reduce binary size and may also affect performance.
Here are some additional docs to consider:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows
http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations
http://www.sqlite.org/optoverview.html
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
Here are the flags I’m currently using for my Zaurus:
-DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK \
-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_LOAD_EXTENSION -DNDEBUG \
-march=armv4 -mtune=strongarm -O3 -fomit-frame-pointer
Compiler is GCC 2.95.3. Over -O2 these flags reduced the execution time of my SQL testsuite from 1040 ms down to 880 ms. This is caused mostly due to the function inlining and architecture optimization.
Unrolling loops will hurt performance in most cases. Your mileage may vary.
I’m not clear on the suggestion regarding the use of hashkeys. Are you proposing using a smaller/leaner hashkey in place of long strings as primary/foreign keys? If so, after you generate & store the hashkeys in the primary & foreign tables, wouldn’t you still need to create an index on the hashkey columns and “join” the tables via that column in order to leverage it at query time?
Thanks for the comment, Dave.
Well, it largely depends on the use case and that’s what I failed to point out.
I agree, for joining of big tables it’s probably necessary to add an index on the hashkey to at least one table assuming the table order in the query is right. For lookups via the hash value of the string an index would also be required, otherwise performance will be sacrificed.
And even with an index on the hash value, my experience is, it’s still a bit faster – provided that the hash value is an integer. I can only assume this is because indices on integers are more efficient than those on strings.
Also, I haven’t been clear here: Having hashkeys over indices pays off for smaller tables, especially for temporary tables storing small amounts of intermediate results. Selects and joins without an index seem to be faster here compared to the overall time needed for setting up the index again and looking up in the index. It all depends on how much entries you have.
Here is an example for a different use case:
When reading a playlist, my media player has to lookup metadata for each filename in the playlist within the DB.
The problem is, the filename isn’t saved in one place within the DB. Actually the filename is split into location/dirname and basename, both of which are kept in separate tables for space-saving reasons. I can’t directly use an index here. I have to split up the filename and do an indexed lookup for each part over two tables. This is slower then using the hashkey approach with its preliminaries.
That’s why I’m hashing the filename in my application to a 32-bit int. The same method is used when updating and adding new entries to the metadata table.
But then again, everything depends on the datastructures and the requirements of the application, whether a hashkey approach might be better or not.
Andre
Hi Andre,
Have you done any memory profiling on sqlite? Do you any optimization steps for reducing heap usage with out degrading performance and have least foot print.
I believe this is among the so much significant information for me. And i’m satisfied reading your article. However wanna remark on some basic things, The website style is wonderful, the articles is really excellent : D. Excellent activity, cheers
I can’t read the comments, the font is too large :)