Fixing predicated NSFetchedResultsController/NSFetchRequest performance with SQLite backend?

Question

I have a series of NSFetchedResultsControllers powering some table views, and their performance on device was abysmal, on the order of seconds. Since it all runs on main thread, it's blocking my app at startup, which is not great.

I investigated and turns out the predicate is the problem:

NSPredicate *somePredicate = [NSPredicate predicateWithFormat:@"ANY somethings == %@", something];
[fetchRequest setPredicate:somePredicate];

I.e the fetch entity, call it "things", has a many-to-many relation with entity "something". This predicate is a filter that limits the results to only things that have a relation with a particular "something".

When I removed the predicate for testing, fetch time (the initial performFetch: call) dropped (for some extreme cases) from 4 seconds to around 100ms or less, which is acceptable. I am troubled by this, though, as it negates a lot of the benefit I was hoping to gain with Core Data and NSFRC, which otherwise seems like a powerful tool.

So, my question is, how can I optimize this performance? Am I using the predicate wrong? Should I modify the model/schema somehow? And what other ways there are to fix this? Is this kind of degraded performance to be expected? (There are on the order of hundreds of <1KB objects.)

EDIT WITH DETAILS:

Here's the code:

[fetchRequest setFetchLimit:200];
NSLog(@"before fetch");
BOOL success = [frc performFetch:&error];
if (!success) {
    NSLog(@"Fetch request error: %@", error);
}
NSLog(@"after fetch");

Updated logs (previously, I had some application inefficiencies degrading the performance here. These are the updated logs that should be as close to optimal as you can get under my current environment):

2010-02-05 12:45:22.138 Special Ppl[429:207] before fetch
2010-02-05 12:45:22.144 Special Ppl[429:207] CoreData: sql: SELECT DISTINCT 0, t0.Z_PK, t0.Z_OPT, <model fields> FROM ZTHING t0 LEFT OUTER JOIN Z_1THINGS t1 ON t0.Z_PK = t1.Z_2THINGS WHERE  t1.Z_1SOMETHINGS = ? ORDER BY t0.ZID DESC LIMIT 200
2010-02-05 12:45:22.663 Special Ppl[429:207] CoreData: annotation: sql connection fetch time: 0.5094s
2010-02-05 12:45:22.668 Special Ppl[429:207] CoreData: annotation: total fetch execution time: 0.5240s for 198 rows.
2010-02-05 12:45:22.706 Special Ppl[429:207] after fetch

If I do the same fetch without predicate (by commenting out the two lines in the beginning of the question):

2010-02-05 12:44:10.398 Special Ppl[414:207] before fetch
2010-02-05 12:44:10.405 Special Ppl[414:207] CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, <model fields> FROM ZTHING t0 ORDER BY t0.ZID DESC LIMIT 200
2010-02-05 12:44:10.426 Special Ppl[414:207] CoreData: annotation: sql connection fetch time: 0.0125s
2010-02-05 12:44:10.431 Special Ppl[414:207] CoreData: annotation: total fetch execution time: 0.0262s for 200 rows.
2010-02-05 12:44:10.457 Special Ppl[414:207] after fetch

20-fold difference in times. 500ms is not that great, and there does not seem to be a way to do it in background thread or otherwise optimize that I can think of. (Apart from going to a binary store where this becomes a non-issue, so I might do that. Binary store performance is consistently ~100ms for the above 200-object predicated query.)

(I nested another question here previously, which I now moved away).

Answer 1

This is an on-going problem for many of us trying to use Core Data on the iPhone for larger databases or more complex schemas. My profile has links to a couple different SO questions related to this -- in the context of full text searching.

Not all apps are dog slow just by applying a predicate to a to-many relationship, so there's more to it. See Apple's performance document. Set -com.apple.CoreData.SQLDebug 1.

Check that there's an index, if appropriate. Make sure there are no sql functions (such as format/type conversions) being called that keep sqlite from efficiently joining or using an index.

If you have a lot of rows and there are many attributes in your entity there may be memory issues. Consider splitting a single entity into a smaller one with a few attributes used for your search and pull in the rest when needed.

If your predicate is more complex than the to-many relationship part, then you might experiment with searching in two steps. E.g. the SQL might be inefficiently joining and then filtering instead of filtering and then joining. But doing this would break NSFetchedResultsController. Still, it might shed some light on the trouble.

I would not do something like pack ObjectIDs into an attribute to make your own to-many foreign key reference. Sounds like a horrible hack surely to dissuade you from using Core Data seriously... yet the advantages of Core Data are numerous.

Experiment with removing or adding the reverse relationship. I found in one case that my query ran much faster without the reverse relationship. The downside was that removing the reverse relationship bloated my database.

Let us know what you find.

Answer 2

The practical solution to this for me was to just switch to Core Data binary store that has much better performance. I did not investigate whether I can improve Core Data SQLite performance with my current app situation.

Answer 3

A binary story will load the entire data set into memory which will cause a memory issue on some devices. Instead you should re-write your predicate.

Since your relationships are double-sided (right?) you can come at it from the other side and you probably don't need a NSFetchedResultsController at all for this situation. Presume you have:

MyWidgetEntity <--->> SomethingEntity

Since you already have a reference to an instance of SomethingEntity you just ask it for it's widget via:

id widget = [mySomethingInstance valueForKey:@"widget"];

In the situation where you have:

MyWidgetEntity <<-->> SomethingEntity

You can get access to all of the associated widgets via:

NSSet *widgets = [mySomethingInstance valueForKey:@"widgets"];

No NSFetchedResultsController is needed because you can turn this set into an array and sort it.