1

For a simplified example, assume that all our accounts have a ranking based on revenue. Every month I want to update these rankings based on new accounts that were added. Assume we have 50,000 accounts.

The logic is pretty simple, create a list of all the accounts, order by revenue DESC, loop through the list and use the index as the ranking.

However, I'm hung up on doing this at a large scale. It can't be done through a trigger since it gives me a 10,001 DML error. I've tried Apex Batch Update, but that seems to only load 200 records at a time before applying the above logic. However, I need to run the logic on all the records at once. I'm not very familiar with Batch Apex so maybe there is a different way to load the data.

How can this be done?

Thanks

Below is my existing code.

global class BatchTierUpdate implements Database.Batchable<sObject> {
global final String Query;
global BatchTierUpdate()
{
Query = 'SELECT Id FROM Account Order by Revenue__c';
}

global Database.QueryLocator start(Database.BatchableContext BC) {

    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<Account> scope) {

    Integer i = 1;

    For (Account ac : scope) {
        ac.Rank__c = i;
        i = i + 1;
    }
    update scope;
}

global void finish(Database.BatchableContext BC) {

}   
}
3

What you need here is to implement Database.Stateful on your class to retain the index between calls to the execute method, without this the index will reset each time Batch Apex passes the job the next chunk of 200 records (which is the default chunk/scope size via Database.executeBatch).

Using State in Batch Apex

Each execution of a batch Apex job is considered a discrete transaction. For example, a batch Apex job that contains 1,000 records and is executed without the optional scope parameter is considered five transactions of 200 records each.

If you specify Database.Stateful in the class definition, you can maintain state across these transactions. When using Database.Stateful, only instance member variables retain their values between transactions. Static member variables don’t and are reset between transactions. Maintaining state is useful for counting or summarizing records as they're processed. For example, suppose your job processed opportunity records. You could define a method in execute to aggregate totals of the opportunity amounts as they were processed.

If you move your Integer out as a class member this will retain its state between calls, like so.

public class BatchTierUpdate implements Database.Batchable<sObject>
   implements Database.Stateful
{
    public final String Query;
    public Integer rankIndex;

    public BatchTierUpdate()
    {
        Query = 'SELECT Id FROM Account Order by Revenue__c';
        rankIndex = 1;
    }

    public Database.QueryLocator start(Database.BatchableContext BC) 
    {   
        return Database.getQueryLocator(query);
    }

    public void execute(Database.BatchableContext BC, List<Account> scope) 
    {   
        for (Account ac : scope) 
        {
            ac.Rank__c = i;
            rankIndex = rankIndex + 1;
        }
        update scope;
    }

    public void finish(Database.BatchableContext BC) { }    
}

Note: That i have also removed the global and used public, the documentation still shows this, as in the past it was a requirement, these days it is not, unless you want to expose this class to callers outside a managed package.

  • So my actual problem is more complex and requires knowing the # of accounts. Essentially I want to assign all the accounts into 3 tiers, based on revenue. For example, I'd want to load all accounts and order by revenue DESC, get the size of the list, find the first third, and assign the accounts a tier based on the index (1st, 2nd, 3rd). I'm assuming that calling scope.size() in the execute statement only loads the current batch of 200 records. It's not clear to me where I would execute this logic. Would it still be possible with this method? Thanks! – user91240192094 Apr 4 '14 at 2:48
  • Thanks, i understand, yes this should still be possible, providing you can reliably count the total accounts first. It seems David has taken up this latest additional part of your question in his answer below, check it out. The only concern i would have is counting the number of Accounts, you must make sure the WHERE clause used keeps the selected records under 50,000 as this is the limit even when using the Aggregate functions. – Andrew Fawcett Apr 4 '14 at 9:13
0

In the start method, or in the constructor, you can do a [SELECT Count() FROM Account WHERE ... ORDER BY AnnualRevenue DESC] query, which will return an integer. That is your total number of records. Assign that to an instance integer. You could even assign it to a final integer.

Divide that number by 3 and store that number in a stateful integer (we'll call it oneThird). Make a third integer set to 0, which is your counter. You'll need three integers - one for each tier.

As you go through your batch scopes, increment your counter for each record, until it equals oneThird. Then set that tier's number to the running total, reset your counter to 0 and go again.

Bottom line is that you can use the same WHERE clause and just do a count() query to get the number of records - and I have done it in the constructor and the start method, each with identical results.

Oh, and my style is to do the header of the batch class like this:

public class BatchTierUpdate implements Database.Batchable<sObject>, Database.Stateful {
}

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.