fetch request performance issues

I have some serious performance issues when querying a sqlite database with approx. 25k rows. What I want to do is the following: as the user types something into a textfield I want to give him autocompletition suggestions in a tableview which is the inputAccessoryView of the keyboard. Each time he enters a new character, 4 new queries go off to search for appropriate suggestions. I do this in a seperate thread using GCD and blocks. However the performance is too low. Here's the code for one query:

- (void) queryDatabase:(NSString *) searchString
{   
[self.fetchedResults removeAllObjects];

dispatch_queue_t fetchQueue = dispatch_queue_create("Fetch Queue", NULL);

dispatch_async(fetchQueue,^{
    NSError *error = nil;
    NSManagedObjectContext *context = [[NSManagedObjectContext alloc] init];
    context.undoManager = nil;
    [context setPersistentStoreCoordinator: self.persistentStoreCoordinator];

    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"reducedTownName like %@", [searchString stringByAppendingString:@"*"]];
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    request.entity = [NSEntityDescription entityForName:@"Station" inManagedObjectContext:context];
    request.fetchLimit = 20;
    [request setIncludesPropertyValues:NO];
    request.predicate = predicate;
    request.resultType = NSManagedObjectIDResultType;

    NSArray *results = [context executeFetchRequest:request error:&error];
    NSEnumerator *e = [results objectEnumerator];
    NSManagedObjectID *objectId = nil;

    while (objectId = [e nextObject]) 
    {
        Station *station = (Station *) [self.managedObjectContext objectWithID:objectId];
        if ( ![self.fetchedResults containsObject:station])
            [self.fetchedResults addObject:station];
    }       

    dispatch_async(dispatch_get_main_queue(),^{
        [self.tableView reloadData];
    });
    [开发者_C百科request release];
    [context release];
});
    //do 3 more queries similar to the first (only predicate changes)
    dispatch_release(fetchQueue);
}

I use an NSArray (fetchedResults) to hold the returned Entities and update the tableView with data from this Array.

Does someone see any performance killer in this code, or has some other advice for me?

I think I might have spotted it.

You do your query to get the entities and put it into the results array. That's query number 1 and it's optimized ok (assuming there are no joins etc but it doesn't look like there are)

Then, you go through all the results to remove duplicates that are already in your results. This means that you execute a new query to get each Station NSManagedObject. If you want to avoid duplicates you will have to do it without calling objectWithID because that will et the object from CoreData.

Worst case, to get 20 stations results, you need 21 queries. Not good.

As sqlite3 is single threaded, why don't you just make a large predicate instead of 4 smaller ones - then you can remove your duplicates in the predicate and won't need to go through the array until it's time to draw that station's table view cell.

I've had this problem before with lookups and had to solve it by making a seperate table in the database. In your case this table (called station_search) would only contain the station name (which would be indexed of course) and a stationId. Then I do searches on this table (which would be fast because it's not got lots of data to search through).

Once I've got my results I just use the stationId to get the Station from the main stations table only when I need it i.e. I'm drawing the cell in a table.

I can also use a NSFetchedResultsController to batch my results together.

This has turned into a bit of a ramble - if you have any questions just ask!

Hope it helps,

Sam

Ok I finally got it. Replaced the LIKE query with

reducedTownName >= %@ AND reducedTownName <= %@

where the first argument is the search term and the second argument is again the search term, except the last letter is replaced by the following one, eg.

reducedTownName >= 'oak' AND reducedTownName <= 'oal'

This is extremely fast and gives me the results I wanted. I also could move the queries in the main thread, which gave me an extra boost in performance.

Greetings

Sam