CODEWITHSUNDEEP
iosobjective-ccore-data
andercruzbr
andercruzbr

Reputation: 454

Subquery on iOS CoreData

I have a CoreData performance problem in my iOS App.

I need execute a query with a subquery clause.

Lets explain the case.

I have a Entity called quota that has groupCode and date attributes. (There are more attributes but it isn't import for now).

For execute my search, it's require two step:

Example:

NSArray *quotasGroupProduct = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode=%@", quotaGroupProductParam]];
for (Quota *quota in quotasGroupProduct) {
    NSArray *quotasGroup = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode = %@ AND date = %@", quotaGroupParam, quota.date]];
    if([quotasGroup count] > 0 && [quotasRepresentative count] > 0) {
        [quotaDatesArray addObject:quota.date];
    }
}

I'd like change this code to execute just one fetchRequest.

Below a SQL query that do that:

select * from zquota q where zgroupCode = 58 and exists (select 1 from zquota where ZgroupCode = 6816 and zdate = q.zdate);

Anyone could help me?

Thanks!

Upvotes: 1

Views: 273

Answers (1)

pbasdf
pbasdf

Reputation: 21536

Currently, each quota in the quotasGroupProduct array results in an additional fetch, which in itself is expensive, but also building and parsing the associated predicate is costly. You can avoid this by changing your predicate to perform these fetches all in one go. Note that to reproduce the same behaviour as your current code, it is easier to perform the fetches in reverse order:

  1. Fetch all Quotas with groupCode = quotaGroupParam.
  2. Extract the dates into an array.
  3. Fetch all Quotas with groupCode = quotaGroupProductParam and with date IN the above array of dates.
  4. Extract the dates into your final array.

(The reason for this relates to duplicates. If there are several Quotas from the first fetch, all with the same date, the intermediate array will contain duplicates of the same date. But the IN clause in the second fetch will ensure only one is included in the final array.)

NSArray *quotasGroup = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode=%@", quotaGroupParam]];
// extract the dates...
NSArray *datesOfInterest = [quotasGroup valueForKey:@"date"];
// execute the second fetch
NSArray *quotasGroupProduct = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode = %@ AND date IN %@", quotaGroupProductParam, datesOfInterest]];
// extract the dates again
NSArray *finalDatesArray = [quotasGroupProduct valueForKey:@"date"];
if ([quotasRepresentative count] > 0) {
        [quotaDatesArray addObjectsFromArray:finalDatesArray];
}

So, no matter how many items match your predicates, only two fetches are required. I hope this will give you the required performance boost.

Upvotes: 1

Related Questions