+ Start a Discussion
SubC4i-dev1SubC4i-dev1 

SOQL List vs SOQL For Loop

I have written the below for a trigger that I'm working on and both seem to produce the desired result.  Based on what I've learned they are two best pracitce ways to use SOQL queries.  But can someone please help me understand why I wouldn't want to just use the version with the SOQL For Loop almost all the time? (since it can help can avoid governor limits)

 

Version 1: SOQL List

 

List<OpportunityLineItem> OppProducts = new List<OpportunityLineItem>([SELECT Id, OpportunityId, PricebookEntryId, Post_Sale_Project__c FROM OpportunityLineItem WHERE OpportunityId IN: OppIds]);
		system.debug('OppProducts list size : ' + String.valueof(OppProducts.size()));
		
		for(OpportunityLineItem oli: OppProducts){
			if(psp.Opportunity__c == oli.OpportunityId){
				ResetOppProducts.put(oli.Id, new OpportunityLineItem(Id=oli.Id, OpportunityId = psp.Opportunity__c, Post_Sale_Project__c = null));
				UpdateOppProducts.put(oli.Id, new OpportunityLineItem(Id=oli.Id, OpportunityId = psp.Opportunity__c, Post_Sale_Project__c = psp.Id));
				system.debug('Post Sale Project Opportunity__c : ' + psp.Opportunity__c);
				system.debug('OpportunityLineItem OpportunityId : ' + oli.OpportunityId);
				system.debug('OpportunityLineItem Id : ' + oli.Id);
				system.debug('OpportunityLineItem ProductId : ' + oli.PricebookEntryId);
				system.debug('OpportunityLineItem Post Sales Project : ' + oli.Post_Sale_Project__c);
			}
		}

 

Version 2: SOQL List in For Loop

 

for(List<OpportunityLineItem> OppProducts: [SELECT Id, OpportunityId, PricebookEntryId, Post_Sale_Project__c FROM OpportunityLineItem WHERE OpportunityId IN: OppIds]){
			for(OpportunityLineItem oli: OppProducts){
				if(psp.Opportunity__c == oli.OpportunityId){
					ResetOppProducts.put(oli.Id, new OpportunityLineItem(Id=oli.Id, OpportunityId = psp.Opportunity__c, Post_Sale_Project__c = null));
					UpdateOppProducts.put(oli.Id, new OpportunityLineItem(Id=oli.Id, OpportunityId = psp.Opportunity__c, Post_Sale_Project__c = psp.Id));
				}
			}
		}

 

Thanks in advance for your help!

 

Best Answer chosen by Admin (Salesforce Developers) 
HariniHarini

Version 1 , using list  will cause a runtime exception if the number of records retreived by the soql query is more(results are too large).

The reason why this happens is because of the limit on heap size.

 

To resolve, use a SOQL query for loop instead, since it can process multiple batches of records through the use of internal calls toqueryandqueryMore

 

Hope this helps.

All Answers

HariniHarini

Version 1 , using list  will cause a runtime exception if the number of records retreived by the soql query is more(results are too large).

The reason why this happens is because of the limit on heap size.

 

To resolve, use a SOQL query for loop instead, since it can process multiple batches of records through the use of internal calls toqueryandqueryMore

 

Hope this helps.

This was selected as the best answer
SubC4i-dev1SubC4i-dev1

Thanks, that answers my question.  Both examples were listed in an Apex workbook as best practice, but I didn't see any reason why I wouldn't just use the For Loop version just to be safe all the time.

Katia HageKatia Hage

Btw, here is another topic from the Apex workbook explains the advantage of using SOQL For loops: http://www.salesforce.com/us/developer/docs/apex_workbook/Content/apex6_4.htm

 

Sometimes it might be easier to use the regular SOQL query (not in a loop) because you don't have to remember that it runs for every batches of 200. The Apex dev guide has a little more detail re. comparing SOQL queries with For SOQL loop queries: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm#soql_for_vs_soql

 

SubC4i-dev1SubC4i-dev1

Thanks Katia!  Always glad to get new resources to check out.

Shubh DShubh D
Thank you so much Harini! 
farukh sk hdfarukh sk hd
(soql for loop) retrives all sObjects using call to query and queryMore whereas 
(list for loop) retrives number of objects records.

It is advisable to use (soql for loop) over (list for loop) to avoid heap size limit error.
For more deatil visit,
https://www.sfdc-lightning.com/2018/10/soql-list-vs-soql-for-loop.html
Manish Kumar 442Manish Kumar 442

Case 1:-

List<Account> accountList = [SELECT Id, Name,Amount,Status FROM Account ];

Here accountList variable is holding 50,000(max) account record, if we say ,if each Record Is taking 2KB , the total Size will be 50,000 * 2 = 100,000 KB means approx 100 MB but allowed Limit is 6 MB (for synchronous Process) .Hence, a Variable  accountList is holding data of Size 10 MB.So , Error will be Encountered.

Case 2:-

For(Account acc: [Select id,Name,Amount,Status from Account]){
.....Business Logic...
}

Here also the the Soql query will return 50,000 (max) records. but the variable acc is holding one record at a time means 2 KB. So , Basically we are not hitting Heap Size governer Limit here.

So, to avoid Hitting Heap Size Limit , Always use SOQL for Loops

Note :- 

1.) Limits.getHeapSize() -         Returns the approximate amount of memory (in bytes) that has been used for the heap in the current context.
2.) Limits.getLimitHeapSize() -  Returns the total amount of memory (in bytes) that can be used for the heap in the current context.he limit on heap 3.) size.for synchronous heap size is 6 MB, for asynchronous it is 12 MB.
4.) Don't use class level variables to store a large amounts of data.
5.) Utilize SOQL For Loops to iterate and process data from large queries.
6.) Construct methods and loops that allow variables to go out of scope as soon as they are no longer needed.

 

 Concepts:-
1.) The single sObject format executes the for loop's <code_block> once per sObject record 
             for (Account acc : [SELECT Id FROM Account WHERE Name = 'Acme']) {}
2.) The sObject list format executes the for loop's <code_block> once per list of 200 sObjects. 
            for (Account[]  acc : [SELECT Id FROM Account WHERE Name = 'Acme']) {}