I recently implemented an ordered, reflexive relationship in core data - similar to the many-to-many Friend example in the Core Data Programming Guide. In that example there is the main Person table, and a join entity called FriendInfo which also has a ranking attribute.
In my use case the join entity has a 'type' and I needed Core Data to efficiently select related entities by type and order. To explore the performance and see what core data actually does I created a small model and tried several approaches to fetching, using SQL debug logging to dump the queries. I ran a release build on my iPhone 3G device to capture the logging and aggregated logs into a spreadsheet to summarize run times.
Modelling ordered list data is pretty common so it seemed a good idea to put some investigative time into this to discover strengths and weaknesses and make better modelling choices.
Test Model and Sample Data
A Person's associates are modelled using the many-to-many join entity AssociateInfo. AssociateInfo.type is the type of associate - in my example, Friend, Foe or PartnerInCrime. AssociateInfo.rankingForType represents a 0-based index of an associate within that type. For example John could have 3 Friends and 4 Foes, with Fred being associated as 1st-ranked Friend and 4th-ranked PartnerInCrime.
My sample data is 100 Person entities, each having 10 Friends, 10 Foes and 10 PartnersInCrime. Therefore each Person has 30 associates creating a total of 3000 relationships in the datastore.
The action being investigated is fetching an ordered list of names for the 10 people who are Foes of a given Person.
Method 1 - Object graph navigation
This is the most basic approach without consideration given to performance. The code walks the object graph, letting CoreData decide when to fault in objects and performs sorting in the application layer.
-(void) listNames:(NSArray *)sortedAssociates { for (int i=0; i<[sortedAssociates count]; i++) { Person *associate = [sortedAssociates objectAtIndex:i]; NSLog(@"Person at [%i] is named [%@]", i, associate.firstName); } } -(void) action1 { Person *person = [self aPerson]; NSMutableArray *foes = [NSMutableArray arrayWithObjects:[NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], [NSNull null], nil]; for (AssociateInfo *info in [person.associates allObjects]) { if ([info.type intValue] == AssociateTypeFoe) { int foeRanking = [info.rankingForType intValue]; [foes replaceObjectAtIndex:foeRanking withObject:info.associate]; } } [self listNames:foes]; }
The code loops over all the AssociateInfo objects in the set, and places the associated person for each of type "Foe" at their corresponding list index. (This sorting uses the fact that foeRanking corresponds to index). The code walks the object graph faulting at "person.associates", "info.associate" and "associate.firstname". The following queries were performed by Core Data:
-- 1 query - selects PKs of all AssociateInfo objects. SELECT 0, t0.Z_PK FROM ZASSOCIATEINFO t0 WHERE t0.ZSOURCE = ? -- 30 queries - AssociateInfo is faulted in each time we read it's type. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTYPE, t0.ZRANKINGFORTYPE, t0.ZASSOCIATE, t0.ZSOURCE FROM ZASSOCIATEINFO t0 WHERE t0.Z_PK = ? -- 10 queries - Person is faulted in each time we read their name. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZLASTNAME, t0.ZFIRSTNAME FROM ZPERSON t0 WHERE t0.Z_PK = ?
- 41 queries, 0.98 seconds total spent fetching data
All fetches for AssociateInfo and Person return 1 row. And even worse, 20 of those rows weren't needed. But at least we have a baseline to compare against.
Method 2 - Fetch by type, order by
This approach uses a fetch request defined in the model combined with a sort. It fetches the ordered AssociateInfo objects back for the requested type, and in the application layer we traverse the "associate" reference to get our list of foes.
This fetch request is stored against the AssociateType entity as byTypeForPerson.
// Create the fetch request with a sort descriptor. -(NSFetchRequest *) associateInfoFRByType:(AssociateType)type forPerson:(Person *)source { NSFetchRequest *request = [mom fetchRequestFromTemplateWithName:@"byTypeForPerson" substitutionVariables:[NSDictionary dictionaryWithObjectsAndKeys:source, @"SOURCE", [NSNumber numberWithInt:type], @"TYPE", nil]]; NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"rankingForType" ascending:YES]; [request setSortDescriptors:[NSArray arrayWithObject:sortDescriptor]]; [sortDescriptor release]; return request; } // Navigate from the ordered AssociateInfo objects to the associate. -(void) action2 { Person *person = [self aPerson]; NSArray *foeAssociateInfos = [datastoreService orderedAssociateInfosByType:AssociateTypeFoe forPerson:person]; NSMutableArray *foes = [NSMutableArray arrayWithCapacity:10]; for (AssociateInfo *associateInfo in foeAssociateInfos) { [foes addObject:associateInfo.associate]; } [self listNames:foes]; }
Queries performed:
-- 1 query - select and order AssociateInfo objects. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTYPE, t0.ZRANKINGFORTYPE, t0.ZASSOCIATE, t0.ZSOURCE FROM ZASSOCIATEINFO t0 WHERE ( t0.ZSOURCE = ? AND t0.ZTYPE = ?) ORDER BY t0.ZRANKINGFORTYPE -- 10 queries - Person is faulted in each time we read their name. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZLASTNAME, t0.ZFIRSTNAME FROM ZPERSON t0 WHERE t0.Z_PK = ?
- 11 queries, 0.35 seconds total spent fetching data
Method 3 - Fetch AssociateInfo by type, order by, prefetch associate
This is the same as method 2 except we add a prefetch for the keypath to AssociateInfo.associate.
[request setRelationshipKeyPathsForPrefetching:[NSArray arrayWithObject:@"associate"]];
The prefetch tells CoreData to select the entities for the key path (or paths) provided so that when we eventually do navigate to them, they will already be faulted in.
-- 1 query - select and order AssociateInfo objects. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTYPE, t0.ZRANKINGFORTYPE, t0.ZASSOCIATE, t0.ZSOURCE FROM ZASSOCIATEINFO t0 WHERE ( t0.ZSOURCE = ? AND t0.ZTYPE = ?) ORDER BY t0.ZRANKINGFORTYPE -- 1 query - prefetches associated persons. SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZLASTNAME, t0.ZFIRSTNAME FROM ZPERSON t0 WHERE t0.Z_PK IN (?,?,?,?,?,?,?,?,?,?)
- 2 queries, 0.11 seconds total spent fetching data
The prefetch works exactly as advertised - better than I expected in fact.
Method 4 - Fetch Foes directly
Can we make a fetch request which does all the work for us in a single select?
It doesn't seem possible to create a single fetch request to fetch the ordered related Foes in one select. Indeed the Core Data guide alludes to the fact that this level of complex data fetching isn't what Core Data is designed to do. A fetch request on Person using the predicate "ANY associatedFrom.source == $SOURCE" fetches associates without regard for their type, so in code we would have to do further filtering and sorting work. I tried that and it took 0.17 seconds to fetch all 30 related persons (i.e. all 3 types - Friend, Foe and PartnerInCrime) or about 50% longer than the previous two fetches combined.
Summary
With this design pattern the best choice depends on how the application ultimately uses the data (apologies for the politicians answer!). In a situation where only one of your types of related entities is needed at once, I would go with Method 3 for it's conciseness and efficiency. But there may be a reason to select either one entity at a time (the objects could be large enough to warrant reducing the memory footprint this way) - or to select everything at once.
The other approach is to redesign the datamodel. In my case the datasets aren't so large that this is needed - but the investigation did cause me to revisit the rationale of the type column. One way could be creating an association table for every type of associated person.
It was interesting exploring Core Data this way and will help me make better informed design decisions, I hope this article will be able to bring that to others too.
