2

I have a task to perform a fairly large and specific data removal.

The goal is to remove all EmailMessage objects that have a parent Case record whose status is closed, from BEFORE a certain date. The problem i think is that the inner query on Cases is returning a lot of records - around 60,000.

The query that i need the result for is this:

select id from emailmessage where parentid in (select id from case where status = 'closed' and closeddate < 2014-11-01T00:00:00.000Z)

Here are the methods that have tried with their resultant issues:

  1. Salesforce Developer Console. Query takes too long and times out.

  2. Data Loader command line interface. Not possible because this method allows for inner queries but does NOT allow for a date filter.

  3. Python API. This allows me to run the query successfully and in Sandbox environment with minimal data returns successfully. In production, it times out due to the large volume of data.

It would be great if i could split up the query by limiting the inner query, but the limit keyword is not allowed within an inner query.

Any ideas?

1

You can run your query starting from the Case side:

public class MyBatchable implements Database.Batchable<SObject> {

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([
                select Id, (select Id from EmailMessages)
                from Case
                where status = 'closed' and closeddate < 2014-11-01T00:00:00.000Z
                ]);
    }

    public void execute(Database.BatchableContext bc, List<Case> scope) {
        EmailMessage[] messages = new EmailMessage[] {};
        for (Case c : scope) {
            messages.addAll(c.EmailMessages);
        }
        delete messages;
    }

    public void finish(Database.BatchableContext bc) {
    }
}

and set the Database.executeBatch size so that less than 10,000 email messages are deleted per batch. (E.g. if you think you have less than 10 emails per case a batch size of 1000 would be OK.)

  • Thanks for the tip. I also hadn't considered using an Apex batch job - seems obvious now! – Robin Smith Nov 26 '14 at 18:36
1

You would want to use:

SELECT Id from EmailMessage 
WHERE ParentId IN 
    (SELECT Id 
     FROM Case 
     WHERE isClosed = TRUE 
          AND ClosedDate < 2014-11-01T00:00:00.000Z)
    AND Parent.Type = 'Case'

This is much better because Parent.Type is indexed and thus will speed up your query significantly.

The query plan button in the developer console is a great help for this as well. In the developer console, click help --> preferences and then select Enable Query Plan = TRUE. You can then on the Query Editor, input your SOQL and click the Query Plan button (instead of Execute) which will show if you are using indexes or completing a table scan.

  • Using the query plan tool is a good idea, though I would guess that restricting the outer query will have any impact on the 60,000 cases returned by the inner query. But worth a look. – Keith C Nov 26 '14 at 19:04
  • It shouldn't matter, the inner query is running on cases only and the requirement stipulated that we only cared about email messages associated with cases. It's why using Parent.Type will reduce the query significantly. – techbusinessman Nov 26 '14 at 19:10
  • Nevermind, I take back my last comment. You are correct it won't impact the query and drive below the 50K mark. – techbusinessman Nov 26 '14 at 19:11
0

I would give Jitterbit's free DataLoader a try, I don't have as many email message records in my org as you did, but I was able to successfully run your query in Jitterbit. Screen shots below:

Query Wizard Object Selection Query Wizard Query String with Test Results

Not sure you will need to limit this query though when using Jitterbit, might be able to get them all in one shot.
LIMIT Added to Query

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.