How to run SOQL for an Object having more than 100K records?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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