Performance differences between SQLite on Android and iOS

Question

I am trying to perform a benchmark between SQLite performance in Android and iOS for a project, and seem to get really bad performance on the iOS platform, compared to Android.

What I am trying to achieve is to measure the time to insert a number of rows (5000) into the SQLite DB and compare between platforms. For Android I get results around 500ms to perform all 5000 inserts, but for iOS the same operation takes above 20s. How can this be?

This is a snippet of my iOS code (the insert part), dataArray is an array with 5000 random 100 char NSStrings:

int numEntries = 5000;
self.dataArray = [[NSMutableArray alloc] initWithCapacity:numEntries];//Array for random data to write to database

//generate random data (100 char strings)
for (int i=0; i<numEntries; i++) {
    [self.dataArray addObject:[self genRandStringLength:100]];
}

// Get the documents directory
NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *docsDir = [dirPaths objectAtIndex:0];

// Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent: @"benchmark.db"]];

NSString *resultHolder = @"";

//Try to open DB, if file not present, create it
if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK){

    sql = @"CREATE TABLE IF NOT EXISTS BENCHMARK(ID INTEGER PRIMARY KEY AUTOINCREMENT, TESTCOLUMN TEXT)";

    //Create table
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL) == SQLITE_OK){
        NSLog(@"DB created");
    }else{
        NSLog(@"Failed to create DB");
    }

        //START: INSERT BENCHMARK
        NSDate *startTime = [[NSDate alloc] init];//Get timestamp for insert-timer

        //Insert values in DB, one by one
        for (int i = 0; i<numEntries; i++) {
            sql = [NSString stringWithFormat:@"INSERT INTO BENCHMARK (TESTCOLUMN) VALUES('%@')",[self.dataArray objectAtIndex:i]];
            if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL) == SQLITE_OK){
                //Insert successful
            }
        }

        //Append time consumption to display string
        resultHolder = [resultHolder stringByAppendingString:[NSString stringWithFormat:@"5000 insert ops took %f sec\n", [startTime timeIntervalSinceNow]]];

        //END: INSERT BENCHMARK

Android code snippet:

           // SETUP
           long startTime, finishTime;

        // Get database object
            BenchmarkOpenHelper databaseHelper = new BenchmarkOpenHelper(getApplicationContext());
            SQLiteDatabase database = databaseHelper.getWritableDatabase();

            // Generate array containing random data
            int rows = 5000;
            String[] rowData = new String[rows];
            int dataLength = 100;

            for (int i=0; i<rows; i++) {
                rowData[i] = generateRandomString(dataLength);
            }

            // FIRST TEST: Insertion
            startTime = System.currentTimeMillis();

            for(int i=0; i<rows; i++) {
                database.rawQuery("INSERT INTO BENCHMARK (TESTCOLUMN) VALUES(?)", new String[] {rowData[i]});
            }

            finishTime = System.currentTimeMillis();
            result += "Insertion test took: " + String.valueOf(finishTime-startTime) + "ms \n";
            // END FIRST TEST

Answer 1

On iOS, in addition to the BEGIN/COMMIT change that StilesCrisis discussed, which offers the most dramatic performance difference, if you want to further optimize your iOS performance, consider preparing the SQL once and then repeatedly call sqlite3_bind_text, sqlite3_step, and sqlite3_reset. In this case, it seemed to make it roughly twice as fast.

So, here's my rendition of your existing iOS logic with sqlite3_exec (which uses stringWithFormat and %@ to manually build the SQL every time):

