2

I am encountering and error while doing a data load.

here is my code when dataloading. is there anything that needs to be optimize in here so far?

public class depToAccount {
public static void deptpAccountV1(List<Dependent__c> depmem) {
    Set<Id> accountIdSet = new Set<Id>();
    Set<Id> coverIdSet = new Set<Id>();
    Map<Id, List<Dependent__c>> atoDep = new Map<Id, List<Dependent__c>>();
    Map<Id, List<Dependent__c>> ctomeddep = new Map<Id, List<Dependent__c>>();
    Map<Id, List<Dependent__c>> cto19belowdep = new Map<Id, List<Dependent__c>>();
    Map<Id, List<Dependent__c>> ato19belowdep = new Map<Id, List<Dependent__c>>();
    Map<Id, List<Dependent__c>> cto20belowdep = new Map<Id, List<Dependent__c>>();
    Map<Id, List<Dependent__c>> ato20updep = new Map<Id, List<Dependent__c>>();//
    List<CampaignMember> cmList = new List<CampaignMember>();

    Map<Id, Account> accountToUpdateMap = new Map<Id, Account>();


    for(Dependent__c depm : depmem){
if(depm.status__c == 'ACTIVE' && depm.Catg__c == 'Vision'){
    accountIdSet.add(depm.Account__c);
    coverIdSet.add(depm.Cover__c); }
    }       
    accountIdSet.remove(null);
    coverIdSet.remove(null);

    List<Dependent__c> existingDependent = [SELECT Id, Account__c, Account__r.Name, Cover__c, Cover__r.Comply__c,Rel__c, status__c, Catg__c, Age_Today__c 
                                                        FROM Dependent__c 
                                                        WHERE ((Cover__c IN: coverIdSet AND Cover__c != null)
                                                               OR (Account__c IN: accountIdSet AND Account__c != null))
                                                        AND status__c ='ACTIVE' AND Plan_Name__c != NULL aND Catg__c != NULL];
    for(Dependent__c depM : existingDependent){
        if(depM.Age_Today__c < 19){
            {
                Depedendent__c[] records;
                if((records = cto19belowdep.get(depM.Cover__c)) == null) {
                    cto19Below.put(depM.Cover__c, records = new Dependent__c[0]);
                }
                records.add(depM);
            }

            if(ato19belowdep.containsKey(depM.Account__c)){
                ato19belowdep.get(depM.Account__c).add(depM);
            }
            else {
                ato19belowdep.put(depM.Account__c, new List<Dependent__c>{ depM });
            }
        }
        if(depM.Age_Today__c >= 19){
            {
                    Depedendent__c[] records;
                if((records = cto20belowdep.get(depM.Cover__c)) == null) {
                    cto20belowdep.put(depM.Cover__c, records = new Dependent__c[0]);
                }
                records.add(depM);
            }

            if(ato20updep.containsKey(depM.Account__c)){
                ato20updep.get(depM.Account__c).add(depM);
            }
            else {
                ato20updep.put(depM.Account__c, new List<Dependent__c>{ depM });
            }
        }
        if(depM.Catg__c == 'Vision'){
            if(ctomeddep.containsKey(depM.Cover__c)){
                ctomeddep.get(depM.Cover__c).add(depM);
            }
            else {
                ctomeddep.put(depM.Cover__c, new List<Dependent__c>{ depM });
            }
        }

        if(depM.Catg__c == 'Dental'){
            if(atoDep.containsKey(depM.Account__c)){
                atoDep.get(depM.Account__c).add(depM);
            }
            else {
                atoDep.put(depM.Account__c, new List<Dependent__c>{ depM });
            }
        }
    }
    Map<Id, Account> acctAttesAppMap = new Map<Id, Account>([Select ID,Good__c,Age_in_Numbers__pc,PersonContactId,Phone__c ,NumberofYears__c  FROM Account where ID IN : accountIdSet ]);

    for(Id acctId : accountIdSet){
    Account accRecord = accRecord);
        for(Dependent__c depM : existingDependent){
            for(Id covId : coverIdSet){
                if(cto19belowdep.containsKey(covId) && depM.Cover__c==covId){ 
                    for(Dependent__c depU19 : cto19belowdep.get(covId)){
                        if(depU19.Age_Today__c < 19){ 

                            if((atoDep.containsKey(acctId)) || (accRecord.Good__c == true) || accRecord.NumberofYears__c > 0 )){ 
                                if(ctomeddep.containsKey(covId)){
                                    for(Dependent__c depMemMedi : ctomeddep.get(covId)){ 
                                        accountToUpdateMap.put(depMemMedi.Account__c, new Account(Id=depMemMedi.Account__c, Compliant__c=true, Comply__c=false));
                                    }
                                }

                            }
                            else {
                                if((!atoDep.containsKey(acctId)) || (accRecord.Good__c == false) || (accRecord ).NumberofYears__c == 0 )){ 
                                    if(ctomeddep.containsKey(covId)){
                                        for(Dependent__c depMemMedi : ctomeddep.get(covId)){
                                            accountToUpdateMap.put(depMemMedi.Account__c, new Account(Id=depMemMedi.Account__c, Compliant__c=false, Comply__c=false));
                                        }
                                    }

                                }
                                IF(accRecord.Phone__c != NULL ){
                                    {

                                        cmList.add( new CampaignMember(
                                            CampaignId  = System.Label.Doctor_Campaign,
                                            ContactId = accRecord).PersonContactId,
                                            Status = 'Completed'
                                        ));


                                    }
                                }
                                IF(accRecord.Phone__c == NULL && accRecord.Age_in_Numbers__pc >= 19){
                                    {
                                        cmList.add( new CampaignMember(
                                            CampaignId  = System.Label.Nurse_Campaign,
                                            ContactId = accRecord).PersonContactId,
                                            Status = 'Completed'
                                        ));
                                    }
                                }
                                IF(accRecord.Phone__c == NULL &&  accRecord.Age_in_Numbers__pc < 19){
                                    {
                                        cmList.add( new CampaignMember(
                                            CampaignId  = System.Label.Staff_Campaign,
                                            ContactId = accRecord).PersonContactId,
                                            Status = 'Completed'
                                        ));
                                    }
                                }
                            }
                        }

                    }
                else if(cto20belowdep.containsKey(covId) && depM.Cover__c==covId){
                    for(Dependent__c depU20 : cto20belowdep.get(covId)){
                        if(depU20.Age_Today__c >= 19){ 
                            if(atoDep.containsKey(acctId) ){
                                if(ctomeddep.containsKey(covId)){
                                    for(Dependent__c depMemMedi : ctomeddep.get(covId)){
                                        accountToUpdateMap.put(depMemMedi.Account__c, new Account(Id=depMemMedi.Account__c, Compliant__c=false, Comply__c=true));
                                    }
                                }
                            }
                        }

                    }
                }
                if(depM.Cover__r.Comply__c==true && depM.Cover__c == covId){
                    if(ctomeddep.containsKey(covId)) 
                        for(Dependent__c CMmed : ctomeddep.get(covId)){
                            accountToUpdateMap.put(CMmed.Account__c, new Account(Id=CMmed.Account__c, Compliant__c=false, Comply__c=true));
                        }
                } 

            }
        }
    }
    database.insert(cmlist, false);
    update accountToUpdateMap.values();

}

}

  • How is this code being run? Is it called from a trigger, run in Anonymous Apex, ...? How many records is the main query returning? – David Reed Jul 8 at 15:57
  • Going to take a moment, there's a lot you can optimize here. – sfdcfox Jul 8 at 15:58
  • this is run using an update trigger depToAccount.depToAccountV1(trigger.new); it is a job that loads around 1.2M data. – Cloud Jul 8 at 15:58
  • I am planning to limit it by 10000, but my queston would be if I Limit that to 10k, will the whole record run? or only 10k? – Cloud Jul 8 at 16:00
