Optimize Bulk insert performance in sqlite iphone

Question

I am trying to save more than 500 records at once in sqlite, code that I have wrote so far is given below

The method name closeDatabase closes any open connection of the database the variable ContactID is nothing but an Array which is populated with ContactID of people present in my address book.

 sqlite3_exec(databaseRefObj, "BEGIN TRANSACTION", 0, 0, 0);

                for (NSString *contactIDstr in contactID)
                {
                    const char *insertintoGropsMember = "My insert query here";
                    NSString *groupIDstr = [NSString stringWithFormat:@"%d",grpID];

                    [self closeDatabase];

                    if(sqlite3_prepare_v2(databaseRefObj, insertintoGropsMember, -1, &sqlstatement, NULL)==SQLITE_OK)
                    {
                        sqlite3_bind_text(sqlstatement, 1, [groupIDstr UTF8String], -1, SQLITE_TRANSIENT);
                        sqlite3_bind_text(sqlstatement, 2, [contactIDstr UTF8String], -1, SQLITE_TRANSIENT);
                        sqlite3_step(sqlstatement);
                    }
                    else
                    {
                        successFlag = NO;
                    }


                    sqlite3_finalize(sqlstatement);
                    sqlite3_close(databaseRefObj);
                }

                sqlite3_exec(databaseRefObj, "END TRANSACTION", 0, 0, 0);

The process of data insertion is quite slow here and I would like to know where am I going wrong or what steps should I follow to optimize my sqlite performance.

Please note that I am not using any kind of index on the column on which I want to perform the insert.

Why convert the integer to a string? Why use SQLITE_TRANSIENT? Why prepare the same statement multiple times? — trojanfoe
@trojanfoe: It's a method that i wrote to force close any open database connection before executing an sqlite query. I am preparing the same statement multiple times because the variable named ContactID is an Array which holds multiple IDs and i have to add all those in the DB. — Radix
Why do you need to do that? You want the database connection open... — trojanfoe

trojanfoe trojanfoe · Accepted Answer · 2013-02-05T08:26:34

I can see several issues with your code:

You appear to be closing the database during processing.
You aren't taking advantage of the statement prepare/multiple binding capability.
You are binding the integers as text, when sqlite3 will happily bind integer values

Try this code:

BOOL ok = YES;
sqlite3_exec(databaseRefObj, "BEGIN TRANSACTION", 0, 0, 0);
const char *insertintoGropsMember = "My insert query here";
if (sqlite3_prepare_v2(databaseRefObj, insertintoGropsMember, -1, &sqlstatement, NULL) == SQLITE_OK)
{
    for (NSString *contactIDstr in contactID)
    {
        sqlite3_bind_int(sqlstatement, 1, grpID);
        sqlite3_bind_text(sqlstatement, 2, [contactIDstr UTF8String], -1, 0);
        if (sqlite3_step(sqlstatement) != SQLITE_DONE)
        {
            NSLog(@"Error inserting row: %s", sqlite3_errmsg(databaseRefObj));
            ok = NO;
            break;
        }
        sqlite3_reset(sqlstatement);
    }
    sqlite3_finalize(sqlstatement);

    sqlite3_exec(databaseRefObj, ok ? "COMMIT" : "ROLLBACK", 0, 0, 0);
}
else
{
    NSLog(@"Error preparing statement: %s", sqlite3_errmsg(databaseRefObj));
}

Optimize Bulk insert performance in sqlite iphone

1 Answers