Memory optimization for sqlite requests – help needed

| | August 8, 2015

I’m using two methods to r/w sqlite table:

+ (NSString *) getWeatherData:(int)rowID:(int)columnID {
    NSString *savedWeatherData = [[NSString alloc] init];
    if (sqlite3_open([[DBController getDBPath] UTF8String], &database) == SQLITE_OK) {

        const char *sql = "select * from TABLE where id=?";
        sqlite3_stmt *selectstmt;
        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            sqlite3_bind_int(selectstmt, 1, rowID);

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {

                NSInteger primaryKey = sqlite3_column_int(selectstmt, 0); 
                Weather *weatherObj = [[Weather alloc] initWithPrimaryKey:primaryKey];

                weatherObj.weatherData = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, columnID)];
                savedWeatherData = weatherObj.weatherData;
                [weatherObj release];
            }

        }   
    }
    return [savedWeatherData autorelease];
}

And to save some data:

+ (void) saveDataToDataBase:(NSString *)columnToUpdate:(int)rowID:(NSString *)value {

    if (sqlite3_open([[DBController getDBPath] UTF8String], &database) == SQLITE_OK) {

        updateStmt = nil;

        NSString *sqlString = [[@"update TABLE set " stringByAppendingString:columnToUpdate] stringByAppendingString:@"=? where id=?"];

        const char *sql = [sqlString UTF8String];

        if(sqlite3_prepare_v2(database, sql, -1, &updateStmt, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
        } else { // select statement ok

            sqlite3_bind_text(updateStmt, 1, [value UTF8String], -1, SQLITE_TRANSIENT); // replace first ? with value
            sqlite3_bind_int(updateStmt, 2, rowID);                                     // replace second ? with rowID

            if(SQLITE_DONE != sqlite3_step(updateStmt)) {
                NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));
            } else {
               // NSLog(@"Update completed !!!");  
            }

            sqlite3_reset(updateStmt);
        }

        sqlite3_finalize(updateStmt);

    }
    else
    sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.

}

With the Instruments, I see pretty big memory consumption for sqlite.
Info: during app startup, cca 100 different kind of data is stored in DB – for each data, this saveDataToDataBase method is called. (In case of no internet connection – getWeatherData would be used – and again cca 100 different kind of data would be read)

Please, can you instruct me – is it possible to optimize memory consumption.

Thanks a lot!

Kind regards!

One Response to “Memory optimization for sqlite requests – help needed”

  1. You need to finalize that selectstmt. Also you should not open and close the database on every call. Keep a reference to the database instead and only close it when you really don’t need it anymore. (The first method doesn’t close the database every time either.)

    Also, you can put this in after you open the database to set the cache size:

    // Modify cache size so we don't overload memory. 50 * 1.5kb
    if (sqlite3_exec(database, "PRAGMA CACHE_SIZE=50;", NULL, NULL, NULL) !
    = SQLITE_OK) {
    NSAssert1(0, @"Error: failed to set cache size with message '%s'.",
    sqlite3_errmsg(database));
    }
    

Leave a Reply