SQLite optimization on the iPhone

Our current iPhone application makes quite heavy use of SQLite. We persist the model in the database using a Data Access Object pattern built on top of my own JDBC like SQLite layer and it is all still quite lightweight. CoreData whould have been prefereable but we have to be compatible with iPhone SDK versions prior to 3.0. We also use the database to rank trends using indexed and weighted keywords and some additional temporal factors. Thankfully the text data we search through on the device is pre-indexed so we don't have to worry too much about getting suitable data into the tables. However we do need the database to run queries against this data and we spotted early on that optimization would be beneficial here.

I had some ideas of where we could improve and with a little Googling I found a sparse but useful SQLite optimization FAQ compiled by Jim Lyon. I quickly put together a hit list of optimizations that I'd try.

Use a between construct instead of LIKE

The FAQ explains that a LIKE cannot use an index and thus where possible it should be replaced with the > and < operators. For example:

    word LIKE 'dog%'

Can be replaced with the more efficient:

    word > 'dog' AND word < 'doh'

It's not a trivial replacement. Firstly the strings you are comparing must be of one case only. I also ran into trouble with international characters which of course don't fit nicely into the byte ranges. Furthermore you must write a little logic to generate the last character(s) of the 'upper limit term' (in this case 'dog'). Incrementing the last character is all well and good - but what about when you want to perform:

    LIKE 'oz%'

I simply appended a low value character to the string to obtain the 'upper limit term' and ended up with something like: 'oz!' Thankfully I could work within these limitations for our use-cases and performance was much improved.

Move members of IN clauses into temporary tables

Many of our queries used variable length IN clauses. This made it unfeasible to prepare and cache the resultant statements and they would be prepared fresh each time. This sucked up and incredible amount of time - we might spend a second just preparing the statement. A typical clause is shown here:

    AND id IN (32, 45, 67, 68, 80)

I decided that if I moved these values into a temporary table I could use a sub-select within the IN clause and hence end up with a static statement that I could prepare once and cache:

    AND id IN (SELECT id FROM temporary_ids)

In addition to this I hoped to use a PRAGMA directive described in the FAQ to move the temporary tables off of the flash disk and into memory:

    PRAGMA temp_store MEMORY

However, this setting does not seem to take effect on the iPhone version of SQLite which was somewhat disappointing. That said, the restructuring of my IN clauses did provided yet another significant performance improvement. I wouldn't be surprised if the row inserts into the temp table actually take longer than the execution of a given IN clause. But in this instance I am avoiding the costly preparation of a statement on each call.

Order sub-queries so that smaller results are returned first

The FAQ suggests in section 5.3 that sub-queries or criteria should appear in an order such that the criteria that will exclude the most rows should appear first. I take this to mean that this (poor) example query:

    SELECT o.id

    FROM owner o, pet p

    WHERE o.age > 12 AND p.name = 'nathan' AND p.id = o.pet_id

Should be rewritten as:

    SELECT o.id

    FROM owner o, pet p

    WHERE p.name = 'nathan' AND o.age > 12 AND p.id = o.pet_id

Because the p.name criteria is far more selective than that using o.age. Okay, so it's not a great example query. However, in our queries it was quite clear which criteria would be the most selective.

Other practices

Prior to implementing these optimizations we were using many of the best practices recommended in the FAQ including:

• Batching commands into transactions
• Using indexes where appropriate and justifying the indexes with the EXPLAIN command
• VACUUMing the database file before making release builds - this had a noticeable effect on the database file size but I couldn't say that it improved the performance.

Revisiting JDBC (pt. 1)

About 6 or 7 years ago I was writing a lot of SQL and JDBC - I remember being particularly pleased when I developed an efficient implementation of Celko's Nested sets to represent hierarchical data in a Content Management project. At the time JDBC was a pretty neat way of interfacing with a relational database. However, we Enterprise Java developers have for the most part left JDBC behind in favor of excellent ORM frameworks such as Hibernate and although we are using JDBC more that ever, we do so with it operating under the covers - tucked away within our ORM framework. Sure there may be times when we have to step back to JDBC - how well would Hibernate handle hierarchical data? - but they are infrequent.

