Need To Optimize SQLite Loading Into TableView

I have an SQLite Database with 5000 rows that is loaded into a tableview.

It takes around 5-10 sec (which is extremely long on the iPhone) to load the table. Is there a way I can load the cells in smaller batches or something else to speed this up? Also, when I go back up in hierarchy then revisit this view, it has to reload all over again. Can I cache this out instead?

- (void)viewDidLoad
{
    [super viewDidLoad];
    [NSThread detachNewThreadSelector:@selector(myThread:) toTarget:self withObject:nil]; 
}

-(void) myThread:(id) obj {
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
    NSMutableArray *array = [[Database sharedDatabase] getICD9DiseaseCodes];

    [self performSelectorOnMainThread:@selector(dataDoneLoading:) withObject:array waitUntilDone:NO];
    [pool release];    
}

-(void) dataDoneLoading:(id) obj {
    NSMutableArray *array = (NSMutableArray *) obj;
    self.codeAray = array;
    [self.myTableView reloadData];
    [self dismissHUD];
    NSLog(@"done");    
}

In my database class:

-(NSMutableArray *) getICD9DiseaseCodes
{
    sqlite3 *database = CodesDatabase();

    NSMutableArray *icd9DiseaseCodes = [[[NSMutableArray alloc] init] autorelease];

    NSString *nsquery = [[NSString alloc] initWithFormat:@"SELECT code, title, description FROM icd9_disease_codes ORDER BY code"];
    const char *query = [nsquery UTF8String];
    [nsquery release];

    sqlite3_stmt *statement;
    int prepareCode = (sqlite3_prepare_v2( database, query, -1, &statement, NULL));
    if(prepareCode == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            ICD9DiseaseCodes *code = [[ICD9DiseaseCodes alloc] init];
            code.code = [NSString stringWithCString:(char *)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];
            code.name = [NSString stringWithCString:(ch开发者_如何学Goar *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
            code.description = [NSString stringWithCString:(char *)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
            [icd9DiseaseCodes addObject:code];
            //            NSLog(@"Code: %@ Short Description: %@ Long Description: %@", hcpcsCode.code, hcpcsCode.name, hcpcsCode.description);
            [code release];
        }
        sqlite3_finalize(statement);
    }
    return icd9DiseaseCodes;
}

My first question is why do you use SQLite directly, and not Core Data? Core Data will do fast partial fetches from the data store for you, for free with no extra code on your part.

If you want to do the same thing manually with SQLite directly, then you are in for some heavy lifting code. You will need to use LIMIT and OFFSET in your SQL queries to fetch partial data, and extend your API to fetch only smaller sub-sets at a time.

Have you considered or looked into CoreData? CoreData has a lot of optimization for large recordsets.

Thanks. James