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, Commented Feb 5, 2013 at 8: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
– Radix, Commented Feb 5, 2013 at 8:19
Why do you need to do that? You want the database connection open... — trojanfoe
– trojanfoe, Commented Feb 5, 2013 at 8:19

trojanfoe · Accepted Answer · 2013-02-05 09:25:16Z

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 5, 2013 at 9:25

answered Feb 5, 2013 at 8:26

trojanfoe

123k23 gold badges219 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Radix Over a year ago

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.

trojanfoe Over a year ago

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.

Radix Over a year ago

It worked but still it did not help in decreasing the time required :( Should i put the function in a GCD or in MainThread ?????

trojanfoe Over a year ago

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: stackoverflow.com/questions/1711631/…

Collectives™ on Stack Overflow

Optimize Bulk insert performance in sqlite iphone

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related