IOS Bulk Update database with Fmdb transactions

When it comes to "business", I think that SQLite itself is supporting transactional operations, Fmdb as the upper package of SQLite also support the transaction, so that so many students do not understand the word "business" to explain it: usually once sqlite3_exec is a transaction, If you want to insert new data into the Stutent table in the database, the specific process of the transaction is to start a new thing, insert data, and commit the transaction, then when we want to insert 500 data into the table, if the normal operation will be executed 500 times "start new things The process of submitting a transaction by inserting data.

Well, today's focus, for example: If a factory in Beijing to pick up a Shanghai B Company's 500 products orders, think about: A factory is the production of a product immediately sent to B Company or 500 products all finished production and then sent to B Company? The answer must be the latter, because the former wastes a lot of time, manpower and material costs between Beijing and Shanghai. The same can be done with our database operations, and here are my own two tests for using transactions and not using transactions:

Test code:

A total of three methods of inserting data were called in the following code, since the first call contains a series of procedures such as database and table creation, so the time computed for the 23rd invocation of the insertion method is accurate. (Database with Fmdb)

[[Testdatabase Sharedinstancedb] insertdata:0 Usetransaction:no];

NSDate *date1 = [NSDate Date];

[[Testdatabase Sharedinstancedb] insertdata:500 Usetransaction:no];

NSDate *date2 = [NSDate Date];

Nstimeinterval a = [date2 timeIntervalSince1970]-[date1 timeIntervalSince1970];

NSLog (@ "Do not use transactions to insert 500 data%.3f seconds", a);

[[Testdatabase Sharedinstancedb] insertdata:1000 Usetransaction:yes];

NSDate *date3 = [NSDate Date];

Nstimeinterval B = [Date3 timeIntervalSince1970]-[Date2 timeIntervalSince1970];

NSLog (@ "Use transactions to insert 500 data%.3f seconds", b);

The following is a concrete insert implementation method,

UseTransaction uses transaction processing for true delegates, instead of using

-(void) InsertData: (int) FromIndex usetransaction: (BOOL) usetransaction

{

[_database Open];

if (usetransaction) {

[_database BeginTransaction];

BOOL isrollback = NO;

@try {

for (int i = FromIndex; i<500+fromindex; i++) {

NSString *nid = [NSString stringwithformat:@ "%d", I];

NSString *strname = [[NSString alloc] initwithformat:@ "student_%d", I];

NSString *sql = @ "INSERT into Student (id,student_name) VALUES (?,?)";

BOOL a = [_database executeupdate:sql,nid,strname];

if (!a) {

NSLog (@ "Insert failed 1");

}

}

}

@catch (NSException *exception) {

Isrollback = YES;

[_database rollback];

}

@finally {

if (!isrollback) {

[_database commit];

}

}

}else{

for (int i = FromIndex; i<500+fromindex; i++) {

NSString *nid = [NSString stringwithformat:@ "%d", I];

NSString *strname = [[NSString alloc] initwithformat:@ "student_%d", I];

NSString *sql = @ "INSERT into Student (id,student_name) VALUES (?,?)";

BOOL a = [_database executeupdate:sql,nid,strname];

if (!a) {

NSLog (@ "Insert failed 2");

}

}

}

[_database Close];

}

Judging from the real data above, you should know that you should take advantage of transaction processing when you batch update the database!

Summary: The use of transactions is to commit all tasks after the completion of the results of a one-time submission to the database, if this process occurs when an exception will be performed rollback operations, so as to save a large number of repeated submissions wasted time

IOS Bulk Update database with Fmdb transactions