CoreData performance: predicate on more to-many relationships

Question

Using SQL data store, I have a model like this:

Category <-->> Item

Collection <-->> Item

Brand <-->> Item

Every left-side entity has a to-many relationship named items to the Item entity. Each one is the inverse of to-one Item relationships category, collection and brand respectively.

I want to fetch all Brand objects with at least one Item in relation with a particular Collection AND a Category. In natural language, I want all the brands of items with particular category and a particular collection.

Given the objects Category *myCategory and Collection *myCollection, I use to build a predicate on the Brand entity in this way:

NSPredicate *myPredicate = [NSPredicate predicateWithFormat:@"(ANY items.category == %@) AND (ANY items.collection == %@)",myCategory,myCollection]; 

And I get the correct results. The problem is performance. Too slow! And I simplified the question for three levels! Let's consider four levels in a iOS multilevel catalog: the app shows categories available, user select a category, the app shows collections in that category, user select a collection, the app shows brands in that collection (and the category already selected), user select a brand, the app shows for example materials in that brand,collection and category...

To be honest, i use NSCompoundPredicate to put in AND subpredicates at every user selection, building dynamically the final predicate. But this doesn't matter for the purpose of the question.

There are more than 30,000 (30k) objects of Item entity in the sql data store, and using AND between items relationship is very slow (I noticed 7-10 seconds of lag to show brands in the example above).

I would try to:

-remove items relationships from Category, Collection and Brand entites

-on the Item entity, replace category, collection and brand relationships with fetched properties on attributes like "category_id", "collection_id" and "brand_id".

-use keypath or predicates to fetch the brands without using to-many relationships.

Before I start to destroy and remake the whole work of the last 6 months, do you have any suggestion? :)

Thank you!

Answer 1

Reverse the logic of what you are looking for.

You are not looking for a Brand with a certain category and a certain collection; you are looking for an Item entity that has a category of X and a collection of Y. Once you have the Item you can ask it for its brand:

id myCategory = ...;
id myCollection = ...;
NSFetchRequest *request = [NSFetchRequest fetchRequestWithEntityName:@"Item"];
[request setPredicate:[NSPredicate predicateWithFormat:@"category == %@ && collection == %@", myCategory, myCollection]];

NSError *error = nil;
NSArray *itemArray = [moc executeFetchRequest:request error:&error];
NSAssert2(!error || itemArray, @"Error fetching items: %@\n%@", [error localizedDescription], [error userInfo]);

NSArray *brands = [itemArray valueForKey:@"brand"];
return brands;

Update

Item is an entity on the other end of a relationship just as you have it in your question. -valueForKey: will go through the array of items and get the Brand entity on the other end of the relationship and give you back an array of the Brand. You do not need to add anything, not even a property since it will be accessed via KVC and will "just work".

This is going to give you the most efficient retrieval based on your requirement. Since Item is on the many side of each of those relationships it will hold the foreign keys and therefore the SQL will not need to cross table boundaries and therefore should be very fast.

The only other option and I do not recommend it but you can play with the sets coming from Category and Collection and get a union from a mutable set. Every time I have experimented with that direction it was slower then going to the database. That kind of logic is best done at the database level in my opinion.