iPhone Sqlite Performance Problem

Question

Hey guys, here is the low down.

I have one table, consisting of a primary key(col1), text(col2), and text(col3). Basically a map. This table contains about 200k rows. It basically takes me about 1.x seconds to retrieve a single row (this is all I want). I'm basically using select * from table where col2 = 'some value'.

I've tried creating an index for all three columns, each column individually, and col2 and col3, but this really hasn't improved my situation at all.

I'm wondering, is this normal ? I haven't come across any posts of people complaing about slow sqlite performance for big tables, so I'm wondering what I'm doing wrong.

Any help would be greatly appreciated.

Answer 1

Hi Paul,

I would say, that this is absolutely not typically.

Even when you have a large table, an access via an index should be rather fast.

What could you do: Create only one index on col2 (that is the one and only you need for this select!).

Than use "EXPLAIN SELECT ...." to get the information, what SQLite makes out of it. The result is not easy to read, but with some experience it is possible to see if the index is used. You could also post the result here.

Answer 2

What is the actual code your using to retrieve the data from the database?

Answer 3

I've made this class a singleton (called SQLAdapter), and this it contains two methods in here, one to copy the database if its needed, and the other to exec my sql code:

Here is the sql code method, this was the first time I coded in Obj-C, so just ignore the string append methods, I'm changing this as we speak...

- (NSString *)getMapping:(NSString *)test{
    //Our return string
    NSString *res = test;

    // Setup the database object
    sqlite3 *database;

     NSString *sqlStmnt;
     if (direction) sqlStmnt = @"select * from table where col1 = '";
     else sqlStmnt = @"select * from table where col2 = '";

     NSString *tStmt = [sqlStmnt stringByAppendingString:test];
     NSString *sqlState = [tStmt stringByAppendingString:@"'"];
     const char * sqlStatement = [sqlState UTF8String];

    // Open the database from the users filessytem
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
     // Setup the SQL Statement and compile it for faster access
     sqlite3_stmt *compiledStatement;
     //execute the statement
     if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) != SQLITE_OK) {
      NSAssert1(0, @"Error: during prepare '%s'.", sqlite3_errmsg(database));
     }
     //bind our translation into the sql select statment 
     sqlite3_bind_text( compiledStatement, 1 , [word UTF8String], -1, SQLITE_TRANSIENT);
     if(sqlite3_step(compiledStatement) == SQLITE_ROW) { //if execution is successful i.e. we get a match
      //lets return the desired language translation
      res = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, (direction) ? 2 : 1)];
     }
     sqlite3_finalize(compiledStatement); //Release the compiled statement from memory
    }
    sqlite3_close(database); //lets return the translation
    return res;
}

Pretty much exactly the same way that the SQLiteBooks project does it if I'm not mistaken...

Answer 4

I did a trace with Shark, here are the results: (nicely formatted) http://dpaste.com/71465/

It seems that I am spending most of my time in

92.7% 92.7% libSystem.B.dylib mach_msg_trap

I have no clue what this means, so if someone could point me in the right direction many thanks :)

Answer 5

I solved the problem. It was that when I created a new sqlite database file, and added it to the project, xcode didn't properly recompile itself, it was still using the old file. I had to remove the old database from the project, remove the compiled version on the computer, clean the project, then compile it and make sure that it was crashing since the database was missing. Then again remove the compiled files, clean it, and re-add the new sqlite database.

This is why even after I created the index there was no performance improvement whatsoever....

Strange, would this be considered a bug with Xcode ?