Better for working with filtered DataSets: SOQL or FOR Loops

Question

I have an Apex class where I have to grab a Dataset of custom objects and then perform some checks on various subsets of that data. I was wondering what would be the most optimized way of handling this:

A. Pull each subset from a separate SOQL query? i.e.

Foo[] barFoos = [select Id, And, Some, Other, Fields from Foo where Something = 'bar' and ParentField = 'someParent'];
Foo[] buzzFoos = [select Id, And, Some, Other, Fields from Foo where SomethingElse = 'buzz' and ParentField = 'someParent'];

B. Pull the main subset and iterate through it via a series of separate For Loops? i.e.

Foo[] foos = [select Id, And, Some, Other, Fields from Foo where ParentField = 'someParent'];
for (Foo f : foos) {
    if(foo.Something == 'bar) do something;
}
for (Foo f : foos) {
    if(foo.SomethingElse == 'buzz) do something else;
}

Answer 1

Given the two options, you will usually want option B, as it uses fewer queries and may return fewer rows if Condition1 and Condition2 both apply to a single record; you have a much lower limit for queries than you do other limits, except for DML statements.

However, sometimes you'll be forced to use option A, such as the query becoming so complex that the system either raises an error (perhaps a selectivity error on a large object), or because you end up using far more CPU time with multiple if statements. The best option will usually be dictated by a balance between being able to read the code and minimizing governor limit usage.

As an aside, if you're going to use option A, try to descope the query results as soon as possible:

{
    Foo__c[] records = [SELECT ... FROM Foo__c WHERE Condition1];
    for(Foo__c record: records) {
        // Do Something
    }
}
{
    Foo__c[] records = [SELECT ... FROM Foo__c WHERE Condition2];
    for(Foo__c record: records) {
        // Do Something Else
    }
}

Even better, use inline queries directly in the loop statement, as this reduces heap usage on large queries. Using option B, you can gain quite a bit of performance by inlining the query:

for(Foo__c record:[SELECT ... FROM Foo__c WHERE Condition1 OR Condition2]) {
    if(record == Condition1) {
        // Do Something
    }
    if(record == Condition2) {
       // Do Something Else
    }
}

This has the distinct advantage of using less heap space, as the query will be internally chunked using query/queryMore. This will make a significant difference as the number of records returned by the query increases, especially as compared to option A if many of those records meet both Condition1 and Condition2.

For example, if the net number of rows is 25,000, both option A and B, as described, will need to store 25,000 records in memory. Using an inline query reduces the heap usage to just 200 records at a time.

Answer 2

I think the only true answer here is "it depends." Much of Apex development is balancing the different kinds of governor limits, and trading them against each other when there is a danger of hitting one.

For instance, if you were in danger of breaking the SOQL query execution context cap, you would want to use B and consume only one query instead of two.

As a converse example - if you had no worries about hitting the SOQL query governor limit, and this was a piece of some massive process in danger of hitting the runtime cap (unlikely) - you would want to use A.

However, for what I think you are asking, A would almost certainly be "more optimized" in terms of execution speed - but, as Adrian noted, the query limit is most often reached, the runtime cap is extremely generous, and the difference between A and B in terms of time to completion would be miniscule, so most would use B.