0

I have a custom object called exception__c and a child object called exception_occurrence__c. There are more than 5000 records in exception object and each exception has more than 25000 child records (exception_occurrence__c). There is a field called date_of_occurrence__c in the exception_occurrence__c object. Now based on the date of occurrence, i want only, first 250 and last 250, 500 records of exception occurrence for each exception. Business use case is that almost all of the exception occurrences will be identical, so that i need to purge them and bring it down to 500.

Please find the code that i have written for this. this is a one time operation so i don't want to push any code to production. i just want to run this script in anonymous window.

But when i run the below code i get 50001 SOQL rows error and if i limit the expList to 1 then i get 10001 DML rows error.

Note: Number_of_Occurrences__c is a field in exception__c which is a rollup summary of the child object exception_occurrence__c

List<Exception__c> expList = [Select id, Number_of_Occurrences__c from Exception__c where Number_of_Occurrences__c > 500 and Status__c IN ('Expired','Rejected','Closed') order by Number_of_Occurrences__c ASC Limit 15];

Set<Id> expIds = new Set<Id>();
for (Exception__c exp : expList) {
    expIds.add(exp.Id);
}

if (null != expIds){
    for (Id expid : expIds) {
        List<Exception_Occurrence__c> expOccurrence = [Select Id, date_of_occurrence__c from Exception_Occurrence__c where exception__c = :expid order by date_of_occurrence__c asc];
        if (null != expOccurrence) {
            List<Exception_Occurrence__c> toDelete = new List<Exception_Occurrence__c>();
            Integer total = expOccurrence.size();
            integer startValue = 250;
            integer endValue = total - 251;
            for (integer i = startValue; i <= endValue; i++) {
                toDelete.add(expOccurrence[i]);
            }
            if(toDelete.size() > 10000) {
                for(Integer i = 0 ; i < (toDelete.size() / 10000)+1 ; i++){
                    List<Exception_Occurrence__c> lstTemp = new List<Exception_Occurrence__c>();
                    for(Integer j=(i*10000);(j<(i*10000)+10000) && j<toDelete.size() ; j++){
                        lstTemp.add(toDelete.get(j));
                    }
                    delete lstTemp;
                }
            } else {
                delete toDelete;
            }
        }
    }
}

Please let me know an efficient way of doing it without using batch apex.

1

There's no way you're going to be able to do this in Execute Anonymous. The data volume is simply too large. If your numbers are correct, you have 5,000 Exception records, each with 25,000 Occurrences. That's 125 million records, out of which you want to keep 2.5 million. You can only delete 10,000 records per transaction, so even if you wanted to rewrite your code to take the limits into account, you'd be hitting Run something like 12,250 times.

Even if that's off by an order of magnitude, this is not a problem you can solve with Execute Anonymous. If it's off by several orders of magnitude, you could potentially tweak your existing script with some LIMIT clauses and run the whole thing several times. (The limit's 10,000 rows per transaction, not per DML statement).

You have only two genuine options that I can see:

  1. Write a Batch Apex class very carefully to be able to handle this data volume. It will be tricky to write, take a long time to run, and will be difficult to test effectively, but it absolutely should be tested in a full copy sandbox.
  2. Extract the data from Salesforce and manipulate it in some external data store to identify the records to delete. I don't believe Microsoft Excel or LibreOffice can handle this data volume; you'd need to be using some kind of database product. Then, use the Bulk API in Hard Delete mode to delete all of the records to be removed.

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.