Now as I mentioned in previous posts, the main reason I found myself developing - well, debugging at first - on the iPhone platform was to investigate why a SQLite based feature of our application wasn't functioning as well as the original Java proof-of-concept. Fairly soon I was immersed in the world of SQLite's C/C++ interface - JDBC this was not. From a Java point of view it's low-level: Error conditions signaled by return value on almost every function and pointers aplenty. What I wanted was JDBC - or rather OBJCDBC - but in fact I wanted much more because in the JDBC world I had also become used to:

• Excellent connection and prepared statement pooling with the likes of DBCP.
• Concise utility methods that allowed me to avoid JDBC boiler-plate in the form of DbUtil.

I Googled a while to see if such a thing existed in the iPhone domain and thankfully found many likely candidates on the SQL Wiki (see: Objective-C section) ranging from simple wrappers to ORM frameworks. However, the simple wrappers were not as clean and object orientated as I was used to with JDBC and the ORM frameworks would not give me enough control to write some of the highly optimized SQL queries that our application demanded. I set about writing my own wrapper. One could argue that I was reinventing the wheel - but I'm always happy to learn more about wheels. My requirements were as follows:

• Option to pool connections and statements
• Check for every error condition that SQLite could possibly set and convert these into exceptions so we can adopt a try/catch/finally approach when accessing the database.
• Provide a clean and simple programmatic interface.
• Move all direct interactions with SQLite into a few sensible classes - C based SQLite code had previously been spread liberally throughout the application.

My core classes were to be as follows:

• PooledDataSource - A connection data source that also pools connections. Calling close on a connection actually returns it to the pool.
  
• Connection - Encapsulates a SQLite database handle - can also pool statements that have been prepared from this connection. Calling prepare on a connection might actually fetch and reuse a pooled PreparedStatement rather than creating a new instance. rovides methods to manage transactions.
• PreparedStatement - Prepares transient and non-transient statements, binds parameter values to statements, and executes statements. Returns ResultSets for SELECT queries. Calling close on a prepared statement returns it to the statement pool if it is not transient.
• ResultSet - An interface for stepping through a cursor and retrieving values from the row.

With these classes in place I could write some typical Data Access Object code:

    PreparedStatement statement;

    ResultSet results;

    NSNumber maxId = nil;

    @try {

        stmt = [con prepare:@"SELECT MAX(id) FROM foo WHERE bar = ?"];

        [con begin]; // OK - so we don't need this for a SELECT

        results = [stmt executeWithValues:@"dog", nil];

        if ([results next]) {

           maxId = [results getInt:1];

        }

        [con commit]; // for illustration only

    @catch (NSException* sqlX) {

        [con rollback]; // for illustration only

        // handle error

    } @finally {

        [DatabaseCommons closeQuietly:results, statement, con];

    }

I'll get more into the actual class implementation details in part 2.

Background

So a little bit of background. I have been happily developing with Java for over 9 years and am very appreciative of the wealth of frameworks available that make my job easier and more productive. In the company I work for currently I have been developing an enterprise server side Java application whose job is to aggregate and feed data to an iPhone based client. It got to the point where the server was virtually complete, scaling well and had few additional features remaining in the product backlog. We ‘server guys’ had applied best practices, patterns, and frameworks with a very satisfactory result.

I then began architecting features of the iPhone client application - with no intention of actually developing on the platform but merely providing the iPhone developers with a proof of concept that they could effectively ‘transcribe‘ into the iPhone domain. The feature in question required an SQLite database on the iPhone and as a server guy I could throw SQL together with ease - so it made sense that I should do the design. However, as the iPhone implementation of my designs progressed, many issues presented themselves on the client that just didn’t exist in my Java-based proof of concept. It was at this point I realized that I’d have to delve into the world of Objective-C and XCode. Having done a bit of C programming at university and written one MFC based OpenGL app for my coursework 12 years previous - I was struck with fear at the learning curve I probably faced.

I was very pleasantly surprised. In terms of language structure and syntax - the core things that make Java development easier (or rather: less difficult) than C were all in place in Objective-C. Exceptions, Thread classes, synchronization, interfaces, etc. were there - although often they are often called something totally different and aren’t quite like the Java equivalent. I was given Dylan McNamee’s ‘Java to Objective-C cheat sheet’ which proved a useful starting point and I was soon at ease. Then, having had a quick look at the language I wanted to get started debugging the iPhone implementation of the SQLite POC.

I’d recommend that any Java developer interested in the iPhone platform jump right in - they won’t find the learning curve that steep and in my opinion you can apply all the software development fundamentals that one is often required to know in the Enterprise Java domain to this (much smaller) platform.