Performance issue in iOS-5 for Sqlite

Question

I am facing one problem while using sqlite in iOS 5. I am fetching records from two tables: one in Recipe & other in Ingredients from one Menu.db

From Recipe table I get all record and one recipeid on that basis I fetch records from ingredients table. It takes no time to fetch record when run on iOS 4.2 but when I run on iOS 5 it takes time to fetch the records. See the following code:

NSString *query = [NSString stringWithFormat:@"select id from Recipes"];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, [query UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
    while(sqlite3_step(selectstmt) == SQLITE_ROW) {
        rcp.recipeID = sqlite3_column_int(selectstmt, 0);
        NSString *sql = [NSString stringWithFormat:@"select Name from Ingredients where recipeId = %d",rcp.recipeID];
        sqlite3_stmt *stmt2;
        if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt2, NULL) == SQLITE_OK) {
            while(sqlite3_step(stmt2) == SQLITE_ROW) {}
        }
    }
}

Why is this issue coming in iOS 5.0, the same code runs fine on iOS 4.0, 4.2?

I know, code I have written is right,I want to know the exact reason behind this Performance issue in iOS 5.0 for Sqlite bcoz my app is totally build around database.

Answer 1

Try with using two different functions After you finish with complete execution of your first query, start with second query.

For example :-

NSString *query = [NSString stringWithFormat:@"select id from Recipes"];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, [query UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
    while(sqlite3_step(selectstmt) == SQLITE_ROW) {
        rcp.recipeID = sqlite3_column_int(selectstmt, 0);
    }
}

and then call

 NSString *sql = [NSString stringWithFormat:@"select Name from Ingredients where recipeId = %d",rcp.recipeID];
            sqlite3_stmt *stmt2;
            if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt2, NULL) == SQLITE_OK) {
                while(sqlite3_step(stmt2) == SQLITE_ROW) {}

Hope this helps to solve your issue.

Answer 2

I think you linked against libsqlite3.dylib. You should link the libsqlite3.0.dylib library instead.