Updating a large data set in a limited processing window

Question

I don't know if I can't see the wood for the trees on this one, but I need some help.

• I have ~2,200,000 Accounts
• I have to update every one of them (some fairly complex logic involved)
• I have to do it overnight, in a 4 hour processing window
• The whole process takes ~10 hours

So, I set about implementing a 'self abort' in the execute method to prevent it processing for more than 4 hours, e.g.

if( System.NOW() > startTime.addMinutes( 240 ) )
{
    // store lastProcessedId
    CustomSetting__c settings = [select LastAccountProcessed__c From CustomSetting__c];
    settings.LastAccountProcessed__c = lastProcessed;
    update settings;

    // abort
    System.abortJob( BC.getJobId() );        
    return;
}

All good so far.

However, I can't think of a good way to mark the last processed account in order to start the process again the following night from the correct place. I did think about using Id and then adding :

Where Id > :lastProcessed

but you can only use the = operator with IDs. I would like to avoid adding a custom checkbox to the Account.

Has anyone done anything similar, if so what approach did you take?

Thanks!

Answer 1

I can't see any option that doesn't involve storing some custom data in the Account object. Options I see:

1. The checkbox. I won't dwell there as you've decided that isn't your first choice.
2. Custom date/time stamp field. Have a field only updated by your process, and then when the processing is complete on the last day, run a batch to wipe it. You could even use your own special chosen far future date (01/01/2265) so as to ensure you always know it is your value.
3. Custom string field set as ExternalId that stores the 15-digit record id. Now you can use an order by, and a > operator (maybe...don't know really if we allow that on string fields, but worth a shot). You'll have to do some data cleaning to duplicate the account Id in your new field for all 2.2mil records, but once you do that, put a workflow rule in place so that all future account records do this automatically on insert.

External Id is critical on the last option since it adds an index to the field.

Answer 2

This is just a thought. How about setting up your process so that it orders your accounts by CreatedDate? Save the start time in a Custom Setting. Process the records by that CreatedDate until your 4 hours are up and then save that CreatedDate and the current date in the same custom setting.

The next night when it kicks off you can filter your accounts by starting after or equal to that CreatedDate and LastModifiedDate not between the start and end date of the previous run through.

Now, you may get overlap (for instance, you updated a record between that time on that same exact CreatedDate, but the LastModifiedDate is now set later because a user updated that account between the time you last ran the script and now), so you should account for that in the script. However, I think the overlap in this scenario would be very minimal.

This may not be a perfect solution, but hopefully it helps get an idea flowing.

Answer 3

You could have the custom setting as a list type, append the ids to this as you complete them and then when you do your query only query those not in that list?