App crashes while inserting 7000 rows into SQLite database

Question

I have a problem, I want to insert 7000 rows into an SQLite database but when I reach the 6000th row, my application crashes with no error.

I used a profiler and apparently the problem is RAM usage on my iPad (when the app allocates 435MB of RAM it crashes).

So my question is how to manage memory during inserts? (and yes, I need to have this data locally because my app needs to work in disconnected mode).

Here is the code I use to insert rows in the table:

-(void)MultiInsertFamille:(NSArray *)categories{
sqlite3_stmt *stmt=nil;
sqlite3 *bdd;

NSString *database = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"myBDD.sqlite"];
if (sqlite3_open([database UTF8String], &bdd) == SQLITE_OK)
{
    sqlite3_exec(bdd, "BEGIN", 0, 0, 0);
    const char *sqlstatement="insert into Famille values(?,?,?,?,?,?,?)";

    if(sqlite3_prepare_v2(bdd,sqlstatement , -1, &stmt, NULL)==SQLITE_OK)
    {
        int hasError= 0;
        int i = 0;
        for(NSString *path in categories)
        {
            i++;
            NSString *nameFile = [[path componentsSeparatedByString: @"."] objectAtIndex:0];
            XMLParserFamille *fa = [[XMLParserFamille alloc] initXMLParserFamille:nameFile parseError:nil];
            FamilleData *fam = fa.famille;
            [fa release];

            if (fam.champ_Recherche)
                sqlite3_bind_text(stmt, 1, [fam.champ_Recherche cStringUsingEncoding:NSUTF8StringEncoding], -1, SQLITE_TRANSIENT);
            else
                sqlite3_bind_text(stmt, 1, "NULL", -1, SQLITE_TRANSIENT);

            NSString *pathImTh = @"";
            for (Attribut *att in fam.Attributs)
            {
                if ([att.name isEqualToString:@"ProductThumbnail"])
                {
                    pathImTh = att.value;
                    break;
                }
            }
            if (pathImTh)
                sqlite3_bind_text(stmt, 2, [pathImTh cStringUsingEncoding:NSUTF8StringEncoding], -1, SQLITE_TRANSIENT);
            else
                sqlite3_bind_text(stmt, 2, "NULL", -1, SQLITE_TRANSIENT);

            if (fam.nom)
                sqlite3_bind_text(stmt, 3, [fam.nom cStringUsingEncoding:NSUTF8StringEncoding], -1, SQLITE_TRANSIENT);
            else
                sqlite3_bind_text(stmt, 3, "NULL", -1, SQLITE_TRANSIENT);

            if (fam.ordre)
                sqlite3_bind_int(stmt, 4, [fam.ordre intValue]);
            else
                sqlite3_bind_int(stmt, 4, 0);

            if (fam.uid)
                sqlite3_bind_text(stmt, 5, [fam.uid cStringUsingEncoding:NSUTF8StringEncoding], -1, SQLITE_TRANSIENT);
            else
                sqlite3_bind_text(stmt, 5, "NULL", -1, SQLITE_TRANSIENT);

            if (fam.uid_Categorie)
                sqlite3_bind_text(stmt, 6, [fam.uid_Categorie cStringUsingEncoding:NSUTF8StringEncoding], -1, SQLITE_TRANSIENT);
            else
                sqlite3_bind_text(stmt, 6, "NULL", -1, SQLITE_TRANSIENT);

            if (fam.xmlErrone)
                sqlite3_bind_int(stmt, 7, [fam.xmlErrone intValue]);
            else
                sqlite3_bind_int(stmt, 7, 0);

            if(sqlite3_step(stmt)==SQLITE_DONE)
            {
                NSLog(@"OK");
            }
            else
            {
                NSLog(@"sqlite3_step error famille: '%s'", sqlite3_errmsg(bdd));
                hasError= 1;
            }
            sqlite3_reset(stmt);
            if (i==5500)
                break;
        }
        //if( hasError == 0 ) {
        sqlite3_exec(bdd, "COMMIT", 0, 0, 0);
        //}
        //else {
        //    sqlite3_exec(bdd, "ROLLBACK", 0, 0, 0);
        //}
    }
}
sqlite3_close(bdd);}

Answer 1

You can try to do smaller transactions, for instance begin/commit every 1000 rows.

Something like that might help:

NSInteger rowsProcessed = 0;
for(NSString *path in categories)
{
  if (rowsProcessed == 0)
  {
     sqlite3_exec(bdd, "BEGIN", 0, 0, 0);
  }
  if (rowsProcessed++ > 1000)
  {
     sqlite3_exec(bdd, "COMMIT", 0, 0, 0);
     rowsProcessed = 0;
  }

  ...
  // your inserts here
  ...
}
if (rowsProcessed != 0) 
{
   sqlite3_exec(bdd, "COMMIT", 0, 0, 0);
}

Workarounds for committing without committing (might be performance impacting thus sqlite is adding implicit transactions if you remove begin/commit completely):

• If your table is empty when you start you can completely remove begin/commit and just delete all rows from a table in the end in case of error.
• If your table is not empty but you still want to remove all rows in case of error, then you might remove begin/commit and add some column which would keep some unique id when you insert these rows (one unique id for all inserts in this method) so later you can delete all rows by this id in case of error.

Answer 2

There can be a lot of temporary objects created within your loop (e.g. String handling).

You can try to put your stuff into an autorelease pool block, so temp objects will be released at the end of every loop.

for(NSString *path in categories) {
   @autoreleasepool {
      your stuff here ..
   }
}