How to use Database.Statful in batch apex

apex-codesalesforce

I have a batch process that is aggregating about 275,000 records by fund. I have the batch size set to the maximum number of 200. So, when the batch runs there are 1,380 batches that are processed.

I need to process all the funds by name. For example, if I have a fund called "Fund A". In the first batch of 200 records "Fund A" might be in that batch 10 times and "Fund A" is rolled up for those 10 records and is inserted.

The next batch of 200 records is processed and "Fund A" might be in those 200 records 5 times. "Fund A" is rolled up for those 5 records and inserted.

This continues until all batches have been processed and I end up inserting multiple records for "Fund A".

Reading the batch apex documentation it indicates the following:

Each execution of a batch Apex job is considered a discrete transaction. For example, a batch Apex job that contains 1,000 records and is executed without the optional scope parameter is considered five transactions of 200 records each.

If you specify Database.Stateful in the class definition, you can maintain state across these transactions. When using Database.Stateful, only instance member variables retain their values between transactions. Static member variables don’t and are reset between transactions. Maintaining state is useful for counting or summarizing records as they're processed. For example, suppose your job processed opportunity records. You could define a method in execute to aggregate totals of the opportunity amounts as they were processed.

If I understand correctly, I can combine "Fund A" into one record across all batches and then insert that one record?

I'm trying to use database.stateful, but I'm getting the error:

First error: Insert failed. First exception on row 1 with id a28e0000000uvHYAAY; first error: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify Id in an insert call: [Id]

Here is my batch code:

global class ProductionAggregateRollupBatch implements Database.Batchable<sObject>, Database.Stateful {

public string query = 'select DBR__c, FundName__r.Name, FundName__r.Class__c, PurchasesPY__c, PurchasesPYTD__c, PurchasesYTD__c, RedemptionsPY__c, RedemptionsPYTD__c, RedemptionsYTD__c, AUM__c from Production_Aggregate__c';
global Map<String, Funds_Purchased__c> rollupMap = new Map<String, Funds_Purchased__c>();

global database.querylocator start(Database.BatchableContext BC)
{
    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, Sobject[] scope)
{
    Set<Id> dbrIds = new Set<Id>();

    for (Production_Aggregate__c pa : (List<Production_Aggregate__c>)scope) {
        dbrIds.add(pa.DBR__c);
    }

    Map<Id, List<Id>> dbrToContactMap = getDbrToContactMap(dbrIds);

    for (Production_Aggregate__c pa : (List<Production_Aggregate__c>)scope) {
        if(!dbrToContactMap.isEmpty() && dbrToContactMap.size() > 0) {
            if(dbrToContactMap.containsKey(pa.DBR__c)) {
                List<Id> contactIds = dbrToContactMap.get(pa.DBR__c);
                for(Id contactId : contactIds) {

                    String index = '' + new String[] {
                        '' + pa.FundName__r.Name,
                        '' + contactId
                    };                      

                    Funds_Purchased__c fundPurchased = rollupMap.get(index);

                    if(fundPurchased == null) {
                        fundPurchased = new Funds_Purchased__c();
                        rollupMap.put(index, fundPurchased);
                    }

                    fundPurchased.Fund_Name__c = pa.FundName__r.Name;
                    fundPurchased.DBR__c = pa.DBR__c;
                    fundPurchased.Contact__c = contactId;

                    if(pa.AUM__c == null) pa.AUM__c = 0;
                    if(fundPurchased.AUM__c == null)fundPurchased.AUM__c = 0;
                    fundPurchased.AUM__c += pa.AUM__c;

                    if(pa.PurchasesPY__c == null) pa.PurchasesPY__c = 0;
                    if(fundPurchased.PurchasesPY__c == null)fundPurchased.PurchasesPY__c = 0;                       
                    fundPurchased.PurchasesPY__c += pa.PurchasesPY__c;

                    if(pa.PurchasesPYTD__c == null) pa.PurchasesPYTD__c = 0;
                    if(fundPurchased.PurchasesPYTD__c == null)fundPurchased.PurchasesPYTD__c = 0;                   
                    fundPurchased.PurchasesPYTD__c += pa.PurchasesPYTD__c;

                    if(pa.PurchasesYTD__c == null) pa.PurchasesYTD__c = 0;
                    if(fundPurchased.PurchasesYTD__c == null)fundPurchased.PurchasesYTD__c = 0;                     
                    fundPurchased.PurchasesYTD__c += pa.PurchasesYTD__c;

                    if(pa.RedemptionsPY__c == null) pa.RedemptionsPY__c = 0;
                    if(fundPurchased.RedemptionsPY__c == null)fundPurchased.RedemptionsPY__c = 0;                   
                    fundPurchased.RedemptionsPY__c += pa.RedemptionsPY__c;

                    if(pa.RedemptionsPYTD__c == null) pa.RedemptionsPYTD__c = 0;
                    if(fundPurchased.RedemptionsPYTD__c == null)fundPurchased.RedemptionsPYTD__c = 0;                       
                    fundPurchased.RedemptionsPYTD__c += pa.RedemptionsPYTD__c;

                    if(pa.RedemptionsYTD__c == null) pa.RedemptionsYTD__c = 0;
                    if(fundPurchased.RedemptionsYTD__c == null)fundPurchased.RedemptionsYTD__c = 0;                 
                    fundPurchased.RedemptionsYTD__c += pa.RedemptionsYTD__c;                        
                }
            }
        }           
    if(!rollupMap.isEmpty() && rollupMap.size() > 0) {
        insert rollupMap.values();
    }       
}   

global void finish(Database.BatchableContext BC) {

}

private Map<Id, List<Id>> getDbrToContactMap(Set<Id> dbrIds) {
    Map<Id, List<Id>> dbrContactMap = new Map<Id, List<Id>>();
    List<Id> contactIds = new List<Id>();
    for(DBR_Group_Member__c member : [select Id, Contact__c, DBR__c from DBR_Group_Member__c where DBR__c in: dbrIds AND IsActive__c = true]) {
        if(!dbrContactMap.containsKey(member.DBR__c)) {
            contactIds = new List<Id>();
            dbrContactMap.put(member.DBR__c, contactIds);
        }
        contactIds.add(member.Contact__c);
    }
    return dbrContactMap;
}
}

Thanks for any help. Anyone?

Best Answer

Move your insert to the finish method:

global void finish(Database.BatchableContext BC) {
    if(rollupMap.size() > 0) {
        insert rollupMap.values();
    }
}

Your code had insert at the end of the execute method and would attempt to call it 1380 times. On the first execute insert probably works and assigns Ids back to your in-memory objects. On the second execute your insert attempt throws the error message that you can't insert with given Ids. Moving insert to finish() will ensure you only insert once after all of your execute iterations are complete.

Maybe a bit of preparing upfront would help?

Another thing to consider is how many fields are in the multipicklist? Have you thought about making a bit of preparation upfront? For example on every insert & every update save the values from "important fields" to a helper Text(255) field on Contact, let's call it "duplicate category / segment / tag" or something like that. Mark that field as external id (it doesn't have to be unique, ext. id is enough to get it indexed).

