0

I have a batch class to process some records Select Field1, Field2 from Object where Lookup_Field__c != null && Date_Field__c in (Date1, Date2)

Now the Object that this query is on has more than 7 million records and growing. There are very few records that have Lookup_Field__c populated(about 0.1% of the total data volume)

Even though the Lookup_Field__c is indexed, as per the database query optimizer cheatsheet it is mentioned that having a != in the query filter makes the filter conditions unselective. The Date field used in the query is currently not indexed.

Hence my question is, will the above query degrade my batch class to the point of causing the query to fail? Is there a better recommended approach for the same?

0

Using a negative filter will decrease the query performance. Please note that a filter condition with a Negative filter operator comes under Index Selectivity Exception

enter image description here As per this documentation

Typically, a custom index isn’t used in these cases.

  • The queried values exceed the system-defined threshold.
  • The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH.
  • The CONTAINS operator is used in the filter, and the number of rows to be scanned exceeds 333,333. The CONTAINS operator requires a full scan of the index. This threshold is subject to change.
  • You’re comparing with an empty value (Name !='').

As per Salesforce best practices we should not use negative filters. I recommend using query plan tool and this documentation to make the query selective. Adding more filters should help

Also, note that when you create a custom index any gain you would have had by included the custom indexed field in your query filter is lost by using a negative operator like NOT EQUAL TO (!=), NOT CONTAINS and NOT STARTS WITH.

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.