- (void)insertWithExec
{
    NSDate *startDate = [NSDate date];

    NSString *sql;

    if (sqlite3_exec(database, "BEGIN", NULL, NULL, NULL) != SQLITE_OK)
        NSLog(@"%s: begin failed: %s", __FUNCTION__, sqlite3_errmsg(database));

    for (NSString *value in dataArray)
    {
        sql = [NSString stringWithFormat:@"INSERT INTO BENCHMARK (TESTCOLUMN) VALUES('%@')", value];
        if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, NULL) != SQLITE_OK)
            NSLog(@"%s: exec failed: %s", __FUNCTION__, sqlite3_errmsg(database));
    }

    if (sqlite3_exec(database, "COMMIT", NULL, NULL, NULL) != SQLITE_OK)
        NSLog(@"%s: commit failed: %s", __FUNCTION__, sqlite3_errmsg(database));

    NSTimeInterval elapsed = [[NSDate date] timeIntervalSinceDate:startDate];

    // log `elapsed` here
}

Here's an optimized rendition of the code where I prepare the SQL only once, but then use sqlite3_bind_text to bind our data to the same ? placeholder in the SQL that your Android code used:

- (void)insertWithBind
{
    NSDate *startDate = [NSDate date];

    if (sqlite3_exec(database, "BEGIN", NULL, NULL, NULL) != SQLITE_OK)
        NSLog(@"%s: begin failed: %s", __FUNCTION__, sqlite3_errmsg(database));

    sqlite3_stmt *statement;

    NSString *sql = @"INSERT INTO BENCHMARK (TESTCOLUMN) VALUES(?)";

    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) != SQLITE_OK)
        NSLog(@"%s: prepare failed: %s", __FUNCTION__, sqlite3_errmsg(database));

    for (NSString *value in dataArray)
    {
        if (sqlite3_bind_text(statement, 1, [value UTF8String], -1, NULL) != SQLITE_OK)
            NSLog(@"%s: bind failed: %s", __FUNCTION__, sqlite3_errmsg(database));

        if (sqlite3_step(statement) != SQLITE_DONE)
            NSLog(@"%s: step failed: %s", __FUNCTION__, sqlite3_errmsg(database));

        if (sqlite3_reset(statement) != SQLITE_OK)
            NSLog(@"%s: reset failed: %s", __FUNCTION__, sqlite3_errmsg(database));
    }

    sqlite3_finalize(statement);

    if (sqlite3_exec(database, "COMMIT", NULL, NULL, NULL) != SQLITE_OK)
        NSLog(@"%s: commit failed: %s", __FUNCTION__, sqlite3_errmsg(database));

    NSTimeInterval elapsed = [[NSDate date] timeIntervalSinceDate:startDate];

    // log `elapsed` here
}

On my iPhone 5, it took 280-290ms to insert 5,000 records using your sqlite3_exec logic (my insertWithExec method) and it took 110-127ms to insert the same 5,000 records with sqlite3_bind_text, sqlite3_step and sqlite3_reset (my insertWithBind method). My numbers aren't comparable to yours (different device, inserting different dataValues objects, I did it in a background queue, etc.), but it is notable that it took less than half as long when preparing the SQL statement once, and then only repeating the bind, step, and reset calls.

Looking at the Android code, I notice that you're using the ? placeholder, so I assume that it's doing sqlite3_bind_text behind the scenes, too (though I don't know if it's preparing it once and binding/stepping/resetting every time, or re-preparing every time; probably the latter).

---

As an aside, as a general rule of thumb, you should always use the ? placeholder, like you did in Android, rather than building the SQL manually with stringWithFormat, as it saves you from needing to manual escape apostrophes in your data, protects you against SQL injection attacks, etc.

Answer 2

You need to use a transaction--start by executing BEGIN and finish with COMMIT.

This should greatly improve INSERT performance.

http://www.titaniumdevelopment.com.au/blog/2012/01/27/10x-faster-inserts-in-sqlite-using-begin-commit-in-appcelerator-titanium-mobile/

Once that's done I'd expect 5000 inserts to be quite fast on both platforms.

Here is another StackOverflow answer which lists a ton of different things which can improve SQLite performance, including using bind variables and enabling various PRAGMA modes which trade off robustness for speed: Improve INSERT-per-second performance of SQLite?