Have you ever wonder how to mass delete big object records effectively? Well, consider yourself lucky if you come across this post because I am going to show you how to do it. Without further ado, let's get into it!

Can I use Data Loader to mass delete?

Well, the quick answer is no, you can't. Let me walk you through so you can save your time trying to do so:

1. Select big object (you might need to check the "Show all Salesforce objects" to find your big object)
   

2. Map the field (hmm strange... where are the field mappings? You will find out soon 😉)
   

3. Perform delete action (dangg... big object is not supported!)
   
   If you didn't enable Bulk API in your Data Loader Settings, you might probably see this (I have to abort it because all the records are failed to delete)
   

---

How can I mass delete big object records anyway?

Here's what I do. The only way to delete big object records is via deleteImmediate API.

But... but there are so many records and how am I supposed to filter out the records that I need to delete?

Well, if you have set up the index on the big object that allows you to mass delete the records without applying more filters, then good for you! However, if you have to do some filters on the records to be deleted, loading all records and apply filters via if/else statements might not work well especially when we are talking about hundred thousands or even millions of records. As you know SOQL query limit, the maximum rows that you can query is 50,000 rows.

---

Anyway, I know you are so eager to see the solution, let's check out the code below:

ContentVersion cv = [SELECT id, VersionData 
                  FROM ContentVersion WHERE ContentDocumentId = 'the_content_document_id' AND IsLatest = true
                 ];

List<Custom_Big_Object__b> bList = new List<Custom_Big_Object__b>();

String contentDataAsString = cv.VersionData.toString();
String[] contentDataLines = new String[] {};
contentDataLines = contentDataAsString.split('\n');

for(String s : contentDataLines) {
    String [] sArr = s.split(',');
    bList.add(
        new Custom_Big_Object__b(
            Custom_Index_1__c = sArr[0],
            Custom_Index_2__c = sArr[1],
            Custom_Index_3__c = sArr[2] // depend on how many index you have defined in big object
        )
    );
}

if(bList.size() > 0) Database.deleteImmediate(bList);

Let me explain the code. the_content_document_id will be the .csv that you uploaded to Files.

You can grab the the_content_document_id in the url link (18 characters).

What the code does is that it loads the .csv file and extract the content into a string, then loop through the lines, extract the field values and load into big object list which will be deleted via deleteImmediate API.

Here are some errors that might occur:

1. Query of LOD fields caused heap usage to exceed limit
   
   This error happens when the .csv that you load is too large. Try to reduce the file size and try again.

2. Regex too complicated
   
   This is an issue with the split function. You can create an Iterator class to overcome this. Here's the code:

// code retrieved from https://salesforce.stackexchange.com/questions/85048/catch-regex-too-complicated-when-splitting-a-string-in-salesforce
public with sharing class Utility_RowIterator implements Iterator<String>, Iterable<String> {
   private String m_Data;
   private Integer m_index = 0;
   private String m_rowDelimiter = '\n';

   public Utility_RowIterator(String fileData) {
      m_Data = fileData; 
   }
   
   public Utility_RowIterator(String fileData, String rowDelimiter) {
      m_Data = fileData; 
      m_rowDelimiter = rowDelimiter;
   }

   public Boolean hasNext() {
      return m_index < m_Data.length() ? true : false;
   }
   
   public String next() {     
      Integer key = m_Data.indexOf(m_rowDelimiter, m_index);
      if (key == -1) key = m_Data.length();
      String row = m_Data.subString(m_index, key);
      m_index = key + 1;
      return row;
   }
   
   public Iterator<String> Iterator() {
      return this;   
   }
}

Full version of the code

Now, we can transform our code so it can load properly. Here's the full version of the code:

ContentVersion cv = [SELECT id, VersionData 
                  FROM ContentVersion WHERE ContentDocumentId = 'the_content_document_id' AND IsLatest = true
                 ];

List<Custom_Big_Object__b> bList = new List<Custom_Big_Object__b>();

String contentDataAsString = cv.VersionData.toString();

Utility_RowIterator r = new Utility_RowIterator(contentDataAsString,'\n'); //Replace \n with whatever delineates your row

while(r.hasNext()){
    String s = r.next();
    String [] sArr = s.split(',');
    bList.add(
        new Custom_Big_Object__b(
            Custom_Index_1__c = sArr[0],
            Custom_Index_2__c = sArr[1],
            Custom_Index_3__c = sArr[2] // depend on how many index you have defined in big object
        )
    );
}

if(bList.size() > 0) Database.deleteImmediate(bList);

Please bear in mind that the process of delete is taking way too long to a point that you think the system hangs. Trust me, from my experience, you just got to give it some time, it will come around! Here's some stats that you can reference:

Deleting 1000 records takes roughly 6 seconds.
Deleting 4000 records takes roughly 20 seconds.
Deleting 10000 records takes roughly 2 minutes and 18 seconds.
Deleting 40000 records takes roughly 28 minutes and 25 seconds!

The last one is taking almost 30 minutes! WHY??

Well, I have found a way to speed up the process which is not to delete all the records all at once!

For example, when I delete 10000 records with 1000 records per batch, it works pretty good.

    if(bList.size() > 1000){
        Database.deleteImmediate(bList);
        bList.clear();
    }

It only takes 43 seconds to delete 10000 records! You can fine-tune which would work out the best for you, if you want to let batch apex to take care of the deletion, it's fine too.

However, you might need to beware of Apex CPU time limit exceeded, so you still need to fine-tune to maximize your output:

FYI, my big object has set up a total of 3 indexes, I'm not sure if that will affect the deletion efficiency.

Since I am loading around 50000 records from csv file, and I have about 700k records to be deleted, that would be 14 csv files to load. I guess it will be a long long week for me...

---

Well, I hope this post helps! I also hope Salesforce can work on Bulk API on big objects as well. See you in next post! Ciaoz!

Post was published on 24 Jul 2020, last updated on 24 Jul 2020.

Like the content? Support the author by paypal.me!