Key words: FMDB SQLite transaction performance

FMDB reads and writes a lot of data, how to ensure performance? Like writing 100,000 pieces of data to a database

A written.

In iOS, FMDB just wraps SQLite. So here is an example of the use of FMDB. Because SQLite allows only one write operation ata time, the FMDatabaseQueue operation is recommended in multithreading.

1. Write using FMDatabaseQueue

Code 1.1.
- (void)excuteSqlByQueue:(NSString *)sql {
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        FMDatabaseQueue *dbQueue = [self FMDatabaseQueue];
        __block NSInteger i = 0;
        [dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
            while (i++ < 100000) {
                [db executeUpdate:@"insert into student (name, age, height, score, date) values (? ,? ,? ,? ,?) ".@"John",
                 @(arc4random()%(30 - 20 + 1) + 20),
                 @((arc4random()%(17 - 15 + 1) + 15) *0.1),
                 @(arc4random()%(90 - 50 + 1) + 50),NSDatedate]]; }}]; }); }Copy the code
1.2 CPU Usage and Time

2. Write using Transaction

Code 2.1.
- (void)excuteSqlByQueue:(NSString *)sql {
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        FMDatabaseQueue *dbQueue = [self FMDatabaseQueue];
        __block NSInteger i = 0;
        [dbQueue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
            while (i++ < 100000) {
                [db executeUpdate:@"insert into student (name, age, height, score, date) values (? ,? ,? ,? ,?) ".@"John",
                 @(arc4random()%(30 - 20 + 1) + 20),
                 @((arc4random()%(17 - 15 + 1) + 15) *0.1),
                 @(arc4random()%(90 - 50 + 1) + 50),NSDatedate]]; }}]; }); }Copy the code
2.2 CPU Usage and Time

Conclusion 3.

Insert 100,000 pieces of data:

  • The queue takes 32 seconds, and the CPU usage is 63%
  • Transaction takes 8 seconds and the CPU usage is 34%

Overall comparison using Transaction is more efficient.

The default SQLite database insert will trigger a transaction every time it writes to the database. With transaction commit, all insert operations are committed uniformly.

Ii. Read the

Data reading can increase query efficiency by adding indexes.

1. Query without indexes

Code 1.1.
- (void)select {
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        FMDatabaseQueue *dbQueue = [self FMDatabaseQueue];
        [dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
            db.shouldCacheStatements = YES;
            FMResultSet *res = [db executeQuery:@"select 'select' as class, id, name, age, height, score from student where score = 100"];
            while (res.next) {
                NSString *class = [res stringForColumn:@"class"];
                NSInteger ID = [res intForColumn:@"id"];
                NSString *name = [res stringForColumn:@"name"];
                NSInteger age = [res intForColumn:@"age"];
                CGFloat height = [res doubleForColumn:@"height"];
                NSInteger score = [res intForColumn:@"score"];
                NSLog(@"class= %@,ID=%ld, name=%@, age=%ld, height=%f, score=%ld".class, (long)ID, name, (long)age, height, (long)score); }}]; }); }Copy the code
1.2 CPU Usage and Time

2. Use indexes to read data

Code 2.1.
- (void)createIndex {
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        FMDatabaseQueue *dbQueue = [self FMDatabaseQueue];
        [dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
            [db executeUpdate:@"create index score_index on student (score)"];
        }];
    });
}

- (void)select {
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        FMDatabaseQueue *dbQueue = [self FMDatabaseQueue];
        [dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
            db.shouldCacheStatements = YES;
            FMResultSet *res = [db executeQuery:@"select 'insert-index' as class, id, name, age, height, score from student indexed by score_index where score = 100"];
            while (res.next) {
                NSString *class = [res stringForColumn:@"class"];
                NSInteger ID = [res intForColumn:@"id"];
                NSString *name = [res stringForColumn:@"name"];
                NSInteger age = [res intForColumn:@"age"];
                CGFloat height = [res doubleForColumn:@"height"];
                NSInteger score = [res intForColumn:@"score"];
                NSLog(@"class= %@,ID=%ld, name=%@, age=%ld, height=%f, score=%ld".class, (long)ID, name, (long)age, height, (long)score); }}]; }); }Copy the code
2.2 CPU Usage and Time

Conclusion 3.

Read 1 piece of data:

  • No index Reading takes 0.01 seconds
  • Index reading takes 0.001s

Comprehensive comparison using index read efficiency is higher.

Reason: An Index is a special lookup table that database search engines use to speed up data retrieval. Simply put, an index is a pointer to the data in a table. An index in a database is very similar to an index in a book. Note, however, that indexes are not a panacea, and sometimes adding indexes can make queries less efficient. The value of an index is to help you locate quickly. If you locate a lot of data, it loses its value.

4. When should indexes be avoided?

  • The query result is large

    • Indexes should not be used insmallerOn the table.
    • Indexes should not be used inLarge data duplicationFor example, more than 10%.
    • Indexes should not be used inContains a lot of NULL valuesOn the column.

  • Frequent operations

    • Indexes should not be used in aFrequent mass update or insert operationsOn the table.
    • Indexes should not be used inFrequent operationOn the column.

5. Special circumstances

If a field has a small value, such as gender, there is usually no need to create an index. However, the distribution of values should also be considered, and indexes can be used if the final query results are small.

Code store address