0

I have the following Coredata model: Core data Model

Warengruppe contains the name of a specific product and WarengruppeVK is a disposal of a Warengruppe, it contains the selling price of a product and some other numeric values. PbsRow describes how many WarengruppeVK have been done in some timeframe, e.g. jahr=2012 and monat=2 tells us how many times a product has been sold to a specific customer in February 2012.

Now, I have a tableview and need to show how much revenue has been made for each specific product in some defined timeframe (user can define start and end dates). Additionally, I need to show how much revenue has been made in the same timeframe one year before.

So, this is how I'm doing it: First, I'm fetching all Warengruppe objects from Core data (12 products right now). Then, I'm using a for loop on my array of Warengruppe and calculate the sum of netto of each WarengruppeVK, using a predicate where PbsRow's jahr and monat are set to the user chosen timeframe. I'm defining the FetchRequest like this: NSFetchRequest *request = [[NSFetchRequest alloc] initWithEntityName:@"WarengruppeVK"];

NSExpression *keyPathExpressionNetto = [NSExpression expressionForKeyPath:@"netto"];
NSExpression *keyPathExpressionDB = [NSExpression expressionForKeyPath:@"db_basis"];

NSExpression *sumExpressionNetto = [NSExpression expressionForFunction:@"sum:" arguments:[NSArray arrayWithObject:keyPathExpressionNetto]];
NSExpression *sumExpressionDB = [NSExpression expressionForFunction:@"sum:" arguments:[NSArray arrayWithObject:keyPathExpressionDB]];

NSExpressionDescription *expressionDescriptionNetto = [[NSExpressionDescription alloc]init];
[expressionDescriptionNetto setName:@"sumnetto"];
[expressionDescriptionNetto setExpression:sumExpressionNetto];
[expressionDescriptionNetto setExpressionResultType:NSDecimalAttributeType];
NSExpressionDescription *expressionDescriptionDB = [[NSExpressionDescription alloc]init];
[expressionDescriptionDB setName:@"sumdb"];
[expressionDescriptionDB setExpression:sumExpressionDB];
[expressionDescriptionDB setExpressionResultType:NSDecimalAttributeType];

[request setPropertiesToFetch:[NSArray arrayWithObjects:expressionDescriptionNetto, expressionDescriptionDB, nil]];
[request setResultType:NSDictionaryResultType];

And here's the loop:

for (Warengruppe *wgruppe in warengruppen) {
    //predicateVJ defines the Timeframe for PbsRow    
    productPredicateVJ = [NSCompoundPredicate andPredicateWithSubpredicates:[NSArray arrayWithObjects:predicateVJ, [NSPredicate predicateWithFormat:@"warengruppe=%@",wgruppe],nil]];

    [request setPredicate:productPredicateVJ];

    NSArray *objectsVJ = [context executeFetchRequest:request error:&error];

    float nettoVJ = [[[objectsVJ valueForKey:@"sumnetto"] firstObject] floatValue];
    float dbVJ = [[[objectsVJ valueForKey:@"sumdb"] firstObject] floatValue];
    float dgVJ = dbVJ/nettoVJ*100;

    //Now doing the same for Timeframe minus 1 year (predicateJ)
    productPredicateJ = [NSCompoundPredicate andPredicateWithSubpredicates:[NSArray arrayWithObjects:predicateJ, [NSPredicate predicateWithFormat:@"warengruppe=%@",wgruppe],nil]];

    [request setPredicate:productPredicateJ];

    NSArray *objectsJ = [context executeFetchRequest:request error:&error];

    float nettoJ = [[[objectsJ valueForKey:@"sumnetto"] firstObject] floatValue];
    float dbJ = [[[objectsJ valueForKey:@"sumdb"] firstObject] floatValue];
    float dgJ = dbJ/nettoJ*100;

}

