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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
+ (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:
1 |
[db setShouldCacheStatements:NO]; |
This is how I measured the time it takes:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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); |