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
EdCodeEdCode 

What are the "benefit of SOQL for loop record batching in triggers"

Hello,

Here is a phrase (highlighed in red below) I do not understand in this Trailhead: Buld Apex Triggers.

User-added image
I totally do understand the benefits of bulkifying SOQLs and DMLs in apex triggers and/or apex Classes.

But, I think this phrase (forgive my poor English) is refeering to statements where you combine a for each loop and an SOQL ? Like below, in lines 5 and 6: 
trigger SoqlTriggerBulk on Account(after update) {  
    // Perform SOQL query once.    
    // Get the related opportunities for the accounts in this trigger,
    // and iterate over those records.
    for(Opportunity opp : [SELECT Id,Name,CloseDate FROM Opportunity
        WHERE AccountId IN :Trigger.New]) {
  
        // Do some other processing
    }
}
But, I do not quite understand the relationship between the fact that you can "unify" in one statement the for each and the SQL AND a potential "Benefit" to do this in terms of performance or efficiency.

What do you mean by "benefit of SOQL for loop"?

Thank you  very much.

 
Amit Chaudhary 8Amit Chaudhary 8
SOQL For Loop Formats
SOQL for loops can process records one at a time using a single sObject variable, or in batches of 200 sObjects at a time using an sObject list:
  • The single sObject format executes the for loop's <code_block> once per sObject record. Consequently, it is easy to understand and use, but is grossly inefficient if you want to use data manipulation language (DML) statements within the for loop body. Each DML statement ends up processing only one sObject at a time.
  • The sObject list format executes the for loop's <code_block> once per list of 200 sObjects. Consequently, it is a little more difficult to understand and use, but is the optimal choice if you need to use DML statements within the for loop body. Each DML statement can bulk process a list of sObjects at a time.

For example, the following code illustrates the difference between the two types of SOQL query for loops:
  
// Create a savepoint because the data should not be committed to the database
Savepoint sp = Database.setSavepoint(); 

insert new Account[]{new Account(Name = 'yyy'), 
                     new Account(Name = 'yyy'), 
                     new Account(Name = 'yyy')};

// The single sObject format executes the for loop once per returned record
Integer i = 0;
for (Account tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
    i++;
}
System.assert(i == 3); // Since there were three accounts named 'yyy' in the
                       // database, the loop executed three times

// The sObject list format executes the for loop once per returned batch
// of records
i = 0;
Integer j;
for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
    j = tmp.size();
    i++;
}
System.assert(j == 3); // The list should have contained the three accounts
                       // named 'yyy'
System.assert(i == 1); // Since a single batch can hold up to 200 records and,
                       // only three records should have been returned, the 
                       // loop should have executed only once

// Revert the database to the original state
Database.rollback(sp);
Please check below post for more infor
1) https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm


Let us know if this will help you