3

Below is my query which throws the exception and is used in the trigger handler

SELECT Id, RecordTypeId, Status, customObj1__c FROM Case
WHERE customObj1__c IN : customObj1_Ids AND RecordTypeId IN : caseRecordTypes
  • customObj1_Ids is List of custom object Ids and
  • caseRecordTypes is set of case record types, predefined values

As per the help document if the fields in WHERE clause are standard indexes the query is selective. And when I look at this query, what we have used is Id fields, both of which are indexed.

What might have caused the issue then..? Making the method to Future or Moving the logic to a batch would help to resolve this..?

5
  • What is the data tyoe of customObj1__c. Is it lookup or text? Jan 5, 2022 at 19:47
  • 2
    First thought is that you have an index skew issue (i.e. most parent records have a small number of children, but some parents have many, many more. Like an Account normally having 3 contacts, but some accounts have 30,000 contacts). Have you tried running this query through developer console and looking at the query plan to see what the query cost is?
    – Derek F
    Jan 5, 2022 at 19:48
  • Hi @gauravsharma that is a look up field, so the fields in where condition are both List/set of Ids
    – Sooraj57
    Jan 5, 2022 at 20:00
  • @DerekF thanks for the thoughts, So this case object is relatively heavy, it can have different child objects, but i dont think numbers are not in 1000s but may be in 10s. I faced this issue only in production, at this point i could not run the query directly in prod dev console
    – Sooraj57
    Jan 5, 2022 at 20:02
  • Do you think , moving the query to future or batch class from trigger can solve this..? or As Adrian said below a null check for custom ids list would be sufficient..? One more question i have is, will it throw this error if i use the indexed fields and hit the threshold
    – Sooraj57
    Jan 5, 2022 at 20:19

1 Answer 1

6

There are two culprits that will almost always be the issue. One is easy to fix, and that is if your collections of Id values may contain null, which will cause a table scan. Make sure you strip out all null values before executing your query.

As noted here:

Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

As mentioned in the comments, the other likely culprit is data skew, which is (in my experience) a bit less likely and significantly harder to fix.

2
  • Hi @Adrian Larson , I thought about this when i was reading the document. recordtypeId set is a constant set which i have defined in trigger, so it always has value. the other custom object Id list, its a prepopulated field on my custom object even though it has not made a mandatory field. may be adding the null check for this custom id list would help me to avoid this issue? also moving it to a future or batch class can be a solution to avoid this query exception?
    – Sooraj57
    Jan 5, 2022 at 20:03
  • 2
    Yes removing null from the collection of custom object record ids is exactly my recommendation. No, moving to async will not help you.
    – Adrian Larson
    Jan 5, 2022 at 20:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .