Tuning SQLite Performance
SQLite is an extremely fast database engine, but it can get slow quickly if you scale the the amount of data without making adjustments. Here are the main things to keep in mind:Transactions
If you're processing more than one row of data, setup a transaction first. The performance difference is enormous. In the DataCrux (now at datacrux.com!) switchover to SQLite 3, the slight change to the query format for setting up a transaction had gone unnoticed by me, and things were taking an insanely long amount of time. For example, in one DataCrux app currently in beta, a data import took about 4 minutes without a transaction. With the transaction code fixed, the same task was completed in less than a second!
Virtual Machines
It takes some slight adjustments to how you go about writing your code, but using virtual machines (sqlite3_stmt * structs) can have a significant impact on speed. SQLite doesn't have to spend time compiling entire query strings. It just reads the input values and applies the changes. DataCrux now dynamically generates these each time a database file is opened.
Indexes
Indexes are created on columns which are frequently used in 'where' clauses. This speeds up selects, but it can significantly slow down inserts and updates. DataCrux currently does not automatically create indexes for this reason. In any case, I believe the biggest speed hit on retrieving data is re-instantiating the objects and setting their values, not finding the data in sqlite. If you do a lot more reading of data than writing, creating indexes clearly makes sense.
Sync Mode
This is perhaps the single biggest factor in how fast SQLite performs. Transactions can save you from a lot of the overhead of using individual file operations for each query statement, but it's not always possible to setup a transaction. In such a case, you may want to consider sending this query string to the database:
PRAGMA synchronous = OFF;
The performance difference can be dramatic. The tradeoff is that if a kernel panic or power loss occurs before the data reaches the disk, it's possible for the database file to become corrupt. Though, according the docs, application crashes pose no threat.
Tuning SQLite Performance
Posted Jan 5, 2005 — 6 comments below
Posted Jan 5, 2005 — 6 comments below
Oskar — Jan 05, 05 51
Carla — Jan 05, 05 52
Scott Stevenson — Jan 05, 05 53
That said, there's one particularly interest aspect of DataCrux that Apple hasn't talked about for Core Data. That feature isn't public knowledge yet but is already in production use at a few places.
Scott Stevenson — Jan 05, 05 54
Oskar — Jan 06, 05 55
De-lurker day?
Scott Stevenson — Jan 06, 05 56
Whether all of that matters to you depends on your market. The more mainstream the target audience, the more you typically have to consider platform versions. Though it's also worth balancing that with development time. If a developer uses API specific to a certain version of the OS, he may still see a net benefit even with a reduced audience if the API saves a significant amount of time or enables certain functionality.