If I Use a Loan Pattern is Salesforce smart enough to do a SOQL for loop?

Question

As we know, Salesforce does some magic when you do something like:

for (List<Account> accountList: [SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%']) {
  // do stuff
}

... such that the code will run more efficiently with higher limits.

and we can do this dynamically like:

for(MyObject__c obj : Database.Query('select Id, Name from MyObject__c'))
{
  // do stuff
}

I like to use a the Selector pattern, so I'd rather have the details of the query within my Selector.

So, I'm wondering whether Salesforce would be apply this efficency if I have a method like:

    public XAP_DAO_SOQL_SObjectSelectorAbs with(
            XAP_DAO_SOQL_QueryFactory queryFactory,
            XAP_DAO_SOQL_SObjectConsumerIntf consumer
    ) {
        for (SObject sobj : queryFactory.query()) {
            consumer.doWith(sobj);
        }
        return this;
    }

... where queryFactory.query() returns an instance of List<SObject> which has been provided by:

    public List<SObject> selectFor(XAP_DAO_SOQL_QueryInterpolation queryInterpolation) {
        return Database.query(
                this.toSoql(queryInterpolation)
        );
    }

... Or does Salesforce require either [ SELECT ... ] or Database.Query(' SELECT ... ') to be exactly after the : in the for loop to get the benefit?

Answer 1

Agreed with sfdcfox that returning a List<SObject> will materialize the whole query first rather than let you do a proper SOQL FOR loop.

One alternative is, you could have your factory return Database.getQueryLocator(query) which is of type Database.queryLocator. Then you can call .iterator() on that locator to return an Iterator<SObject>. Now you iterate it like this:

while(iter.hasNext()){
    SObject so = iter.next(); // or cast it to a specific SObject type
    // do whatever work here
}

This has functionally the same effect as a SOQL FOR loop. In my experience the way it works is, it fetches the first 200 records under the hood and returns them to you one by one. If there are more after that 200, it fetches the next set, etc. So the query work is spread more evenly across the transaction and the heap is used more efficiently compared to querying the whole thing first.

Answer 2

Yes, Salesforce requires either an inline query or a direct call to Database.query.

When returning a value from a function call, all the results will be retrieved before the values can be used, similar to:

Account[] records = Database.query('select id from account');
for(Account record: records);

There's no way around this restriction aside from actually returning the SOQL and calling it directly:

public List<SObject> selectForQuery(XAP_DAO_SOQL_QueryInterpolation queryInterpolation) {
    return this.toSoql(queryInterpolation);
}

Which your loop can then call:

for (SObject[] sobj : Database.query(queryFactory.getSOQL())) {

Calling a method forces all the results to be retrieved, which eliminates the possibility of using this construct.