15

I have a SOQL Query with subquery that fails to execute because it is non-selective. I don't understand why and cannot interpret the results. All I know is that tearing parent und sub query apart solves this.

I have CustomObjectA__c and CustomObjectB__c which can be N-M linked via Linker__c. In my query I am fetching all childs of some CustomObjectA___c records:

NON-SELECTIVE

for(CustomChildObjectA__c a : [SELECT TextField__c 
                              FROM CustomChildObjectA__c 
                              WHERE mdr_Parent__c IN (SELECT mdr_CustomObjectA__c 
                                                      FROM LinkerObject__c 
                                                      WHERE mdr_CustomObjectB__c = 'a0CG000000UW2LT')]) {
    ...
}

Rewriting the code with two seperate queries instead of one with subquery solves this:

SELECTIVE

List<LinkerObject__c> linker = [SELECT mdr_CustomObjectA__c 
                                FROM LinkerObject__c 
                                WHERE mdr_CustomObjectB__c = 'a0CG000000UW2LT'];
if(!linker.isEmpty()) {                             
   for(CustomChildObjectA__c a : [SELECT TextField__c 
                                  FROM CustomChildObjectA__c 
                                  WHERE mdr_Parent__c IN :linker]) {
       ...
   }
}

In the system there are only a handful of LinkerObjects__c so the inner query is not only using an autoindex MDR filter but also return very few results.

Few data, only auto-index-filters, and extra selective subquery. How can this be non-selective??

This is the result of the query plan: **enter image description here**

  • are you doing this operation in trigger ?? – Eric Oct 13 '14 at 12:01
  • Yes indeed this SOQL is called from trigger code. Why? – Robert Sösemann Oct 13 '14 at 12:18
  • 2
    non -selective query problem occurs in mostly triggers. – Eric Oct 13 '14 at 12:22
  • 2
    @sales_lion: Is this just guesswork or do you have any proof or explanation for that?! – Robert Sösemann Oct 13 '14 at 14:21
  • 1
    Not sure if this is because of subselect, but if you fire two separate queries instead of subselect, is each of those separate queries shown as selective ? – apn Oct 13 '14 at 14:31
7

From the documentation on Large Data Volumes best practices: (Page 10)

In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause can be indexed. In this situation, decompose the single query into multiple queries, each of which should have one WHERE filter, and then combine the results.

and

Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

So, while you've got two selective queries - the combination of the two isn't selective and the optimizer isn't able to use indexes. Most likely because of the usage of the foreign key field (Lookup: mdr_Parent__c) in the where clause because the results of the query in the where clause could (theoretically) include nulls and a table scan will be performed to find the matching rows as no index can be used in that situation.

Queries which reference a lookup field will result in a table scan even if your where clause specifies qualifiers like WHERE mylookupfield__c != null.


Small Backstory

I've had to troubleshoot non-selective queries recently where what I thought was written was a good/best practice, specifically checking for != null or = null on lookup field values (as well as other types) in an effort to make the query more efficient. I was under the assumption that this would improve performance by excluding or including rows which had null values in the lookup fields by making use of an index. Unfortunately, this is not the case. These queries perform a table scan and it's extremely inefficient, especially noticeable with large volumes.

The most impressive optimization I've seen recently was a query (in a batch) which was taking 13 minutes to return ~400k records. The platform would shut down the batch when it reached the max processing time and the solution eventually needed to scale to a data volume of 2M+ records. Optimizations were in order.

After reading that LDV best practices document and then changing the structure of the query being performed the difference was quite staggering. The rewritten query returned 1.6M records in under 3 minutes and now scales incredibly well.

Beware of table scans because of referencing foreign keys in where clauses.

  • Mark, thanks for those details but I am still unsure what I should learn from this. Until now I thought that using a more complex query in the for loop was always a better choice than having two with a additional if(!result.isEmpty()). More elegant codewise and better with regards with performance. – Robert Sösemann Oct 15 '14 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.