Core Data SQLite query exponentially slower when subquery predicate contains only one entity

Question

I am using a subquery predicate to perform a fetch on a core data managed object context to fetch entities to put on a map. The entities are filtered by attributes on a related entity, so a subquery is used. The predicate on the subquery varies depending on the user selected filter criteria. I'm finding that one of the simplest of these subquery predicates is causing the fetch to take exponentially longer than it should.

My simplified object graph looks like this:

Entity  <-->>  RelatedEntity  <<-->  FilterRelationship
-latitude
-longitude

The predicate on the NSFetchRequest looks like this:

(latitude > #.# AND latitude < #.# AND longitude > #.# AND longitude < #.#) AND SUBQUERY(relatedEntities, $relatedEntity, $relatedEntity.filterRelationship IN { JUST_ONE_HERE }).@count > 0

JUST_ONE_HERE represents when there is only a single entity in the set in this portion of the subquery predicate, this is where the query takes exponentially longer. If I have more than one entity in this set, the query executes normally.

Using the launch argument -com.apple.CoreData.SQLDebug 1, I logged the following:

CoreData: sql: SELECT t0.Z_ENT, t0.Z_PK, t0.Z_OPT, ... FROM ZENTITY t0 WHERE (( t0.ZLATITUDE > ? AND t0.ZLATITUDE < ? AND t0.ZLONGITUDE > ? AND t0.ZLONGITUDE < ?) AND (SELECT COUNT(t1.Z_PK) FROM ZRELATEDENTITY t1 WHERE (t0.Z_PK = t1.ZENTITY AND ( t1.ZFILTERRELATIONSHIP IN (?) )) ) > ?) ORDER BY ... LIMIT 50

CoreData: annotation: sql connection fetch time: 903.6430s

CoreData: annotation: total fetch execution time: 903.6458s for 50 rows.

and

CoreData: sql: SELECT t0.Z_ENT, t0.Z_PK, t0.Z_OPT, ... FROM ZENTITY t0 WHERE (( t0.ZLATITUDE > ? AND t0.ZLATITUDE < ? AND t0.ZLONGITUDE > ? AND t0.ZLONGITUDE < ?) AND (SELECT COUNT(t1.Z_PK) FROM ZRELATEDENTITY t1 WHERE (t0.Z_PK = t1.ZENTITY AND ( t1.ZFILTERRELATIONSHIP IN (?,?) )) ) > ?) ORDER BY ... LIMIT 50

CoreData: annotation: sql connection fetch time: 1.7181s

CoreData: annotation: total fetch execution time: 1.7216s for 50 rows.

I removed the unimportant parts with ... and renamed the entities to be clear what they are. But as you can see, the query is fetching entities of type Entity based on their longitude and latitude on the map as well as a subquery based on a related entity RelatedEntity and a predicate matching a relationship on these related entities to a specified FilterRelationship entity.

In the simple case of only having one FilterRelationship entity in the set in the subquery predicate, the fetch takes ~15 minutes.

The only difference between the two above queries is the second one has two FilterRelationship entities in the set in the subquery predicate. The second example completed in under 2 seconds.

The database contains ~22,000 Entity, ~5,000 RelatedEntity, and 3 FilterRelationship entities.

I also tried this same predicate except for using an equality predicate in place of the IN a set of one object.

(latitude > #.# AND latitude < #.# AND longitude > #.# AND longitude < #.#) AND SUBQUERY(relatedEntities, $relatedEntity, $relatedEntity.filterRelationship == JUST_ONE_HERE).@count > 0

This predicate had the same slow fetch result.

The smaller the set of Entitys that are searched, the faster the subquery predicate fetch executes. So by zooming in on a small area of the map and having the latitude/longitude predicate filter out the Entitys being searched, the fetch executes faster, despite the slowness of a single FilterRelationship. The ~15 minute numbers were measure when zoomed all the way out on the map, filtering on all the Entitys in the database.

EDIT:

I updated the object graph to reflect that the first relationship, Entity to RelatedEntity is a one to many relationship. The second relationship, RelatedEntity to FilterRelationship is a many to one relationship.

Jeff Lockhart Jeff Lockhart · Accepted Answer · 2014-10-25T05:02:18

After narrowing down the specific portion of the predicate that was causing these slow fetches in my app, I created a workaround by always ensuring there were at least two entities in my FilterRelationship set in my predicate. In order to avoid matching anything with this extra FilterRelationship I tried adding NSNull to the set. Sure enough, this did the trick!

If the user selected search criteria only dictates one FilterRelationship in the predicate, I simply add [NSNull null] to the set in the subquery predicate and the fetch executes in seconds rather than dozens of minutes.

I still have no idea why this is an issue. It would seem to be a bug in either Core Data or SQLite.

Core Data SQLite query exponentially slower when subquery predicate contains only one entity

1 Answers