+ Start a Discussion
Marco SantosMarco Santos 

Avoid SOQL Queries Inside FOR Loops

Hi, 

Could enybody tell me how to change this for loop to put the SOQL outside the for loop to efficiently query records?
for (Opportunity Opp :Trigger.new){

    	Boolean flag = false;

    	Account a = [Select id, name from Account where Id = :Opp.AccountId][0];

    	if (a.Name == 'XB Sample') {
    		flag = true;
    	}
}

 I will be apreciate for help

Thanks
Best Answer chosen by Marco Santos
Balaji Chowdary GarapatiBalaji Chowdary Garapati
@Marco Santos:

 To avoid having a query inside the for loop, the structure you need to follow will be :

1) Loop through all the records and collect your condition criteria
2) Query 
3) Loop Through the results to perform in your operation.


Coming to your problem, the result will look something like:
 
Set<id>AccountIdSet=new Set<Id>(); // To Collect All the account ids we need to query
	Map<Id,Account>AccountMap;//This is to have quick access to the accounts that were queried.
	
	//First Collect Account Ids
for (Opportunity Opp :Trigger.new){
    	
AccountIdSet.add(Opp.AccountId);
    	
}

//Second Query Using Collected Id's

    	AccountMap=new Map<Id,Account>([Select id, name from Account where Id IN :AccountIdSet]);
		
//Third Loop through the opps again to see what the results are

for (Opportunity Opp :Trigger.new){

    	Boolean flag = false;

    	if (AccountMap.get(opp.AccountId).Name == 'XB Sample') {
    		flag = true;
    	}
}

Hope it helps.,

Thanks,
Balaji

 

All Answers

Balaji Chowdary GarapatiBalaji Chowdary Garapati
@Marco Santos:

 To avoid having a query inside the for loop, the structure you need to follow will be :

1) Loop through all the records and collect your condition criteria
2) Query 
3) Loop Through the results to perform in your operation.


Coming to your problem, the result will look something like:
 
Set<id>AccountIdSet=new Set<Id>(); // To Collect All the account ids we need to query
	Map<Id,Account>AccountMap;//This is to have quick access to the accounts that were queried.
	
	//First Collect Account Ids
for (Opportunity Opp :Trigger.new){
    	
AccountIdSet.add(Opp.AccountId);
    	
}

//Second Query Using Collected Id's

    	AccountMap=new Map<Id,Account>([Select id, name from Account where Id IN :AccountIdSet]);
		
//Third Loop through the opps again to see what the results are

for (Opportunity Opp :Trigger.new){

    	Boolean flag = false;

    	if (AccountMap.get(opp.AccountId).Name == 'XB Sample') {
    		flag = true;
    	}
}

Hope it helps.,

Thanks,
Balaji

 
This was selected as the best answer
Marco SantosMarco Santos
Hi Balaji, thank you for your help

Thank you