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, Feb 05 '13 at 08:18
@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, Feb 05 '13 at 08:19
Why do you need to do that? You want the database connection **open**... — trojanfoe, Feb 05 '13 at 08:19

trojanfoe · Accepted Answer · 2013-02-05T09:25:16.483

0

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));
}

edited Feb 05 '13 at 09:25

answered Feb 05 '13 at 08:26

trojanfoe

120,358
21
212
242

It only inserts the first record in the array and ignores the rest i.e. record no 1 is inserted and when it goes for record no 2 then in that case sqlite3_step goes inside the if loop. – Radix Feb 05 '13 at 09:06
OK, it might be you need to use `sqlite3_reset()` which is required in versions <= 3.6.23.1. I have also added actual error reporting, which should give you more detail of failures. – trojanfoe Feb 05 '13 at 09:20
It worked but still it did not help in decreasing the time required :( Should i put the function in a GCD or in MainThread ????? – Radix Feb 05 '13 at 09:25
sqlite3 is slow to insert. You should do this in a background thread so as not to interfere with the main (UI) thread and look at this SO question regarding improving performance: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite – trojanfoe Feb 05 '13 at 09:28

Optimize Bulk insert performance in sqlite iphone

1 Answers1