10

I have to add the huge data in the SQLite database and need some suggestion how I can do that functionality in iOS. I have to sync around 1 GB of data from server to the iPhone SQLite database.

I need alternative ways to store fast data in the database on the iOS side. I have tried to store data one by one, but it's taking too much time to synchronise and store data.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
DeveshM
  • 476
  • 4
  • 11
  • use SQLTransaction to insert data into table – Kalpesh Jun 28 '13 at 12:39
  • Is the 1GB for an initial sync only, or recurring? – Joachim Isaksson Jun 28 '13 at 12:39
  • 3
    If a bunch of records, transactions can help. If you have large blobs, SQLite is ill-suited for that. Tell us more about the data, perhaps sharing some source code. BTW, it strikes me that the limiting factor is going to be the retrieving it from the server, not the saving it on your device. – Rob Jun 28 '13 at 12:42
  • Yeah, figure out where the bottleneck is. Likely, if you're doing a lot of inserts, using transactions will provide at least a 10x performance improvement. And of course use prepared statements for any repeated statements. – Hot Licks Jun 28 '13 at 14:57
  • Just curious, are you sure you want to push that much data to users' phones? If it is a commercial app, you will likely have a ton of problems with users running out of space. – moodboom Jul 25 '16 at 16:54

1 Answers1

6

I would suggest you read this Stack Overflow question:

How do I improve the performance of SQLite?

It's a very thourough look at how to improve the performance of SQLite in general, and it was very helpful when I was hitting speed problems trying to insert 100,000 records into an SQLite database on iOS.

In specific, the use of Transactions dramatically cut down on the overall insert speed. Here is a short block of sample code so you can see what I mean:

const char *dbpath = [[Utilities pathInDocumentsFolder: MY_DATABASE] UTF8String];
const char *sql = "INSERT INTO Filters (Region, District, Territory) " \
    "VALUES (?, ?, ?)";
sqlite3 *mapDB;
char *sqliteError;

sqlite3_stmt *insertStatement;

sqlite3_open(dbpath, &mapDB);

sqlite3_exec(mapDB, "BEGIN TRANSACTION", NULL, NULL, &sqliteError);

if (sqlite3_prepare_v2(mapDB, sql, -1, &insertStatement, NULL) == SQLITE_OK) {
    for (NSArray *row in filtersArray) {
        sqlite3_bind_text(insertStatement, 1, [[row objectAtIndex: 0] UTF8String], -1, SQLITE_TRANSIENT);  // Region
        sqlite3_bind_text(insertStatement, 2, [[row objectAtIndex: 1] UTF8String], -1, SQLITE_TRANSIENT);  // District
        sqlite3_bind_text(insertStatement, 3, [[row objectAtIndex: 2] UTF8String], -1, SQLITE_TRANSIENT);  // Territory

        if (sqlite3_step(insertStatement) != SQLITE_DONE) {
            break;
        }

        sqlite3_clear_bindings(insertStatement);
        sqlite3_reset(insertStatement);
    }
}

sqlite3_exec(mapDB, "END TRANSACTION", NULL, NULL, &sqliteError);

sqlite3_finalize(insertStatement);

The sqlite3_exec with the BEGIN and END TRANSACTION statements are the magic.

Community
  • 1
  • 1
Axeva
  • 4,697
  • 6
  • 40
  • 64
  • +1 Agreed. When inserting lots of records, the use of transactions improves performance by several orders of magnitude. The re-binding also helps, but the performance gain is more modest. – Rob Jun 28 '13 at 14:21
  • 1
    I might also suggest logging `sqlite3_errmsg(mapDB)` if you don't get `SQLITE_OK` as the return code from the various `sqlite3` function calls (or for `sqlite3_step`, the `SQLITE_DONE` return code). – Rob Jun 28 '13 at 14:25
  • 1
    And, BTW, you have an extra `?` placeholder in your SQL. It's just an example, so it's not a big deal, but just FYI. You also don't need the "\" to carry on the string on the next line of code. You only need the backslash if you don't have the closing quotation mark at the end of the first line and the new opening quotation mark at the start of the next. The way you have it, the backslash is unnecessary. – Rob Jun 28 '13 at 14:25