4
for(Dependent__c depm : depmem){
    if(depm.status__c == 'ACTIVE' && depm.Catg__c == 'Vision'){
        if(depm.Account__c != null){
            accountIdSet.add(depm.Account__c);
        }
        if(depm.Cover__c != null){
            coverIdSet.add(depm.Cover__c); 
        }
    }
}

You don't need to check for null here. You can do it at the end:

for(Dependent__c depm : depmem){
    if(depm.status__c == 'ACTIVE' && depm.Catg__c == 'Vision'){
        accountIdSet.add(depm.Account__c);
        coverIdSet.add(depm.Cover__c); 
    }
}
accountIdSet.remove(null);
coverIdSet.remove(null);

Probably doesn't matter, but you can optimize the query, too. Also, you're only looking for records where cover and account matches, which will return far fewer records:

List<Dependent__c> existingDependent = [SELECT Id, Account__c, Account__r.Name, Cover__c, Cover__r.Comply__c,Rel__c, status__c, Catg__c, Age_Today__c 
                                                    FROM Dependent__c 
                                                    WHERE ((Cover__c IN: coverIdSet)
                                                        AND (Account__c IN: accountIdSet))
                                                    AND status__c ='ACTIVE' AND Plan_Name__c != NULL aND Catg__c != NULL];

