function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AishwaryAishwary 

How to avoid soql from the loop in batch class?

Hi, I have created a class for processing opportunity object based on the custom object "staging__c". But the issue is I'm executing SOQL inside for loop which is a bad practice. Can anyone help me to avoid it?
Here is my code for the same please have a look.
Note: My code is working. The only issue is soql which I am executing in the loop. I have commented on the issues in my code for better understanding. 
global class BatchAssignment implements Database.Batchable<sObject>{
    global Database.QueryLocator start(Database.BatchableContext BC){     
        return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c 
                                         FROM staging__c]);
    }    
    
  global void execute(Database.BatchableContext BC, List<staging__c> scope){
        List<Opportunity> newOpptyList = new List<Opportunity>();
   
      for(staging__c sc:scope){
          system.debug('in for loop');
        if(sc != null && sc.Individual_or_organization__c  == 'I') {
            system.debug('in if == I');
            // calling custom method that executes soql inside loop and storing output here
            Account objAccount = searchAccount(sc);
            if(objAccount != null) {
                 // calling custom method that executes soql inside loop and storing output here
                List<Contact> linkContactList = searchContact(objAccount, sc);
                system.debug('contact'+linkContactList);
                try{
                    if(linkContactList != null && !linkContactList.isEmpty()) {
                        upsert linkContactList;
                        system.debug('creating opp');
                        //Create new opportunity;
                        for(Contact objCon : linkContactList) {
                            newOpptyList.add(new Opportunity(Name = objCon.LastName,
                                                             StageName = 'Prospecting',
                                                             AccountId = objAccount.Id,
                                                             ContactId = objCon.Id,
                                                             CLOSEDATE=Date.today()
                                                            ));                       
                            
                        }
                        
                        if(newOpptyList != null && !newOpptyList.isEmpty()) {
                            system.debug('insert opp');
                            insert newOpptyList;
                        }
                    }
                }catch(Exception ex) {
                    system.debug('---Exception--' + ex);
                }
                
            }
        }
      }
    }
    // this method is getting called inside the loop of execute method which is bad
    private Account searchAccount(staging__c scope) {
        Account acc= new Account();
        if(scope.Orgganization_Name__c != null && scope.Postal_Code__c != null) {
            system.debug('acc not zero');
            acc= [SELECT Id, Name FROM Account WHERE Name = :scope.Orgganization_Name__c AND BillingPostalCode = :scope.Postal_Code__c];
        }
        return acc;
    }
    
    // this method is getting called inside the loop of execute method which is bad
    private List<Contact> searchContact(Account objAccount, staging__c scope) {
        List<Contact> linkContactList = new List<Contact>();
        if(scope.First_Name__c != null && scope.Last_Name__c != null && scope.Postal_Code__c != null) {
            List<Contact> existingContactList = [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact 
                                                 WHERE FirstName = :scope.First_Name__c AND LastName = :scope.Last_Name__c AND MailingPostalCode = :scope.Postal_Code__c];
            //For existing contacts
            system.debug('existing contact'+existingContactList);
            if(existingContactList.size()>0 ) {
                for(Contact objCon : existingContactList) {
                    objCon.AccountId = objAccount.Id;
                    linkContactList.add(objCon);
                }
            } else {
                //create new contact
                system.debug('into else');
                linkContactList.add(new Contact(FirstName = scope.First_Name__c, 
                                                LastName = scope.Last_Name__c,
                                                MailingPostalCode = scope.Postal_Code__c,
                                                AccountId = objAccount.Id));
                system.debug('linked contact2'+linkContactList);
            }
            
        }
        return linkContactList;
    }
    
    global void finish(Database.BatchableContext BC){   
        system.debug('finished:::');
    }
}


 
Maharajan CMaharajan C
Hi Aishwary,

Please try the below updated code:

Please handle if there is any syntax issues came. 
  
