SQLite FMDB Batch / Bulk Inserts

I use FMDB framework to work with SQLite database on iOS. Sometimes you need to insert many rows, many objects that come from a server to your local database. It can take some time. When I was inserting rows one by one, the method took 50 seconds to finish inserting about 1300 rows. So I decided to find a faster solution.

And found it here. To insert many rows faster you should use transactions. It is very easy, you shouldn’t use complex approaches, like preparing huge SQL statements. I got 0.5 seconds as a result on iPhone 3GS. Here is more sophisticated approach to optimize insert of millions of rows, but it’s not my case, I’m satistfied with 0.5 second result.

This is a sample code:

+ (BOOL)addToDbRegionArray:(NSArray *)regionArray
{
  FMDatabase *db = [[DBConnection sharedInstance] db];
  
  if ( ! [db open] ) {
    return NO;
  }
  
  [db beginTransaction];
  
  for (RRRegion *region in regionArray) {
    [db executeUpdate:@"INSERT INTO regions (region_id, name, city_id, country_id, has_metro, is_popular) VALUES (?, ?, ?, ?, ?, ?);" withArgumentsInArray:@[@(region.sid), region.name, @(region.cityId), @(region.countryId), @(region.hasMetro), @(region.isPopular)]];
  }
  
  [db commit];
  [db close];
  
  return YES;
}

I have a class RRRegion, which objects I am inserting. I open database, then begin a transaction, execute as many updates as I have objects and commit a transaction, close database.

I should mention, that when I was using caching of statements by FMDB, I got strange leak statement errors, so I don’t cache them. I don’t have this line:

[db setShouldCacheStatements:NO];

This is how I measured the time it takes:

NSDate *start = [NSDate date];
NSArray *jsonArray = (NSArray *)JSON;
NSMutableArray *regionArray = [NSMutableArray new];
for (NSDictionary *jsonDic in jsonArray) {
  RRRegion *region = [[RRRegion alloc] initFromDic:jsonDic];
  [regionArray addObject:region];
}

[RRRegion addToDbRegionArray:regionArray];

NSDate *finish = [NSDate date];
NSTimeInterval interval = [finish timeIntervalSinceDate:start];
NSLog(@"Time consumed to add regions: %f", interval);