CODEWITHSUNDEEP
ioscore-datanspredicate
SomeGuy
SomeGuy

Reputation: 9690

Core Data ANY ANY multi INNER JOIN

I've got a CoreData model that has a big list of searchable 'recipe' objects. For search efficiency I have a referenced model called 'keywords' that simply has a 'term' property (indexed). The term is a single word, processed and normalized from the 'recipe's name.

So for example, if a recipe was called Crème Brûlée, it would have two keywords of 'creme' and 'brulee', then I also normalize the user's search so they can use accented words to find non accented words, and vice versa.

When the user searches I take the string, normalize then split by whitespaces. Search recipe results must have keywords starting with all of the split search components.

I create a dynamic predicate which grows based on the number of words the user is searching for.

If you were searching for 'crem' the predicate would become something like:

[NSPredicate predicateWithFormat:@"ANY keywords.term BEGINSWITH %@", @"crem"]

If you searched for 'crem bru':

[NSPredicate predicateWithFormat:@"ANY keywords.term BEGINSWITH %@ AND ANY keywords.term BEGINSWITH %@", @"crem", @"bru"]

Now, the problem I'm facing.

With a single word, this is blazing fast. I'm able to run it on a 3GS with no noticeable lag with live search suggestions. The problem comes when you add subsequent words to your search. I've looked at the SQL output and I think the problem is that CoreData is doing an INNER JOIN per "ANY keywords.term BEGINSWITH", but if I rewrite the query myself I could simply do it with a single INNER JOIN.

Running on a 3GS:

Single word (0.0262 seconds):

CoreData: sql: SELECT DISTINCT t0.Z_ENT, t0.Z_PK, t0.ZID, t0.ZNAME FROM ZCDRECIPE t0 JOIN Z_4RECIPES t1 ON t0.Z_PK = t1.Z_5RECIPES JOIN ZCDKEYWORD t2 ON t1.Z_4KEYWORDS = t2.Z_PK WHERE NSCoreDataStringSearch( t2.ZTERM, ?, 8, 0) ORDER BY t0.ZNAME

CoreData: annotation: sql connection fetch time: 0.0262s

Multiword (0.2996 seconds):

CoreData: sql: SELECT DISTINCT t0.Z_ENT, t0.Z_PK, t0.ZID, t0.ZNAME FROM ZCDRECIPE t0 JOIN Z_4RECIPES t1 ON t0.Z_PK = t1.Z_5RECIPES JOIN ZCDKEYWORD t2 ON t1.Z_4KEYWORDS = t2.Z_PK JOIN Z_4RECIPES t3 ON t0.Z_PK = t3.Z_5RECIPES JOIN ZCDKEYWORD t4 ON t3.Z_4KEYWORDS = t4.Z_PK WHERE ( NSCoreDataStringSearch( t2.ZTERM, ?, 8, 0) AND NSCoreDataStringSearch( t4.ZTERM, ?, 8, 0)) ORDER BY t0.ZNAME

CoreData: annotation: sql connection fetch time: 0.2996s

You can see even though it's looking at the same table, CoreData is INNER JOINing with ZCDKEYWORD more than just once.

In my predicate is there a way to make it only load the keywords table once into the join?

Thanks

Upvotes: 1

Views: 1499

Answers (1)

Mundi
Mundi

Reputation: 80265

Try using the compound predicate syntax. I had very good results with this setup in a similar situation. 

for (NSString *s in words) {
   finalPredicate = [NSCompoundPredicate andPredicateWithSubpredicates:
     @[finalPredicate, [NSPredicate predicateWithFormat:
         @"ANY keywords.term BEGINSWITH %@", s]]];
}

Edit:

After looking up my old solution, I realised that you are maybe doing the lookup the wrong way round. It makes sense. You have more than one keywords lookup, so there will be two joins.

It might be better to derive the data you want from the other entity in the relationship. The predicate should refer directly to the keyword entity:

[NSPredicate predicateWithFormat:@"term BEGINSWITH %@", s];

And deriving the end result should be just one in-memory lookup: 

searchResults = [searchResults filteredArrayUsingPredicate:
    [NSPredicate predicateWithFormat:@"ANY keywords IN %@", fetchedKeywords]];

Upvotes: 2

Related Questions