Wednesday, October 3, 2012

For loops and SOQL queries

One of my favorite loops in Apex is the for(collection) loop. It's very useful for iterating through a collection. It allows you to do something like:
    // Fix Asset records with null Product2Id fields.
    List<Asset> assets = [SELECT Id, Product_Family__c, Product2Id
                          FROM Asset
                          WHERE Product2Id = null
                         ];
    for(Asset asset : assets)
    {
        // fix asset.Product2Id field here
    }
    update assets;
As useful as this is, it turns out that there is a better way. If you use the for([SOQL]) loop instead, Apex will actually run the SOQL query and automatically querymore each time the loop recycles. This means that you don't have to generate a complete collection and use up the heap space to store the entire query result set. This version looks like:
    // Fix Asset records with null Product2Id fields.
    List<Asset> updatedAssets = new List<Asset>();
    for(Asset asset : [SELECT Id, Product_Family__c,Product2Id
                       FROM Asset
                       WHERE Product2Id = null
                      ])
    {
        // fix asset.Product2Id here
        updatedAssets.add(asset);
    }
    update updatedAssets;
I'm not sure how to bulkify the update without maintaining a collection of records, which sort of negates the benefits (in cases like this were all records get updated anyway) of using this form of loop. But in a case where not all records would be updated this form could save considerable heap space. I didn't try it but it appears that there is another way to perform this operation:
    // Fix Asset records with null Product2Id fields.
    for(Asset[] assets : [SELECT Id, Product_Family__c,Product2Id
                          FROM Asset
                          WHERE Product2Id = null
                         ])
    {
        for(Asset asset : assets)
        {
            // fix asset.Product2Id here
        }
        update assets;
    }
This reduces the heap used to store the entire DML update at the expense of the number of DML operations performed. It will update records 200-at-a-time but only 200 record will be put onto the heap at a time. The choice of which form to use is probably situationally dependent, but knowing the options obviously is the biggest part of the battle.

No comments:

Post a Comment