Performance Tuning

Any questions about the meaning of the content of this page can be sent to vbsqliteNOSPAM@NOSPAMag-software.com (just remove he NOSPAM)

This page is an area where you can place code, hints, suggestion or text that relates to increasing the speed of the SQLite databases.

Place indexes on join and where column

Placing indexes on columns that are used in joins or where statements are the best way to increase query speed.

Use cluster indexes

Clustered indexes are indexes that comprise more than one column. These increase the speed of queries where you have multiple joins of one table or you are selecting rows based on multiple columns. (This is true in other DB systems, I don't know if it holds true in SQLite.)

How SQLite works out JOINs

From an email by D. Richard Hipp on 13 January 2004:

SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.

SQLite converts joins into where statements. This requires CPU time to perform the conversion, however small that CPU time might be. If you are calling a lot of SQL statements which are returning small amount of rows then it might reduce the overall time (CPU cost) if you manually remove the JOIN and replace them with where statements.
While savings on this might be small in tight loops of 1000's or in low memory situations it could be worth the coding. When SQLite supports a prepare/precompile interface then precompiling the SQL Statements will be the better way to go.

Order your tables properly in your where clause

Place the tables where you can eliminate the most rows by using a where clause (preferably on an indexed column) first, in order to limit the number of JOIN operations required.

Indexes on INTEGER PRIMARY KEY columns (don't do it)

When you create a column with INTEGER PRIMARY KEY, SQLite uses this column as the key for (index to) the table structure. This is a hidden index (as it isn't displayed in SQLite_Master table) on this column. Adding another index on the column is not needed and will never be used. In addition it will slow INSERT, DELETE and UPDATE operations down.

Use transactions when updating tables

Make sure that you wrap up all multiple updates inside a transaction, e.g.:

BEGIN TRANSACTION;
UPDATE table1 SET col1='1';
UPDATE table1 SET col1='2';
...
INSERT INTO table1(col1) VALUES ('2');
...
COMMIT TRANSACTION;

(The word "TRANSACTION" is optional.)

Using a transaction is the fastest way to update data in SQLite. Basically, this is how it works: After each transaction the SQLite engine closes and opens the database file. When SQLite opens a database file it populates the SQlite internal structures, which takes time. So if you have 100 updates and don't use a transaction then SQlite will open and close the database 100 times. Using transactions improves speed. Use them.