0

We have a custom object having more than 10 million records. There was an existing ETL process which fetches 200K records from this object randomly on daily basis. Recently a requirement has come up to introduce an order by clause in the existing query of ETL job so that records are processed based on priority (the priority field is of type checkbox and used in the order by clause). However when we tried using this field in order by clause we are getting QUERY_TIMEOUT exception.

When we reached out to Salesforce Support to create index on that boolean field, they responded that it is not possible to create index for a field in the order by clause they can create index for filter fields only.

I did not understand the reason/justification given by the support team. Is there any other way to address that issue?

  • How do you randomly choose these records? Do you actually mean random, or just that you let the system pick the next 200k that haven't been processed without ordering? – IllusiveBrian Oct 23 '19 at 20:16
  • @IllusiveBrian, there is a status field which got updated when first 200k records are fetched so that they should not be fetched in next attempt. – LakhanP Oct 24 '19 at 12:15
1

To be honest, a system that "randomly" processes records seems odd, I'd expect that at least they were processed based on their creation or status change date, with priority ones jumping the line. However, to answer your question, I could see two ways to handle this:

  1. Split your process into two queries. The first query will filter for only priority records, limit 200k. The second filters for only non-priority records, and has a limit of (200k - count of first query).
  2. If it really doesn't matter what order the records are processed in except that priority records process first, add a selective filter on another field that will usually get at least 200k records if there are that many, e.g. Processed__c = false and (Priority__c = true or LastModifiedDate < THIS_MONTH)

You could also do a bit of both, make a selective query and if it doesn't return 200k records, do another query that's less selective that will get the rest of the 200k limit from records that weren't in the first query.

| improve this answer | |
  • This is one of the option i thought but wanted minimal change at ETL side. – LakhanP Oct 24 '19 at 12:18
  • @LakhanP Option 2 is probably your best bet then. You didn't specify what you are using for ETL - if it's an option, the bulk api would also probably work better for your needs. – IllusiveBrian Oct 24 '19 at 14:22
0

You may want to try the SOQL Query plan tool first https://help.salesforce.com/articleView?id=000334796&type=1&mode=1 in the developer console. This will help determine where the potential bottleneck in your query is.

| improve this answer | |

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.