How to avoid nested for loop condition for code Optimization?

Question

public void execute(Database.BatchableContext BC, List<FinServ__FinancialAccount__c> scope) {
        Set<String> Acc_Numbers = new Set<String>();
        Map<String,List<Id>> My_Records = new Map<String,List<Id>>();
        List<Junction_Object__c> FinAcc_Custom_Rel = new List<Junction_Object__c>();
        List<Id> My_List = new List<Id>();
        
        for(FinServ__FinancialAccount__c FACC : scope){
            if(!String.isBlank(FACC.FinServ__FinancialAccountNumber__c)){
                Acc_Numbers.add(FACC.FinServ__FinancialAccountNumber__c);
            }enter code here
        }
        
        for(Custom_Object__c usrIds : [Select Id, Account_Number__c, UserId__c from Custom_Object__c where Account_Number__c =: Acc_Numbers]){
            if(!String.isBlank(usrIds.Account_Number__c)){
                My_List.add(usrIds.Id);
                My_Records.put(usrIds.Account_Number__c,My_List);
            }
        }
        
        for(FinServ__FinancialAccount__c FACC : scope){
            if(!String.isBlank(FACC.FinServ__FinancialAccountNumber__c) && My_Records.containsKey(FACC.FinServ__FinancialAccountNumber__c)){
                for(ID Q2 : My_Records.get(FACC.FinServ__FinancialAccountNumber__c)){
                    Junction_Object__c JOBB = new Junction_Object__c();
                    JOBB.Financial_Account__c = FACC.Id;
                    JOBB.My_U_ID__c = Q2;
                    FinAcc_Custom_Rel.add(JOBB);
                } 
            }
        }
        Database.insert(FinAcc_Custom_Rel);
    }

I am trying to create junction object records to establish Many to Many relationship.

Here, FinancialAccount Numbers can be same in multiple records, so have used a set.

The problem is that there could be multiple Custom_Object__c records having same account number, so I have map My_Records.

This works fine but I want to avoid using nested for loop here.

Would appreciate any help.

Answer 1

You can't particularly remove the for loop here. You're creating a matrix of records from two different sources, so a nested loop is the appropriate approach. You can reduce the number of records to process with some filters, as well as a few optimizations. Also, you have a problem, as you're adding all My_List records to all records in scope with an account Id because of a logic bug. My_List is passed by reference, so when you add values to the same list, you get all the values in one list. This is incorrect, and results in the ballooning situation you're seeing here.

Here's the revised, optimized code. Note the nested for loop still exists, as it is proper, but we trimmed out all the unnecessary code and fixed the list issue.

public void execute(Database.BatchableContext BC, List<FinServ__FinancialAccount__c> scope) {
    Set<String> Acc_Numbers = new Set<String>();
    Map<String,List<Id>> My_Records = new Map<String,List<Id>>();
    List<Junction_Object__c> FinAcc_Custom_Rel = new List<Junction_Object__c>();
    
    for(FinServ__FinancialAccount__c FACC : scope){
        Acc_Numbers.add(FACC.FinServ__FinancialAccountNumber__c);
        My_Records.put(facc.FinServ__FinancialAccountNumber__c, new Id[0]);
    }
    Acc_Numbers.remove(null); // Ignore blank values

    for(Custom_Object__c usrIds : [Select Id, Account_Number__c, UserId__c from Custom_Object__c where Account_Number__c =: Acc_Numbers]){
        My_Records.get(usrIds.Account_Number__c).add(usrIds.Id);
    }
    for(FinServ__FinancialAccount__c FACC : scope) {
        for(ID Q2 : My_Records.get(FACC.FinServ__FinancialAccountNumber__c)){
            FinAcc_Custom_Rel.add(
                new Junction_Object__c(
                    Financial_Account__c = FACC.Id, 
                    My_U_ID__c = Q2
                )
            );
        } 
    }
    Database.insert(FinAcc_Custom_Rel);
}