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() );        

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?



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.

  • 1
    Aha, now as it happens our app already creates an external id field on the Account so assuming that isn't used, this might be the answer. Thanks for the idea! – Phil Hawthorn Feb 6 '13 at 15:19
  • Excellent, as it happens this field is free so I can use it. Actually, thinking about it I can select all Accounts where this field is blank and simply abort after the 4 hours. Thanks! – Phil Hawthorn Feb 6 '13 at 15:36

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.

  • Yes, thanks for the idea @JesseAltman - i doubt there is a perfect solution to this. – Phil Hawthorn Feb 6 '13 at 15:07

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?

  • 4
    2.2 million Id's appended to a custom setting? A single custom setting won't hold it. And custom settings have a maximum storage capacity of something like 10mb since they are cached. I considered this too, but I don't think it will work. – pchittum Feb 6 '13 at 15:12
  • That was my initial thought as well. I think holding millions of IDs in the list would be too much. It is a really good idea though that would work well in a smaller scenario. – Jesse Altman Feb 6 '13 at 15:14
  • Agreed...this kind of thing is one of the great use cases for custom settings. The scale of this problem outstrips the custom settings limits, unfortunately. – pchittum Feb 6 '13 at 15:20
  • Good points - I didn't think about storing the entire lot, merely storing a subset. Perhaps he could do the inverse and when aborting the job store the Id's of those not yet processed. May resolve the problem. I think the above solutions from Jesse and Peter are probably better in the long run although I have a query. What about the case where we have 2 batches running at once that get cancelled? What affect will this have on Phil's next run? Could certain items be missed? Merely playing devil's advocate now. – pbattisson Feb 6 '13 at 15:26
  • I should have mentioned this is a one off job, I will only run one at a time over 3 nights (basically to fix some data on Account) – Phil Hawthorn Feb 6 '13 at 15:29

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.