Then you should be able to pretty fast select contacts where Account id and that "category" matches. If the field value is identical or the length is 255 it means it was truncated and you have to run exact field-by-field comparison. But if there are no Contacts with identical "category" - you should be safe to say that there are no duplicates.

Such thing would have to be carefully designed (trigger on Contact but also on Account to "recalculate" this field every time the field definitions change... and if it's possible to point from Contact to other records (say User) then you'd need to protect yourself from user changes too...) but I'd say definitely worth trying out. You don't even have to support all fields. Say just First & Last Name, Email, Phone, one of addresses. This should already be a massive help in "bucketing" them and then querying only from right buckets.

Code review ;)

Cache your calls

You have a lot of map get calls. And you call System.today() in a loop (I would kind of understand System.now... but today?). Check this interesting short video and do what you can to have more local variables and less script statements. The list of fields doesn't change for a given account - so why you painfully fetch it from the map with every iteration.

Check the logic

if(newContactWithAccountMap.get(accountId) != null) - this should never happen. if there are no Contacts for "that" acc being inserted - what are you doing in that function anyway. Are you being overly protective here or is this check actually required (which would suggest some bigger problems). Even for private contacts (with AccountId = null) you should not have fetched the Contacts and built the maps for Accounts that are unaffected.

So in first iteration I'd do something like that (it's minor improvement but who knows, might help):

for(String accountId : accountIdSet){
    List<String> fields =  mapOfAccountWithFilters.get(accountId);  // it's always same for that Account, isn't it?
    for(Contact newContact : newContactWithAccountMap.get(accountId)){
        for(Contact oldContact : mapOfAccountIdWithItsContact.get(accountId)){
            if(oldContact.id != newContact.id){
                Boolean allFieldsMatch = true;
                for(String fieldName : fields){
                    if(allFieldsMatch &= (oldContact.get(fieldName) == newContact.get(fieldName))){
                        oldContactsToUpdateSet.add(oldContact);
                    }else{
                        break;
                    }
                }
                if(allFieldsMatch) {
                    duplicateContactSet.add(newContact.Id);
                    break;
                }
            }
        }
    }
}
Date today = System.today();
for(Contact c : oldContactsToUpdateSet){
    c.Last_De_Duplication_Date__c = today;
}

2nd pass

If it doesn't help - you might try to incorporate bits of my idea here. Let's say you're inserting 3 contacts to account that already has 10. That's 3 * 10 = 30 comparisons in the inner loop (they're both brand new so the trick with comparing old & new contact's id doesn't help).

But if you'd prepare some kind of composite key like this "bucket" I've talked about you're really flattening it out.

for(String accountId : accountIdSet){
    List<String> fields =  mapOfAccountWithFilters.get(accountId);
    Set<String> oldContactBuckets = new Set<String>();
    /* I'm cheating here a bit. 
        All I want to know is whether there was a match. I don't care with which Contact.
        If you care - you'd have to convert this Set<String> to Map<String, Set<Id>> for example.
        Looks like you do care because you're setting this Last_De_Duplication_Date__c
        but I'll leave it as exercise for the reader :P
    */
    for(Contact oldContact : mapOfAccountIdWithItsContact.get(accountId)){
        oldContactBuckets.add(buildKey(oldContact, fields));
    }
    for(Contact newContact : newContactWithAccountMap.get(accountId)){
        String key = buildKey(newContact, fields);
        if(oldContactBuckets.contains(key)){
            duplicateContactSet.add(newContact.Id);
        }
    }
}

private String buildKey(Contact c, List<String> fields){
    List<String> temp = new List<String>();
    for(String fieldName : fields){
        temp.add(String.valueOf(c.get(fieldName)));
    }
    return String.join(temp, '\n'); // pick a field separator that's unlikely to appear in your real data. Tab maybe?
}

This would build the 10 keys first, then compare with 3 keys for incoming data. Meaning only 13 equivalents of your "innermost" loop.

If this won't help in your situation - you can always consider rewriting it to batch apex I guess...

Best Answer

Related Solutions

Apex batch class start method not running

Need Help to Overcome Apex CPU time limit exceeded Error

Maybe a bit of preparing upfront would help?

Code review ;)

Related Topic