iOS-Objective C: How to optimizer sqlite query

Question

In my iOS application I have added the following code which will check if data is present in the database. If yes, then update that record, else insert a new record.

- (void)saveRoomsToDB:(NSArray *)room{
    NSString *dbpath = [[self documentDirectoryPath] stringByAppendingFormat:@"/"databaseName];

    database = [FMDatabase databaseWithPath:dbpath];

    if([database open]){
        [database beginTransaction];

        for (Room *roomData in room) {
            FMResultSet *result = [database executeQuery:@"SELECT RoomDesc FROM Room WHERE QMSRoomId = ?" withArgumentsInArray:@[@(roomData.RoomId)]];

            if ([result next]){
                [database executeUpdate:@"UPDATE Room SET QMSSubSectionId = ?,RoomDesc = ?,LastEditedDate = ?,RoomType = ?, isTrue = ?, CyclePerformed = ? WHERE QMSRoomId = ?" withArgumentsInArray:@[@(roomData.QMSSubSectionId), roomData.RoomDesc, roomData.LastEditDate, roomData.rt_description, @(roomData.isTrue), @(roomData.CyclePerformed), @(roomData.RoomId)]];
            } else{
                [database executeUpdate:@"INSERT INTO Room (QMSSubSectionId, QMSRoomId,RoomDesc, LastEditedDate, RoomNumber,RoomType, CyclePerformed,isTrue) VALUES (?, ?, ?, ?, ?, ?, ?, ?);" withArgumentsInArray:@[@(roomData.QMSSubSectionId), @(roomData.RoomId), roomData.RoomDesc, roomData.LastEditDate, roomData.RoomNumber, roomData.rt_description, @(roomData.CyclePerformed), @(roomData.isTrue)]];
            }
        }

        [database commit];
        [database close];
    }
}

I have printed the log for above query execution as,

2018-05-23 17:47:49.702 SterileTrakks[656:74128] no of records for Rooms : 7136
2018-05-23 17:48:07.153 SterileTrakks[656:74128] Insertion for Rooms finished`

Can I optimize this query insertion time? I think about applying indexing but is there any other way?

Instead of doing a `SELECT` and then choosing to do `INSERT` or `UPDATE` based on the result, why not use a single `INSERT OR REPLACE` statement? See https://www.sqlite.org/lang_insert.html But this will require that `QMSRoomId` is a primary key. — rmaddy, May 23 '18 at 14:33

battlmonstr · Accepted Answer · 2018-05-23T14:26:16.840

Indexing is going to improve SELECT time, but it slows down insertion. In general it is a good idea to have indexes on primary keys like QMSRoomId.

You can optimize the SELECT part by moving the SELECT expression outside of the loop, and instead of doing 7000 SELECT's do just one select in advance to get a set of existing rooms. You should not query RoomDesc there, but query SELECT QMSRoomId FROM Room, which might be faster. Then put all IDs into an NSSet or a sorted NSArray for fast search in the loop. Note that you don't need any indexes for such a query.

To optimize the INSERT loop you could do batch insertions instead of single record inserts (see Is it possible to insert multiple rows at a time in an SQLite database? ). Another optimization is to disable indexes while inserting a lot of stuff (and re-enabling again after).

Another thing that might help is to split UPDATEs. If you have just a few updates it is better to do them separately. If you have a lot of updates, it is actually better to load the complete table, drop it and reload from scratch (without updates).

You should measure each step of your algorithm to understand which part is the slowest.

A more general advice with this code that you should question yourself is do you really need to insert 7000 records at once? It might be fine if it is an action from the user (like trying to import a huge data file in UI), but then the long time is kind of expected. If it is for caching purposes you could keep that cache in memory and insert it into database asynchronously part by part so that it doesn't affect the UX. Also for caching I would consider not using the SQL database completely, especially read-only caching.

It helped. Only by replacing `SELECT RoomDesc FROM Room` to `SELECT QMSRoomId FROM Room` `2018-05-24 09:25:00.800 SterileTrakks[918:11014] no of records for Rooms : 7136 2018-05-24 09:25:03.017 SterileTrakks[918:11014] Insertion for Rooms finished` — User_1191, May 24 '18 at 05:01

score 0 · Answer 2 · answered May 24 '18 at 07:20

must be use OOPs Concept

FMResultSet *rs = [MyModel selectQuery:strquery];

            while ([rs next]) {

            }

////Store Responce DATA IN DaTABASE
 for (int i=0; i<(unsigned long)[responseObject count]; i++) {
                progVal++;
                [MyModel insertInto_papertype:[responseObject objectAtIndex:i] :tableName];
            }


///////////////INSERT DATA METHOD/////////////////////////
+(void)insertInto_papertype:(NSMutableDictionary *)mdict :(NSString *)tblnm
{
    NSLog(@"--%@",mdict);
    NSString *insertQuery=@"", *keyQuery = @"", *valueQuery=@"";
    NSString * numberReg = @"[0-9]";

    NSPredicate * numberCheck = [NSPredicate predicateWithFormat:@"SELF MATCHES %@", numberReg];

    if([mdict count] > 0) {
        insertQuery = [NSString stringWithFormat:@"Insert into %@ (",tblnm];

        for(id key in mdict) {
            NSString *repalcedString =  [mdict valueForKey:key];

            if (![numberCheck evaluateWithObject:repalcedString]){
                @try {
                    if ([repalcedString rangeOfString:@"\'"].location != NSNotFound) {
                        repalcedString =  [repalcedString stringByReplacingOccurrencesOfString:@"\'" withString:@"\''"];
                    }
                }
                @catch (NSException *exception) {
                    NSLog(@"%@", exception.reason);
                }
                @finally {

                }
            }
            if([keyQuery isEqualToString:@""]){
                keyQuery = [NSString stringWithFormat:@"%@,",key];

                valueQuery = [NSString stringWithFormat:@" \' %@ \' ,",repalcedString];

            } else {
                keyQuery = [NSString stringWithFormat:@"%@ %@,",keyQuery,key];
                valueQuery = [NSString stringWithFormat:@"%@ \' %@ \' ,",valueQuery,repalcedString];
            }
        }


        //// removing extra , at end;
        keyQuery = [keyQuery substringToIndex:keyQuery.length-(keyQuery.length>0)];
        valueQuery = [valueQuery substringToIndex:valueQuery.length-(valueQuery.length>0)];

        //// adding remaining ) at end
        keyQuery = [NSString stringWithFormat:@"%@ )",keyQuery];
        valueQuery = [NSString stringWithFormat:@"%@ )",valueQuery];

        //// joinng the key and values for insert
        insertQuery = [NSString stringWithFormat:@"%@ %@ values (%@",insertQuery,keyQuery,valueQuery];
        NSLog(@"URL --%@",insertQuery);
        [self UpdateQuery:insertQuery];
    }


}

If any query to put on comment box.

iOS-Objective C: How to optimizer sqlite query

2 Answers2