Apex Code Development You need to sign in to do that
Don't have an account?
SubC4i-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!
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
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.
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.
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
Thanks Katia! Always glad to get new resources to check out.
(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
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']) {}