I saw the latest article by Eric Sink explaining the issues that he was having with multiple copies of SQLite running. His article mentioned that iOS is running 3.7.13 and the current version of SQLite is 22.214.171.124 and along with a bunch of other improvements it is ‘significantly faster’. Well sign me up, perfect for what I need.
I do maintenance work on an app which uses SQLite a fair bit. Unfortunately the app uses more anti-patterns than it does patterns. Unsurprisingly it is quite slow and I am always looking for ways to speed it up (without a complete rewrite the client won’t pay for that). I was hoping using the latest version of SQLite will give me a quick win speeding up the app.
As a quick test I took the misbehaving app and ran a quick test with the stock iOS SQLite installation and the latest and greatest version of SQLite (126.96.36.199) via cocoapods. I looked at two parts of the app that are in the ‘worst offenders’ category, the first was the initial sync which loads a huge number of JSON files, the second was the loading of all of the customer details. While the sync takes a long time (10+ minutes, mostly waiting for the server) it only happens when the user first uses the app. The customer details happens on a more regular basis.
The testing is very unscientific, basically I did three runs with each version, deleting the app between runs. The timing for the sync was only the time to process the JSON and insert it into the database, it didn’t include waiting for the server, most of the time is spent inserting but it does include some checking for records. The time for the customer loading was just loading the records, the select statement has one where clause and SQLite is sorting the records.
The process of changing SQLite, remove the link to the built in library, add the SQLite to the pods file (pod ‘sqlite3′, ‘~> 3.8′) and run pod install. Cocoapods is a install of the SQLite amalgamation build.
For the iOS built in SQLite (v 3.7.13)
The average of the sync: 377 seconds
Customer load: 3.4 seconds
For the Cocoapods SQLite build (v 188.8.131.52)
The average of the sync: 389 seconds
Customer load: 4.4 seconds
I found this interesting, the time to load all the customer records includes parsing JSON files and performing a bunch of sanity checks. The customer load is pretty much all SQLite and object creation, the second part I wouldn’t have thought would be much slower. From the clients perspective the customer load is much more critical as this happens on average once per day for the end user. The data sync would happen at worst once per week, my guess is most clients probably only do it once per month.
My final test to see if Apple has optimised the default SQLite implementation, or if for my case 3.7.13 was just faster I made a quick podspec for version 3.7.13 so it would install the same way that the 184.108.40.206 build was install. Cocoapods doesn’t have it in its specs, but I made a local copy of the 3.7.14 spec and changed the version number. I didn’t do the sync, mainly because I am lazy, and the results have been pretty close, but I did run the customer test three times.
For the Cocoapods SQLite build (v 3.7.13)
Customer load: 3.3 seconds
It is pretty much in the ball park of the built in library.
I am sure with the proper tweaking and different test environments that 220.127.116.11 could be made to run faster. As I said I was trying this for a quick win, make a client happy that simply changing a library would give quick speed boost to the app. I don’t know if Apple have kept iOS at that version because increasing the version slows things down, however I will be rerunning the speed test if we get a new version of SQLite at WWDC.