+ Start a Discussion
Roniel NavarroRoniel 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
Best Answer chosen by Roniel Navarro
Cory CowgillCory Cowgill
Roniel Navarro, welcome to the world of SOQL Optimization. 

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).
User-added image

Then you can execute your SOQL Query Plan in the Query Editor Pane.
User-added image

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

Roniel NavarroRoniel Navarro
I forgot to add the query as well:

  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.
Anil SavaliyaAnil Savaliya
Hey Roniel,

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.
Cory CowgillCory Cowgill
Roniel Navarro, welcome to the world of SOQL Optimization. 

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).
User-added image

Then you can execute your SOQL Query Plan in the Query Editor Pane.
User-added image

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
 
This was selected as the best answer