fmdb sqlite插入数据慢
之前使用FMDB加载一个全国的行政区划的数据库表到客户端，好让用户在选择收获地址时在本地加载，提高用户的使用体验，在模拟器上跑时非常方便，但在测试时非常慢，真机加载时格外慢，加载8万多条的数据差不多要半个小时，这简直不能忍受，后来查找了很多资料，发现只要添加了事务就好了， 一行代码提升了好几十倍，原本半小时才可以的现在一分钟内就好了，10万条数据测试结果如下（结论，开启事务普通出入数据最快）：
模拟器上的结果：（时间差别不大）
2017-07-25 16:02:06.233 TestFMDB[66270:4375010] *** 创建数据花费时间 0.409968 s
2017-07-25 16:02:59.004 TestFMDB[66270:4375010] *** 普通方式插入数据花费时间 52.770025 s
2017-07-25 16:02:59.004 TestFMDB[66270:4375010] *** address fmdb 加载数据库结束
2017-07-25 16:02:06.233 TestFMDB[66270:4375010] *** 创建数据花费时间 0.409968 s
2017-07-25 16:02:59.004 TestFMDB[66270:4375010] *** 普通方式插入数据花费时间 52.770025 s
2017-07-25 16:02:59.004 TestFMDB[66270:4375010] *** address fmdb 加载数据库结束
2017-07-25 16:08:38.285 TestFMDB[66420:4379160] *** 创建数据花费时间 0.433273 s
2017-07-25 16:08:51.874 TestFMDB[66420:4379157] *** 多线程开启事务方式插入数据花费时间 13.587659 s
2017-07-25 16:09:03.745 TestFMDB[66420:4379158] *** 多线程开启事务方式插入数据花费时间 25.459356 s
2017-07-25 16:09:23.915 TestFMDB[66420:4379222] *** 多线程开启事务方式插入数据花费时间 45.629394 s
2017-07-25 16:09:40.919 TestFMDB[66420:4379211] *** 多线程开启事务方式插入数据花费时间 62.632683 s
真机测试结果（没有重现我项目中不开启事务特别慢的情况）
// 普通插入数据
2017-07-25 16:23:21.120660+0800 TestFMDB[2152:677046] *** 创建数据花费时间 1.374888 s
2017-07-25 16:24:14.512719+0800 TestFMDB[2152:677046] *** 普通方式插入数据花费时间 53.391885 s
2017-07-25 16:24:14.512885+0800 TestFMDB[2152:677046] *** address fmdb 加载数据库结束
// 普通开启事务的结果
2017-07-25 16:21:22.480394+0800 TestFMDB[2147:676024] *** 创建数据花费时间 1.359878 s
2017-07-25 16:22:00.316775+0800 TestFMDB[2147:676024] *** 开启事务方式插入数据花费时间 37.836121 s
2017-07-25 16:22:00.316933+0800 TestFMDB[2147:676024] *** address fmdb 加载数据库结束
// 开启4个线程的结果
2017-07-25 16:14:07.000092+0800 TestFMDB[2135:671904] *** 创建数据花费时间 1.484458 s
2017-07-25 16:14:16.818597+0800 TestFMDB[2135:671902] *** 线程1开启事务方式插入数据花费时间 9.818329 s
2017-07-25 16:14:26.386697+0800 TestFMDB[2135:671903] *** 线程2开启事务方式插入数据花费时间 19.386441 s
2017-07-25 16:14:36.702309+0800 TestFMDB[2135:671900] *** 线程3开启事务方式插入数据花费时间 29.702041 s
2017-07-25 16:14:47.890248+0800 TestFMDB[2135:671901] *** 线程4开启事务方式插入数据花费时间 40.889873 s
开启2个线程的结果
2017-07-25 16:15:52.293433+0800 TestFMDB[2137:672875] *** 创建数据花费时间 2.423719 s
2017-07-25 16:16:13.158949+0800 TestFMDB[2137:672877] *** 线程1开启事务方式插入数据花费时间 20.865313 s
2017-07-25 16:16:32.692071+0800 TestFMDB[2137:672876] *** 线程2开启事务方式插入数据花费时间 40.398287 s
开启2个线程不开启事务的结果
2017-07-25 16:17:20.354732+0800 TestFMDB[2140:673793] *** 创建数据花费时间 1.563348 s
2017-07-25 16:17:47.615129+0800 TestFMDB[2140:673794] *** 线程1开启事务方式插入数据花费时间 27.260129 s
2017-07-25 16:18:16.670929+0800 TestFMDB[2140:673796] *** 线程2开启事务方式插入数据花费时间 56.316027 s
具体代码如下：
#pragma mark - ###### 将数据存入数控库
- (void)testInsertDB
{
__weak typeof(self) weakSelf = self;
dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
// 创建测试数据
NSTimeInterval createDataBegin = [[NSDate date] timeIntervalSince1970];
NSMutableArray *arrayM = [NSMutableArray array];
for (int i=0; i<10*10000; i++) {
NSMutableDictionary *item = [NSMutableDictionary dictionary];
item[@"addr_id"] = [NSString stringWithFormat:@"addr_id_%d",i];
item[@"parent_addr_id"] = [NSString stringWithFormat:@"parent_addr_id_%d",i];
item[@"addr_value"] = [NSString stringWithFormat:@"addr_value_%d",i];
[arrayM addObject:item];
}
NSTimeInterval createDataEnd = [[NSDate date] timeIntervalSince1970];
CGFloat createDataCost = (createDataEnd - createDataBegin);
NSLog(@"*** 创建数据花费时间 %f s",createDataCost);
// 调用
// 普通插入
[weakSelf normalSaveData2LocalDataBaseWithNSArray:arrayM];
// 开启事务插入
//[weakSelf transationSaveData2LocalDataBaseWithNSArray:arrayM];
// 开启多个Queue和事务插入
//[weakSelf mutalQueueSaveData2LocalDataBaseWithNSArray:arrayM];
});
}
#pragma mark - ******** 普通一条一条插入
- (void)normalSaveData2LocalDataBaseWithNSArray:(NSArray *)addressList
{
NSTimeInterval insertDataBegin = [[NSDate date] timeIntervalSince1970];
// 获得Documents目录路径
NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
// 文件路径
NSString *filePath = [documentsPath stringByAppendingPathComponent:@"testAddress.sqlite"];
// 实例化FMDataBase对象
FMDatabase *db = [FMDatabase databaseWithPath:filePath];
[db open];
NSString *dropSql = @"DROP TABLE t_test_address;";
[db executeUpdate:dropSql];
// 初始化数据表
NSString *createSql = @"CREATE TABLE IF NOT EXISTS 't_test_address' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,'addr_id' VARCHAR(255),'parent_addr_id' VARCHAR(255),'addr_value' VARCHAR(255)) ";
[db executeUpdate:createSql];
for (NSDictionary *item in addressList) {
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insertRes = [db executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
NSTimeInterval insertDataEnd = [[NSDate date] timeIntervalSince1970];
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 普通方式插入数据花费时间 %f s",insertDataCost);
NSLog(@"*** address fmdb 加载数据库结束");
}
#pragma mark - ******** 开启事务插入
- (void)transationSaveData2LocalDataBaseWithNSArray:(NSArray *)addressList
{
NSTimeInterval insertDataBegin = [[NSDate date] timeIntervalSince1970];
// 获得Documents目录路径
NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
// 文件路径
NSString *filePath = [documentsPath stringByAppendingPathComponent:@"testAddress.sqlite"];
// 实例化FMDataBase对象
FMDatabase *db = [FMDatabase databaseWithPath:filePath];
[db open];
NSString *dropSql = @"DROP TABLE t_test_address;";
[db executeUpdate:dropSql];
// 初始化数据表
NSString *createSql = @"CREATE TABLE IF NOT EXISTS 't_test_address' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,'addr_id' VARCHAR(255),'parent_addr_id' VARCHAR(255),'addr_value' VARCHAR(255)) ";
[db executeUpdate:createSql];
[db beginTransaction];
for (NSDictionary *item in addressList) {
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insertRes = [db executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
[db commit];
NSTimeInterval insertDataEnd = [[NSDate date] timeIntervalSince1970];
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 开启事务方式插入数据花费时间 %f s",insertDataCost);
NSLog(@"*** address fmdb 加载数据库结束");
}
#pragma mark - ******** 多线程务事务插入
- (void)mutalQueueSaveData2LocalDataBaseWithNSArray:(NSArray *)addressList
{
NSTimeInterval insertDataBegin = [[NSDate date] timeIntervalSince1970];
// 获得Documents目录路径
NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
// 文件路径
NSString *filePath = [documentsPath stringByAppendingPathComponent:@"testAddress.sqlite"];
// 实例化FMDataBase对象
FMDatabase *db = [FMDatabase databaseWithPath:filePath];
[db open];
NSString *dropSql = @"DROP TABLE t_test_address;";
[db executeUpdate:dropSql];
// 初始化数据表
NSString *createSql = @"CREATE TABLE IF NOT EXISTS 't_test_address' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,'addr_id' VARCHAR(255),'parent_addr_id' VARCHAR(255),'addr_value' VARCHAR(255)) ";
__block NSTimeInterval insertDataEnd = [[NSDate date] timeIntervalSince1970];
//创建连个线程(模拟多线程操作)
dispatch_queue_t q1 = dispatch_queue_create("queue1", NULL);
dispatch_queue_t q2 = dispatch_queue_create("queue2", NULL);
dispatch_queue_t q3 = dispatch_queue_create("queue3", NULL);
dispatch_queue_t q4 = dispatch_queue_create("queue4", NULL);
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:filePath];
NSInteger range = addressList.count / 2;
//线程一
dispatch_async(q1, ^{
[queue inDatabase:^(FMDatabase *db2) {
[db2 beginTransaction];
for (NSInteger i = range*0; i < range*1;i++) {
NSDictionary *item = addressList[i];
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insert = [db2 executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
[db2 commit];
NSTimeInterval insertDataEnd1 = [[NSDate date] timeIntervalSince1970];
if (insertDataEnd1 > insertDataEnd) {
insertDataEnd = insertDataEnd1;
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 线程1开启事务方式插入数据花费时间 %f s",insertDataCost);
}
}];
});
//线程二
dispatch_async(q2, ^{
[queue inDatabase:^(FMDatabase *db2) {
[db2 beginTransaction];
for (NSInteger i = range*1; i < addressList.count; i++) {
NSDictionary *item = addressList[i];
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insert = [db2 executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
[db2 commit];
NSTimeInterval insertDataEnd2 = [[NSDate date] timeIntervalSince1970];
if (insertDataEnd2 > insertDataEnd) {
insertDataEnd = insertDataEnd2;
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 线程2开启事务方式插入数据花费时间 %f s",insertDataCost);
}
}];
});
/*
//线程三
dispatch_async(q3, ^{
[queue inDatabase:^(FMDatabase *db2) {
[db2 beginTransaction];
for (NSInteger i = range*2; i < range*3; i++) {
NSDictionary *item = addressList[i];
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insert = [db2 executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
[db2 commit];
NSTimeInterval insertDataEnd3 = [[NSDate date] timeIntervalSince1970];
if (insertDataEnd3 > insertDataEnd) {
insertDataEnd = insertDataEnd3;
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 线程3开启事务方式插入数据花费时间 %f s",insertDataCost);
}
}];
});
//线程四
dispatch_async(q4, ^{
[queue inDatabase:^(FMDatabase *db2) {
[db2 beginTransaction];
for (NSInteger i = range*3; i < addressList.count; i++) {
NSDictionary *item = addressList[i];
// 初始化数据表
NSString *insertSql = @"INSERT INTO t_address (addr_id, parent_addr_id,addr_value) VALUES (?,?,?)";
BOOL insert = [db2 executeUpdate:insertSql withArgumentsInArray:@[item[@"addr_id"],item[@"parent_addr_id"],item[@"addr_value"]]];
}
[db2 commit];
NSTimeInterval insertDataEnd4 = [[NSDate date] timeIntervalSince1970];
if (insertDataEnd4 > insertDataEnd) {
insertDataEnd = insertDataEnd4;
CGFloat insertDataCost = (insertDataEnd - insertDataBegin);
NSLog(@"*** 线程4开启事务方式插入数据花费时间 %f s",insertDataCost);
}
}];
});
*/
}
