4

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?

  • 1
    I have to argue with you here, on the "As we know, Salesforce does some magic when you do something like:" point. SOQL FOR loops are more efficient sometimes than iterating the SOQL result, yes. But putting an update statement inside the loops is not. SF will never do any "magic" to make that more efficient for you. – Charles T Nov 14 '20 at 16:48
  • Sorry, 'update' was from a bad example I copied from somewhere (I think it was actually a Salesforce doc page!) ... but the point is not about update but rather the syntax of for(Sobject sObj: [SELECT ..]) – Brian Kessler Nov 14 '20 at 17:35
  • Yes, it was a Salesforce developer page: developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – Brian Kessler Nov 14 '20 at 17:41
7

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.

  • this has the advantage of preserving one of the biggest pluses of a selector pattern -- mocking the selector's return value in testmethods – cropredy Nov 16 '20 at 2:27
  • 1
    Indeed. You can return the iterator of a static List instead of the iterator of a query. – Charles T Nov 17 '20 at 13:40
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.