function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DaveFFDaveFF 

Non-selective query on standard indexed field

I have the following code in a trigger on Account (after update). It's a bit of a hack because I want certain formula fields on certain Contacts to sync to Mailchimp, so their last modified date needs to be touched. I'm now (a week or two later) unable to Update a new field on Opportunities using the Data Loader, as an error down the chain of triggers says "System.QueryException: Non-selective query against large object type (more than 200000 rows)" for this. 
List<Contact> teachers = [
    SELECT Id
      FROM Contact
     WHERE AccountId IN :schoolIds
       AND RecordType.DeveloperName LIKE 'Client%'
       AND Account.RecordType.DeveloperName = 'School_or_College'
  ];
The query should only ever return less than 100 rows. The table contains ~215k rows. I guard against emptiness or presence of nulls in schoolIds, which should be a very small set (most likely only 1). Consulting this cheat sheet and Setup/Customize/Contacts/Fields, I note that lookups and record types are indexed. I don't see any of the automatic exceptions to index selectivity- I'm tempted to suspect the LIKE clause but the wildcard is at the end.

Why is this query non-selective, and what can I do to make it better? (Using record type IDs rather than DeveloperNames? Splitting out the last WHERE clause into a separate preliminary query?)
Best Answer chosen by DaveFF
Amit Singh 1Amit Singh 1
Yes, that means it's taking too long.  A couple ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds), or implement it as a Batch Apex job.  Since both of these methods are asynchronous, the trigger and associated DML will complete before the calls to the AccountServices methods are made - but they will be called eventually.  Would this work for you?
https://help.salesforce.com/articleView?id=000002493&type=1

Let me know if this helps :)

Thanks!
Amit Singh

All Answers

Amit Singh 1Amit Singh 1
Yes, that means it's taking too long.  A couple ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds), or implement it as a Batch Apex job.  Since both of these methods are asynchronous, the trigger and associated DML will complete before the calls to the AccountServices methods are made - but they will be called eventually.  Would this work for you?
https://help.salesforce.com/articleView?id=000002493&type=1

Let me know if this helps :)

Thanks!
Amit Singh
This was selected as the best answer
DaveFFDaveFF
Running the query (and the update teachers; immediately after it) asynchronously would do fine. The point of this trigger is so the Contacts get synced to MailChimp- which happens hourly (for updates) or nightly (for new subscribers added). I'll try either the @future or batch apex thing sometime.

That link I don't think will help me- as I'm already selecting a small subset of rows, and the columns I'm working with on Contact itself are standard and already indexed.
Amit Singh 1Amit Singh 1
You can use either batch or @future method.

Thanks!