Apex - how to batch update a 'ranking' on all accounts

Question

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) {

}   
}

Answer 1

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.

Answer 2

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 {
}