4
set<ID> conIDs = new set<Id>();
for (Time__c t : trigger.new)
{
  if (t.Contact__c != null)
     conIDs.add(t.Contact__c);
}
if (!conIDs.isEmpty())
{
  for(Time__c t : [Select Id, Time_In_Hours__c, Total_Amount__c From Time__c Where Contact__c IN: conIDs and Contact__c != null])
  {
     ..... do some processing here
  }
}

Above is my code and the line:

for(Time__c t : [Select Id, Time_In_Hours__c, Total_Amount_c From Time_c Where Contact_c IN: conIDs and Contact_c != null])

is throwing error:

execution of AfterUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

The problem is how can I overcome this? I cannot make Matter__c lookup field an External Field as suggested here .... how can I process the records?

The Time__c object has more than 120,000 records in the org.

  • Having the same issue I have 800 records. This code was working till recently. – tsadigov Feb 10 '15 at 18:58
10

Per the indexing cheat sheet PJC posted, adding and Contact__c != null to your query automatically makes it non-selective. You should remove this.

Since foreign-key fields, like a lookup to contact, are automatically indexed as long as you don't have massive numbers of time records for each contact your query should then be selective.

You're already only adding contact IDs to the conIDs if the record's contact__c field is not null, so the exta check in your query shouldn't be needed.

While it seems like a good idea to explicitly filter out nulls to code defensively this practice actually causes your query to perform considerably worse (causing a full table scan under the hood), and if in a trigger, hit this error.

  • thnx for some clarification. I really didn't had no idea that including += null check was infact making it non-selective query. I'll try your suggestion and will report back. – VarunC Apr 21 '13 at 9:06
  • It looks like it worked but I'll do some more testing to be sure. thnx everyone ... :) – VarunC Apr 22 '13 at 10:00
4

As mohith said it has to do with a large data set, and the query taking too long to return results so it times out. Luckally Salesforce has some documentation regarding big data sets and how to handle them.

I suggest taking a look at the following webinar, it explains perfectly how to handle big data sets, and what you can do to improve your query times.

Webinar: Extreme Salesforce Data Volumes

There's also a cheat sheet

2

The error message clearly suggests what you excatly need to do.Sometimes when querying over a an object that has millions (100K in your case ) the Query gets timed out .

The first possible solution you apply as much filters as you can to limit the queryrows with fields custom indexed .

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

The above link will explain you why you are getting error .

You should raise a case with sfdc and get fields you are planning to use in where clause custom indexed .This will allow processing faster and you wont run into any errors

  • But I don't really get to the part where what changes more I can do to the query? I mean, I want to read ALL Time records for the Contact and do the processing, adding more filter will jeopardies that processing logic. I'm getting this error even if I update Single record and I know there are not more than 100 Time records for that single contact ... – VarunC Apr 20 '13 at 10:04
  • Even if there are not 100 records still the Query has to pick those 100 records from 100K records and hence custom indexing need to be done .Hope it makes clear. – Mohith Shrivastava Apr 20 '13 at 10:35

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.