Now the thing is, it takes about 14 Seconds to fetch all the data. But if I remove the the second part (which begins with //doing the same for timeframe minus 1) it only takes 7 seconds.

So my question is, is there some correct way to split this up into 2 threads? I have already tried this with 2 dispatch_async(dispatch_get_global_queue( DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{ blocks, but for some reason it just takes the same amount of time (14 secs). Or is there some other way to improve this request? I can't use any fetch limits, since I need the absolute sum of these values. The big problem is the huge amount of data I'm using. It's an enterprise app which has to work offline, so there's no way for me to kinda calculate this stuff on some server and use http to pass it to the iPad.

WarengruppeVK has 328.666 rows right now :-/.

Thanks in advance.

EDIT: Here is a piece of the full screen on the iPad, after calculation was done: enter image description here

VJ means last year and IST means the current timeframe

EDIT 2: predicateVJ and predicateJ are initialized like this (in this example, the chosen timeframe is current month):

NSPredicate *predicateVJ = [NSPredicate predicateWithFormat:@"(pbsrow.jahr=%d) AND (pbsrow.monat=%d)", year-1, month];
NSPredicate *predicateJ = [NSPredicate predicateWithFormat:@"(pbsrow.jahr=%d) AND (pbsrow.monat=%d)", year, month];

Then I'm starting the fetch request on a background thread:

dispatch_async(dispatch_get_global_queue( DISPATCH_QUEUE_PRIORITY_LOW, 0), ^{

    NSArray *warengruppen = [[CoredataModelHolder sharedHolder] allWarenGruppen];
    NSManagedObjectContext *context = [[NSManagedObjectContext alloc] init];
    [context setPersistentStoreCoordinator:[(AppDelegate*)[[UIApplication sharedApplication] delegate] persistentStoreCoordinator]];

    NSPredicate *productPredicateVJ;
    NSPredicate *productPredicateJ;

    for (Warengruppe *wgruppe in warengruppen) {
    //See 2nd snippet of my question.
    }
});
gasparuff
  • 2,295
  • 29
  • 48
  • I suspect the issue is the NSExpressions. There's a built in `@sum` operator which should perform better. Try to comment out the last two lines from your first code snippet, and then replace the lines setting `nettoVJ` and `dbVJ` with `[[objectsVJ valueForKeyPath:"@sum.netto"] floatValue]` and `[[objectsVJ valueForKeyPath:"@sum.db_basis"] floatValue]` respectively and see if that helps. – Sherman Lo Apr 25 '13 at 08:09
  • Thanks, but doing it this way takes just roughly the same amount of time :-( – gasparuff Apr 25 '13 at 08:31
  • Hmm, in that case it looks like it might be at the database level. You have indexes on the fields you're querying on right? Add `-com.apple.CoreData.SQLDebug 1` to the arguments passed at launch (It'll be under "Edit Scheme..."), this will print info to the debug console indicating how long each query is taking. – Sherman Lo Apr 25 '13 at 08:39
  • Yes, I have indexed the relevant fields. It's definetely at the database level - it's because there are just too many rows. I was hoping to find some multi-threaded solution where I can fetch the sums of the current year and the sums of the last year simultaneously while saving some time. – gasparuff Apr 25 '13 at 11:04
  • Should I also have an index in my `Warengruppe` table? I didn't put any index there because I'm using the whole object in my predicate like this: `[NSPredicate predicateWithFormat:@"warengruppe=%@",wgruppe]` and core data should handle this automatically by comparing the managedObjectID, so there's no need of any index, is it? – gasparuff Apr 25 '13 at 11:10
  • 300,000 rows isn't that many, take a look at the first answer of http://stackoverflow.com/questions/11901931/ios-implementation-theory as an example. Can you paste the sqlite debug statements you get from turning on logging above. Also, how is predicateVJ initialised? There shouldn't be any need for you to add an index, CoreData should take care of it. – Sherman Lo Apr 25 '13 at 11:25
  • I have edited my question and added information about how predicateVJ and predicateJ is initialized. The debug statements are here: http://pastebin.com/pH9hCFSu – gasparuff Apr 25 '13 at 12:05
  • Everything looks like it should be fine, except for the time the queries are taking. Just double checking, which fields are indexed? `jahr` and `monat`? – Sherman Lo Apr 25 '13 at 15:54
  • Yes, both jahr and monat are Indexed and defined as Integer 16 :-(. – gasparuff Apr 25 '13 at 16:18
  • I have some other viewcontrollers where i'm fetching PbsRows instead of warengruppeVKs and they work really fast (also using predicates on Jahr and monat). I just don't get it :-( – gasparuff Apr 25 '13 at 16:20
  • From a closer look at a random CoreData sqlite file I've got here my assumption about `warengruppe` being indexed might be incorrect. Can you try and get rid of `[NSPredicate predicateWithFormat:@"warengruppe=%@",wgruppe]` from your predicate and see if that changes performance significantly? – Sherman Lo Apr 25 '13 at 16:42
  • Keep core data debugging on to make sure that all the queries go through to the database and it's not being cached. – Sherman Lo Apr 25 '13 at 16:43
  • Unfortunately this didn't really change the performance, still about 13 secs. Here's the Core data log: http://pastebin.com/jbfbw3Qy – gasparuff Apr 26 '13 at 07:19

0 Answers0