global class BatchAssignment implements Database.Batchable<sObject>{
    global Database.QueryLocator start(Database.BatchableContext BC){     
        return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c 
                                         FROM staging__c]);
    }    
    
  global void execute(Database.BatchableContext BC, List<staging__c> scope){
        List<Opportunity> newOpptyList = new List<Opportunity>();
		Map<String, Account> accMap = new Map<String, Account>();
		Map<Id, List<Contact> accContacts = new Map<Id, List<Contact>();
		set<String> accNameSet = new set<String>();
		set<String> postalSet = new set<String>();
		Map<String,staging__c> stageMap = new Map<String,staging__c>();
		try{
			for(staging__c sc:scope){
				if(sc != null && sc.Individual_or_organization__c  == 'I' && sc.Orgganization_Name__c != null && sc.Postal_Code__c != null) {
					accNameSet.add(sc.Orgganization_Name__c);
					postalSet.add(sc.Postal_Code__c);
					stageMap.put(sc.Orgganization_Name__c + '-' + sc.Postal_Code__c, sc);
				}
			}
			
			if(!accNameSet.IsEmpty() && !postalSet.IsEmpty())
				accMap = searchAccount(accNameSet, postalSet);
			
			if(!accMap.IsEmpty()){
				accContacts = searchContact(accMap , stageMap, scope)
			}
				
			if(!accContacts.isEmpty()){	
				for(Id  accId : accContacts.keyset()){
					List<Contact> accCons = accContacts.get(accId);
					for(Contact objCon : accCons) {
						newOpptyList.add(new Opportunity(Name = objCon.LastName,
														 StageName = 'Prospecting',
														 AccountId = accId,
														 ContactId = objCon.Id,
														 CLOSEDATE=Date.today()
														));                       
						
					}			
				}
			}	
			if(!newOpptyList.isEmpty()) {
				system.debug('insert opp');
				insert newOpptyList;
			}
		}catch(Exception ex) {
			system.debug('---Exception--' + ex);
		}
    }

    private Map<String, Account> searchAccount(set<String> accNameSet, set<String> postalSet) {
        Map<String, Account> accretMap= new Map<String, Account>();
        for(Account acc : [SELECT Id, Name, BillingPostalCode FROM Account WHERE Name IN: accNameSet  AND BillingPostalCode IN: postalSet ]){
			accretMap.put(acc.Name + '-' + acc.BillingPostalCode , acc);
        }
        return accretMap;
    }
    
    private Map<Id, List<Contact>> searchContact(Map<String, Account> objAccount, Map<String,staging__c> stageMap, List<staging__c> scope) {
        Map<Id, List<Contact>> returnMap = new Map<Id, List<Contact>>();
		List<Contact> contactListtoUpsert = new List<Contact>();
		Map<String, List<Contact>> conStageMap = new Map<Id, List<Contact>>();
		set<String> fnNameSet = new set<String>();
		set<String> postalSet = new set<String>();
		set<String> lnNameSet = new set<String>();
		for(staging__c sc : scope){
			if(sc.First_Name__c != null && sc.Last_Name__c != null && sc.Postal_Code__c != null) {
				fnNameSet.add(sc.First_Name__c);
				lnNameSet.add(sc.Last_Name__c);
				postalSet.add(sc.Postal_Code__c);
			}
		}
		
		for(contact con : [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact 
                                                 WHERE FirstName IN: fnNameSet AND LastName IN:lnNameSet AND MailingPostalCode IN: postalSet]){
			String strkey = con.FirstName + '-' + con.LastName + '-' + con.MailingPostalCode;
			if(!conStageMap.containsKey(strkey)){
				conStageMap.put(strkey, new List<Contact>{con});
			}
			else{
				conStageMap.get(strkey).add(con);
			}
		}
		
		for(string k : objAccount){
			Account acc = objAccount.get(k);
			if(stageMap.containsKey(k))
			{
				staging__c stage = stageMap.get(k);
				if(stage.First_Name__c != null && stage.Last_Name__c != null && stage.Postal_Code__c != null) {
					String sKey = stage.FirstName + '-' + stage.LastName + '-' + stage.MailingPostalCode;
					List<Contact> linkContactList = new List<Contact>();
					if(conStageMap.containsKey(sKey)){
						for(contact c : conStageMap.get(sKey)){
							c.AccountId = acc.Id;
							linkContactList.add(c);
							contactListtoUpsert.add(c);
						}
					}
					else{
						contact cont = new Contact(FirstName = stage.First_Name__c, 
                                                LastName = stage.Last_Name__c,
                                                MailingPostalCode = stage.Postal_Code__c,
                                                AccountId = acc.Id);
						linkContactList.add(cont);
						contactListtoUpsert.add(cont);
					}
					
					returnMap.put(acc.Id, linkContactList);
				}
			}
		}
		if(!contactListtoUpsert.IsEmpty()){
			upsert contactListtoUpsert;
		}
		
		return returnMap;
    }
    
    global void finish(Database.BatchableContext BC){   
        system.debug('finished:::');
    }
}

Thanks,
Maharajan.C
bfdhng gfdhbgbfdhng gfdhbg
This is pretty lengthy so you can visit here (https://jacobmartin.com/) to learn about the complete code guide that will allow you to implement this task on your project.