Apex Code Development You need to sign in to do that
Don't have an account?
Roniel Navarro Non-selective query
Dear fellows
I've come across to an error in one of my apex classes:
Error:Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientInvoiceAfterUpdate: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id a0Tb000000E6kACEAZ; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientInvoiceAfterUpdate: 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.AP516_SetTECHAmountDue.updateTechAmountDue: line 29, column 1 Trigger.ClientInvoiceAfterUpdate: line 189, column 1: [] Class.AP511_SetTechTotalAmountDue.updateAmtDueCurrency: line 149, column 1 Trigger.ClientInvoiceAfterUpdate: line 177, column 1: []
The query is really simple and, as sugested by salesforce, I've splitted the filters of the query to see if in any of them, the result is greater than 100k records. The object where this query is running against to, has more than 100k records as well but the filter when applied separately retrieves way less than 100k records.
Your help and guidance are really appreciated
Best Regards,
Roniel Navarro
I've come across to an error in one of my apex classes:
Error:Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientInvoiceAfterUpdate: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id a0Tb000000E6kACEAZ; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientInvoiceAfterUpdate: 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.AP516_SetTECHAmountDue.updateTechAmountDue: line 29, column 1 Trigger.ClientInvoiceAfterUpdate: line 189, column 1: [] Class.AP511_SetTechTotalAmountDue.updateAmtDueCurrency: line 149, column 1 Trigger.ClientInvoiceAfterUpdate: line 177, column 1: []
The query is really simple and, as sugested by salesforce, I've splitted the filters of the query to see if in any of them, the result is greater than 100k records. The object where this query is running against to, has more than 100k records as well but the filter when applied separately retrieves way less than 100k records.
Your help and guidance are really appreciated
Best Regards,
Roniel Navarro
1. It is not enough to make sure you have a SOQL query that has an indexed field in it. Just because you add a WHERE clause you SOQL does not mean it will be selected. You can use the Developer Console to check if you SOQL is going to use an Index: Setup->Developer Console. Enable the Query Plan tool in Preferences (Help -> Preferences).
Then you can execute your SOQL Query Plan in the Query Editor Pane.
2. You can use this cheatsheet to see how the Force.com SOQL Optimizer will use indexes and if it will be selective or not. http://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf
Important Note: If you have ANY NULL values in your SOQL then an Index will not be used. For example, in your SOQL even if you had Office Set indexed, if you passed any NULL values into the parameter it will not use an index.
You can open a Salesforce Support ticket to create Custom Indexes.
Info on Query Plan Tool:
https://help.salesforce.com/apex/HTViewSolution?id=000199003&language=en_US
All Answers
AggregateResult[] groupedResults = [select sum(AmountDueOffice__c)AmtDueOffice,office__c
from clientinvoice__c where
Office__c IN:invoiceOfficeSet
AND AmountDueOffice__c > 0
AND MigratedRecord__c = false
Group BY office__c];
Please note that I already changed the filter AmountDueOffice__c from != 0 to > 0.
Please refer link for more information : https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm
you are trying to quire againt 100K+ record,In that case,Filter fields are indexed,You can rise case to salesforce field in indexed used in where cause.
1. It is not enough to make sure you have a SOQL query that has an indexed field in it. Just because you add a WHERE clause you SOQL does not mean it will be selected. You can use the Developer Console to check if you SOQL is going to use an Index: Setup->Developer Console. Enable the Query Plan tool in Preferences (Help -> Preferences).
Then you can execute your SOQL Query Plan in the Query Editor Pane.
2. You can use this cheatsheet to see how the Force.com SOQL Optimizer will use indexes and if it will be selective or not. http://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf
Important Note: If you have ANY NULL values in your SOQL then an Index will not be used. For example, in your SOQL even if you had Office Set indexed, if you passed any NULL values into the parameter it will not use an index.
You can open a Salesforce Support ticket to create Custom Indexes.
Info on Query Plan Tool:
https://help.salesforce.com/apex/HTViewSolution?id=000199003&language=en_US