iOS - improve insert performance SQLite

Question

How can i improve performance insert data in SQLite Database.

The Amount of data is 30000 Records. The Code is:

- (void)insertBranchDB:branchlistArray
{
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    int count = 0;
    for (Branch *branch in branchlistArray) {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"insert or replace into Branch (id, lang, lat, lng, province_id, district_id, optional, bus, status, name, address, contact, telephone, remark, province_name, district_name, lastupdate) values (\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\")",branch._id,branch.lang,branch.lat,branch.lng,branch.province_id,branch.district_id,branch.optional,branch.bus,branch.status,branch.name,branch.address,branch.contact,branch.telephone,branch.remark,branch.province_name,branch.district_name,branch.lastupdate];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE) {
            NSLog(@"Completed to Add Branch %d",count);
            count;
        }
        else {
            NSLog(@"Failed to Add Branch %d",count);
            count;
        }
    }
    sqlite3_finalize(statement);
    sqlite3_close(database);
    }
}

You can move the definition of `insertSQL` and assignment to `insert_stmt` outside the loop. This would mean using placeholders for the values ("?"), and filling them inside the loop, using the `sqlite3_bind_*` family of functions. — Avi, Dec 22 '15 at 07:30
Avi's point may have a modest impact on performance (the significant savings is in using transactions), but you should adopt his pattern for other reasons. Specifically using `?` placeholder in your SQL and then use `sqlite3_bind_xxx()` to bind values is much safer and more robust. If someone tried to insert a value that, itself, contained a double quote, your existing SQL would fail. Using `sqlite3_bind_xxx()` will solve that problem. And, as Avi says, if you "prepare" once, and then "bind", "step", and "reset" for each loop, it will also be modestly more efficient. — Rob, Dec 22 '15 at 18:02
By the way, if you find the calling of `sqlite3_bind_xxx()` for each of those columns a bit unwieldy, you can consider using something like [FMDB](https://github.com/ccgus/fmdb), which can simplify that process. — Rob, Dec 22 '15 at 18:04

score 1 · Answer 1 · answered Dec 22 '15 at 08:52

You'll see a dramatic performance improvement if you use transactions. So before you perform all of the INSERT statements, perform a BEGIN TRANSACTION and at the end, perform a COMMIT (or COMMIT TRANSACTION or END TRANSACTION).

See the Transactions discussion at http://sqlite.org.

score -2 · Answer 2 · edited May 23 '17 at 11:44

-2

Look to insert the rows in bulk

INSERT INTO 'tablename'
SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'

So in your case:

INSERT INTO 'Branch'
SELECT 'id' AS 'column1', 'lang' AS 'column2'
UNION ALL SELECT 'row_one_.branch._id', 'row_one_.branch.lang'
UNION ALL SELECT 'row_two_.branch._id', 'row_two_.branch.lang'
UNION ALL SELECT 'row_three_.branch._id', 'row_three_.branch.lang'

look at this post Is it possible to insert multiple rows at a time in an SQLite database?

edited May 23 '17 at 11:44

Community

1
1

answered Dec 22 '15 at 07:36

Reedy

1,217
1
14
19

Then loop to 500 then run command, then compile the next 500, etc. – Reedy Dec 22 '15 at 13:59

iOS - improve insert performance SQLite

2 Answers2