System.QueryException: Non-selective query against large object type (more than 100000 rows)

Question

I started receiving lot of these errors, I'm not sure how to handle this the query that's causing this error is,

 public static void updatePartnerLeadCount(set<Id> setPartnerIds){
  //Map will contain one Lead Id to one sum value
  List<Partner__c> lstpartnercount=new List<Partner__c>();
  map<Id,integer> LeadCountMap = new map<Id,integer>();
   map<Id,datetime> LeadDateMap = new map<Id,datetime>();

    for(AggregateResult q : [select Partner__c,Count(Id),max(CreatedDate)
           from Lead where Partner__c IN :setPartnerIds  group by Partner__c]){
             LeadCountMap.put((Id)q.get('Partner__c'),(integer)q.get('expr0'));
             LeadDateMap.put((Id)q.get('Partner__c'),(Datetime)q.get('expr1'));
    }      

     for(Partner__c p:[Select Id,of_Leads_Sent__c,Date_of_last_lead__c from Partner__c where ID IN:LeadCountMap.keyset()]){
       p.of_Leads_Sent__c=LeadCountMap.get(p.Id);
       p.Date_of_last_lead__c=LeadDateMap.get(p.Id).date();
       lstpartnercount.add(p);
    }
update lstpartnercount;
}

my error message is pointing to -

for(AggregateResult q : [select Partner__c,Count(Id),max(CreatedDate) from Lead where Partner__c IN :setPartnerIds group by Partner__c]){ LeadCountMap.put((Id)q.get('Partner__c'),(integer)q.get('expr0')); LeadDateMap.put((Id)q.get('Partner__c'),(Datetime)q.get('expr1')); }

What am I trying to do in the code ? - I have a Partner Object(custom) and its related list Lead, this is to get the number of leads(of_Leads_Sent__c) and also to find out the date of the last lead (Date_of_last_lead__c)

Answer 1

This is a skew issue when we have Non Selective query against large objects .One thing worth trying for these type of issues is by ordering your query by Parent Id so that it reduces cost .

The salesforce help document is below for this

Using query Tool

Change your above query to below

 for(AggregateResult q : [select Partner__c,Count(Id),max(CreatedDate)
       from Lead where Partner__c IN :setPartnerIds  group by Partner__c order by Partner__c ]

Also remove null from the set "setPartnerIds" to make query specific and avoid querying null values .

Answer 2

There's a whole lot of discussion out there about how to resolve these kinds of errors. The textbook starting point would be here:

https://help.salesforce.com/apex/HTViewSolution?urlname=How-to-make-my-SOQL-query-selective&language=en_US

However, experience tells me that the first thing you should do is check for nulls in setPartnerIds. You query looks like it would be good and selective unless setPartnerIds contains a null value and you have many leads where Partner__c is null. It's certainly something that has tripped me up before.