function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Roniel NavarroRoniel Navarro 

Selective query filters

Dear friends

I've come across to the following error while running a visualforce page:

ResourceEvaluationAfterUpdate: execution of AfterUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact 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) Class.AP302_CalculateAverageRanking.calculateAverageRanking: line 44, column 1 Trigger.ResourceEvaluationAfterUpdate: line 45, column 1

The query that pops the error:

list<AggregateResult> resList = new list<AggregateResult> ([Select COUNT(Id) noOfResEval,
SUM(Ranking__c) totalRanking,
ResourceCatalogue__r.ParentResource__c rcaParent,
ResourceCatalogue__r.Name rcaName,
ResourceCatalogue__c rca
From ResourceEvaluation__c 
Where (ResourceCatalogue__c IN:resourceIds 
OR ResourceCatalogue__r.ParentResource__c IN:resourceIds)
AND Ranking__c > 0
AND LastModifiedDate = LAST_N_DAYS:180
group by ResourceCatalogue__c,ResourceCatalogue__r.ParentResource__c

According to this site provided before by salesforce support:

The query must have at least three filters and at least one of them must be selective, meaning that if it's a standard index should return less than 30% of the total records of the object total records or if it's a custom index less than 10% of the records. 

In this case we have three filters, one of them it's a standard (foreign key) ResourceCatalogue__c, we can consider the query selective as, at least of the three filter is selective, this return 203 from 155386 records. The other two filters are not selective but it shouldn't matter as at least one of them it's selective.

Can you please help us with this problem?

Best Regards,

Hi Roneil,
This error occurs because you have too many records processed in your query. There are two ways you can solve this problem.

1. Because of this it is taking too long to process. Ideally (if I am not wrong) Salesforce gives 10 secs of CPU time per query, this can be overcome by using the @furture annotation that gives you 60 secs. Know more about @future here

2. Or the best practice for fetching large amount of data is using Batch Apex Job. The effects are not instantaneous but eventually do get the work done. Know more about Batch Apex here

That's the best you can do right now.


P.s. If it solves the problem, please mark it as solved so that it helps others.