Apex Code Development You need to sign in to do that
Don't have an account?
Roniel 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 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) 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
,ResourceCatalogue__r.Name]);
According to this site provided before by salesforce support:
https://help.salesforce.com/apex/HTViewSolution?urlname=How-can-I-make-my-SOQL-query-selective-And-the-process-to-determine-the-fields-that-can-be-custom-indexed&language=en_US
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,
Roniel
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 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) 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
,ResourceCatalogue__r.Name]);
According to this site provided before by salesforce support:
https://help.salesforce.com/apex/HTViewSolution?urlname=How-can-I-make-my-SOQL-query-selective-And-the-process-to-determine-the-fields-that-can-be-custom-indexed&language=en_US
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,
Roniel
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 https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_classes_annotation_future.htm
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 http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
That's the best you can do right now.
Cheers,
Sid
Sforce.Ninja
P.s. If it solves the problem, please mark it as solved so that it helps others.