Instead of checking for a key then calling get, you can do both at once to improve efficiency:

{
    Depedendent__c[] records;
    if((records = cto19belowdep.get(depM.Cover__c)) == null) {
        cto19Belowmap.put(depM.Cover__c, records = new Dependent__c[0]);
    }
    records.add(depM);
}

Get objects from a map as few times as possible; cache the data for performance:

if((atoDep.containsKey(acctId)) || (acctAttesAppMap.get(acctId).Good__c == true) || (acctAttesAppMap.get(acctId ).NumberofYears__c > 0 )){ 

...

if((!atoDep.containsKey(acctId)) || (acctAttesAppMap.get(acctId).Good__c == false) || (acctAttesAppMap.get(acctId ).NumberofYears__c == 0 )){ 

...

Can be rewritten as:

Account accRecord = acctAttesAppMap.get(acctId);

...

if((atoDep.containsKey(acctId)) || (accRecord.Good__c == true) || (accRecord.NumberofYears__c > 0 )){ 

...

if((!atoDep.containsKey(acctId)) || (accRecord.Good__c == false) || (accRecord.NumberofYears__c == 0 )) { 

...

Finally, this loop can be optimized:

for(Id acctId : accountIdSet){
    for(Dependent__c depM : existingDependent){
        for(Id covId : coverIdSet){

You'll need to use maps to achieve this.

  • I have replaced the if(cto19belowdep.containsKey(depM.Cover__c)){ cto19belowdep.get(depM.Cover__c).add(depM); } else { cto19belowdep.put(depM.Cover__c, new List<Dependent__c>{ depM }); } with { Depedendent__c[] records; if((records = cto19belowdep.get(depM.Cover__c) == null) { cto19Belowmap.put(depM.Cover__c, records = new Dependent__c[0]); } records.add(depM); } it seems the error was Compile Error: Illegal assignment from Boolean to List<Dependent__c> – Cloud Jul 8 at 17:41
  • @Cloud There was a missing parentheses in the code. Try the new version. – sfdcfox Jul 8 at 17:48
  • do I need to get the account as well? like these?Depedendent__c[] records; if((records = cto19belowdep.get(depM.Cover__c)) == null) { cto19Belowmap.put(depM.Cover__c, records = new Dependent__c[0]); } records.add(depM); Account__c[] accountrecords; if((accountrecords = ato19belowdep.get(depM.Account__c)) == null) { ato19belowdep.put(depM.Account__c, accountrecords = new Dependent__c[0]); } records.add(depM); – Cloud Jul 8 at 18:14
  • I have editted do you think that would help now loading millions of data? thank you – Cloud Jul 9 at 0:14
  • @Cloud You still have other places you can optimize your map usage. You also apparently missed the bit about updating your filters. You should be able to reduce the number of records returned by using AND instead of OR. Also, I wasn't quite sure how to write the last set of loops, but those can clearly use a map or two to reduce the matching process to linear time instead of exponential time. You might also ultimately just need to reduce the data load batch size in order to keep the process running fast enough to avoid limits. – sfdcfox Jul 9 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.