8 Ultimate Guide to Mastering SOQL for Loop in Apex for Better Performance

Mastering SOQL Loops in Apex – The Smart Way to Handle Large Data

Mastering SOQL Loops in Apex – The Smart Way to Handle Large Data

Query large volume records may hit runtime exception lets understand how we can handle this via SOQL for loop.

As we know some time we exceed governor limit of exceeding heap size since we can query 50,000 records by SOQL query.

If records are more then it will hit the governor limit i.e exceed heap limit.

As pe the below code it may causes a runtime exception if records volume are more than 50 thousand.

A runtime exception is thrown if this query result are large i.e exceed heap limit:

Without For SOQL query loop Example: Lets query large datasets via standard SOQL

Case [] lstCases = [SELECT id FROM Case];

Instead of above we can utilise For Loop SOQL query that will avoid to hit heap size limit.

SOQL for loops iterate over all of the sObject records returned by a SOQL query.

Syntax for SOQL query loop:

for (variable : [soql_query]) {
    code_block
}

Or

for (variable_list : [soql_query]) {
    code_block
}

With For SOQL query loop Example: Lets query large data sets via For SOQL Loop

Case [] lstCases = new Case[];

for (Case objCase : [SELECT Id, Subject FROM Case ]) {

    // Add all the cases into list

    lstCases.add(objCase);

}

Force.com platform chunk our large query results into batches of 200 records by using above code where the SOQL query is in the for loop definition, and then handle the individual datasets in the for loop logic.

SOQL For Loops Versus Standard SOQL Queries :

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects.

While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records,

SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. 

Its always prefer to use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

SOQL for loop formats :

As we know that 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. Here are the two main points to remember :

• The single sObject format executes the for loop’s <code_block> one time per sObject record.
• The sObject list format executes the for loop’s <code_block> one time per list of 200 sObjects.

SOQL for Loop with Type:

You can also iterate over sObjects dynamically:

for (SObject sobj : [SELECT Id, Name FROM Account]) {

System.debug(sobj.get('Name'));

}

Important Note:

Here ae the few important points to remember in while querying large dataset i.e SOQL for loop

1. The break and continue keywords can be used in both types of inline query for loop formats. When using the sObject list format, continue skips to the next list of sObjects.
2. The break and continue keywords can be used in both types of inline query for loop formats.
   • When using the sObject list format, continue skips to the next list of sObjects. DML statements can only process up to 10,000 records at a time, and sObject list for loops process records in batches of 200.
3. You may get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assingment, use FOR loop.

Why SOQL For Loop is Better

• Avoids heap size limit (because it processes in batches)
• Reduces governor limit issues (like query row limits)
• Recommended for bulk processing in triggers or batch Apex

When to Use SOQL For Loop

• Avoiding Heap Size Limits
• Processing large datasets
• Triggers with bulk data
• Batch Apex or Scheduled Jobs

More detail about soql for loop click here.

Also Learn more on Salesforce Developer Docs

Thank you for the reading, please feel free to share your thought.