CODEWITHSUNDEEP
iphoneobjective-cdatabasexcodesqlite
Jon
Jon

Reputation: 4732

Is There Any Way To Speed Up SQLite Fetch?

Is There Any Way To Speed Up SQLite Fetch? I am fetching data from my SQLite database into an array which loads my tableView.

It is taking around 10-20 seconds to populate the array. Is there a quicker way to do this or something? Perhaps only load each cell at a time so atleast the user sees some cells while the rest are loading?

Here is the code in my ViewController: Code:

-(void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
    [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];
}

Here is the code from the actual fetch: Code:

-(NSMutableArray *) getICD9ProcedureCodes
{
    sqlite3 *database = CodesDatabase();
    NSMutableArray *icd9ProcedureCodes = [[[NSMutableArray alloc] init] autorelease];

    NSString *nsquery = [[NSString alloc] initWithFormat:@"SELECT code, title, description FROM icd9_procedure_codes"];
    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:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
            code.description = [NSString stringWithCString:(char *)sqlite3_column_text(statement, 2) encoding:NSUTF8StringEncoding];
            [icd9ProcedureCodes addObject:code];
            [code release];
        }
        sqlite3_finalize(statement);
    }
    return icd9ProcedureCodes;
}

Upvotes: 1

Views: 700

Answers (1)

EricS
EricS

Reputation: 9768

You should definitely only load those rows that you need. Use the SQL LIMIT and OFFSET options to load only what you need.

I don't know offhand if reading a single row at a time will be fast enough, but it's worth a try. Something like:

-(void)tableView:(UITableView *)tableView willDisplayCell:(UITableViewCell *)cell forRowAtIndexPath:(NSIndexPath *)indexPath
{
   NSString *queryString = [NSString stringWithFormat: @"SELECT code, title, description FROM icd9_procedure_codes ORDER BY ROWID LIMIT 1 OFFSET %d", indexPath.row];

   // perform your database SELECT operation here

   // set up your UITableViewCell with whatever results you want to display
   cell.textLabel.text = code.name;
}

You should also make sure that you reuse UITableViewCells in cellForRowAtIndexPath.

Upvotes: 2

Related Questions