6

I have done my due diligence on this and simply cannot find an answer that does not involve Apex, which is what I'm trying to avoid for various reasons.

Does anyone know how to adequately without a ton of labor delete tens of millions of records in a (managed, custom) object in Salesforce? (specifically, it's an object from Exact Target / Marketing Cloud)

The closest I can think of based on my investigations are:

  1. SQLForce - seems highly dangerous (though perhaps no more so than any other solution)

  2. Excel Connector (I use it religiously) - I could write VBA to pull a group, delete a group, etc, but given that the table has over 100 million rows already, querying it is a problem (maybe SystemModStamp)

I haven't found anything adequate on AppExchange or via Google. I'm betting Apex is the only answer beyond SQLForce or an insanely time consuming routine like Excel Connector or Data Loading a list of the IDs and upload deletions that way. Am I missing something obvious??

Thanks.

UPDATE 2015-05-22 5pm EST - For the sake of continued due diligence, I've started a Data Loader deletion (Bulk API) of 1 million records to see the result. Fingers crossed! I will update again after the US Holiday.

UPDATE 2015-05-22 530pm - I am rather pleasantly surprised that it WORKED. Now for a real test (next week) of perhaps 10 million, and if that works, may as well go for the 150 million I need to expunge.

We have a specific Trigger that is overbearing, involving our Accounts and Opportunities, and such Bulk actions would be a joke to even consider usually, but either (a) since this is not directly related, or (b) perhaps due to the latest round of developers we've had who have touched up code here and there, maybe we're beyond that.

I will update again after the next test.

  • 2
    If you are trying to clear all records from a custom object, there is the truncate option in the UI. (help.salesforce.com/…) Probably not what you are looking for, but always useful. – Daniel Hoechst May 22 '15 at 21:22
  • (1) not all, and (2) not available for this (probably due to being managed?) :-/ But thanks. – AMM May 22 '15 at 21:35
  • 1
    You have to enable custom object truncation in Customize>User Interface. It works for managed package objects in my org. But, it is the nuclear option! – Daniel Hoechst May 22 '15 at 21:38
  • 1
    Nuclear, hah, yeah. We want to keep "30 days" of data on this object ongoing (now) as we mirror it (without deletion) to Postgres using CopyStorm (an AWESOME utility). SF will have the past month, PSQL will have all time. MicroStrategy will allow reporting on it (and everything else). Boom. ;-) We're only at 720% storage currently (cough). – AMM May 22 '15 at 21:39
  • 1
    Something to keep in mind... when you do a delete, all that is really happening is that the 'IsDeleted' flag is set on the record (which is why you can recover records from the recycle bin). The record still exists for up to 30 days. While your storage usage will drop, the records are still there. I mention this because I ran into an issue where I had 14M records in the recycle bin and my queries were still slow (because they were being checked). If you really want to get rid of the records, you need to do a 'Hard Delete'. – MarcDBehr May 23 '15 at 10:52
2

A previous organisation I worked had had a couple of child objects with over 250 million rows of data in.

When the time came to delete some of it we populated a custom indexed field on the relevent parent object records (eg all with a created date in 2013) with a sequential range of values, and then used Informatica to run a query to extract record ids from the child object for a range of values in the parent object custom field that equated to 100,000 child records and then hard delete them.

We then had a batch script to increase the range of values for the parent object and reran the extract/delete process. Informatica allowed us to schedule the job to run every 10 minutes, using this method we were able to delete 70 million records over a weekend.

0

Write a batch which will delete 10000 records per execution and logic should count the total no. of records , divide by 10000 and execute the batch that no. of times.

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.