1

We have an object with approximately 1.5 million records in Salesforce. These records are created in an external system and synced with Salesforce every 24 hours. On a 24 hour basis, we delete all 1.5 million record rows and reimport them. The problem we're having is that the object has suddenly stopped being queryable. Performing a SELECT COUNT() operation in the developer console returns the error message "No response returned from server". Performing the same query via the tooling API returns the error: "QUERY_TIMEOUT: Your query request was running for too long".

I wasn't able to come away with much from a google search - has anyone encountered this before and what was your solution? We have approximately 3 million of these records in our recycle bin and I'm wondering if that may be related to the issue...

As an alternative solution, I've written an apex queueable that deletes 10,000 rows at a time until there are no results left. Oddly enough, this executes fine until the final 10-20,000 records are queried, in which case it crashes.

Could it be that a single bad record is causing this entire issue?

1 Answer 1

3

First, a full recycle bin can cause problems, because IsDeleted is not indexed. Consider hard-deleting records as you delete them with the emptyRecycleBin function. I suspect the Queueable is probably running into similar problems because you're stacking the recycle bin full of data (it's meant to handle only a few thousand records per user).

Second, consider a smarter integration strategy. Deleting the entire database every day just to regenerate is wasteful and unnecessary. I'd recommend a mark-and-sweep type approach. Set a flag on every single record, then update all records using upsert commands (while unmarking in the process), then finally delete every marked record.

If your external system could track modifications and deletions, that'd be even better, as you won't be writing records constantly that don't need to be written.

3
  • Thanks sfdcfox! As helpful as ever. I'll go ahead and edit the queueable to do a hard delete via Database.emptyRecycleBin(sObject[]) and see if that resolves the issue. Obviously, the perfect solution would be to rewrite the connection - I'll pass that one up the chain.
    – Isaac L
    Jun 28 at 18:01
  • Interestingly enough, Database.emptyRecycleBin doesn't appear to be working. Any idea if there's a time-delay? When I SOQL for WHERE IsDeleted = TRUE, the records I've just deleted and emptied start showing up.
    – Isaac L
    Jun 28 at 18:55
  • @IsaacL The records are purged asynchronously (definitely not in the same transaction as the request to purge). The docs are not clear on how long it takes, but it should be within a few minutes.
    – sfdcfox
    Jun 28 at 18:57

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.