Loop with SOQL or List, Which is Better?

Question:

When working with Salesforce Apex, is it better to use a SOQL query directly within a for loop, or to query the records into a list first and then iterate through that list? Considerations include performance, memory usage, and specific use cases.

Here are two examples of the approaches being compared:

// Approach 1: SOQL directly within the loop
for (Account a : [SELECT Id FROM Account WHERE Id IN :mapAccount.keySet()]) {
    // Perform actions on each Account record
}

// Approach 2: Query into a list, then loop
List<Account> listAccount = [SELECT Id FROM Account WHERE Id IN :mapAccount.keySet()];
for (Account a : listAccount) {
    // Perform actions on each Account record
}

Answer:

Both approaches are valid and have specific scenarios where they perform better. The choice largely depends on the context, such as memory availability, CPU usage, and the intended purpose of the query results. Below are the detailed explanations for each.

CRS Info Solutions provides expert Salesforce training in Nagpur with real-world projects, certification support, and career-focused guidance.

1. SOQL in the for loop:

This method is particularly useful when memory is limited. Records are loaded into memory lazily, one at a time, during iteration. This reduces memory consumption and helps avoid heap size limits, especially when dealing with large datasets.

For example, if you are processing records in small batches and only within the scope of the loop, this approach can prevent memory overhead:

for (Attachment record : [SELECT Body FROM Attachment]) {
    record.Body = encrypt(record.Body); // Process each record
    update record; // Update one record at a time
}

Code explanation:
The given code snippet demonstrates processing Attachment records one at a time using a SOQL for loop. Each record’s Body field is encrypted using a method encrypt(), and the record is immediately updated with a DML operation. This approach minimizes memory usage by processing records lazily, but it increases the number of DML statements, which can risk hitting governor limits if many records are processed.

This approach also allows you to abort early in certain scenarios, saving SOQL row limits.
For example:

for (Account record : [SELECT Name FROM Account LIMIT 10000 ORDER BY Name ASC]) {
    if (record.Name == 'Breaker') {
        break; // Stop processing early
    }
}

Code explanation:
The code iterates through a query that retrieves up to 10,000 Account records ordered by their Name in ascending order. During the loop, it checks each record’s Name field, and if a record with the name “Breaker” is found, the loop terminates early using the break statement. This approach can optimize resource usage by processing only as many records as needed before stopping.

2.Query into a list, then loop:

This approach is advantageous when CPU time is critical. Retrieving all records at once into a list reduces the overhead of lazy loading in the for loop, making the subsequent iteration faster. Additionally, this approach is more suitable when the queried records need to be used outside the loop for further processing.

For example:

List<Account> accountsToUpdate = [SELECT Name FROM Account LIMIT 10000];
for (Account record : accountsToUpdate) {
    record.Name = record.Name.toUpperCase(); // Update each record
}
update accountsToUpdate; // Perform a single DML update

Code explanation:
This code retrieves up to 10,000 Account records into a list using a SOQL query. It iterates over each record in the list, converting the Name field to uppercase. Finally, it performs a single DML update on the modified list, updating all records in one efficient operation.

Key Takeaways:

• Use SOQL in a for loop when memory is a concern or when you don’t need the records outside the loop. This is also useful for processing records one at a time or when the loop might exit early.
  
• Use list assignment with a subsequent loop when CPU time is a priority or when the records need to be used outside the loop. This is generally faster for batch updates or complex processing.

In practice, the performance difference is often negligible for small datasets, so choose the approach that is clearer and more maintainable for your use case. Proper unit testing can reveal exceptions to these guidelines and help you optimize your code further.

Summing Up:

When deciding between looping with SOQL or querying into a list first, the choice depends on the context. Use SOQL within a for loop to conserve memory and handle records one at a time, ideal for large datasets or early exits. Querying into a list is faster for CPU usage and simplifies batch updates or when records need reuse outside the loop. Both approaches are valuable, and the right choice ensures better performance and maintainable code.

Transform Your Career with Salesforce Training in Nagpur

Embark on a career-transforming journey with our Salesforce training in Nagpur . Designed to offer expert certification guidance, in-depth interview preparation, and comprehensive modules, we cover key tracks such as Admin, Developer, and AI. Our training includes detailed class notes and hands-on learning to ensure you master the skills needed to excel in the Salesforce ecosystem. Stand out in the competitive job market with the expertise gained from our program.

Join us for an unparalleled learning experience tailored to your career goals. With industry-relevant projects, expert-led sessions, and personalized mentorship, our program equips you to succeed in real-world scenarios. Take the first step toward your dream job by attending our free demo class. Start your journey today and discover how Salesforce training in Nagpur can shape your future!

Join us today for a free demo session and take the first step towards a rewarding Salesforce career!!!