5

I have a custom object in a managed package that currently has no records in the customers org. Lets call it DFB__ProductUdfMap__c (namespaces etc... have been changed to protect the innocent). The Storage Usage Data Management page doesn't show the custom object - presumably because it has no records.

This custom object is queried as part of a trigger on updating Opportunity records.

For this particular customer the following exception is being encountered with upserts to OpportunityLineItem records, which are subsequently firing the Opportunity triggers.

Upsert failed. First exception on row 0 with id 00k600000000001AAA; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, DFB.ProductOpportunityTrigger: 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) (DFB) : []

Capturing the Debug log when this exception occurs shows the last log entry as:

16:11:35.859 (859740543)|SOQL_EXECUTE_BEGIN|[500]|Aggregations:0|SELECT Id, Name, DFB__DynamicPropertyID__c, DFB__DynamicPropertyType__c, DFB__ProductUdf__c FROM ProductUdfMap__c

The actual query in the managed package on line 500 does include a where clause.

Select Id, Name, DynamicPropertyID__c, DynamicPropertyType__c, ProductUdf__c 
from ProductUdfMap__c
where ObjectType__c = 'Order'

The fields DynamicPropertyID__c, ObjectType__c, and AB2__DynamicPropertyType__c are all formula fields via the indexed lookup ProductUdf__c. While this particular org has no records for the custom object it isn't expected to have more than few dozen records in the extreme case.

Executing the full query in the developer console returns no rows.

The developer console gives the Query Plan as:

Cardinality: 7869700
Fields:
Leading Operation Type: TableScan
Cost: 5.0277874...
sObject Cardinality: 7869700
sObject Type: DFB__ProductUdfMap__c

Query Plan Cardinality

Reducing the SOQL query to just the ID field gives the same results.

Why is the cardinality in the millions when there aren't any records?

I assume this is the problem, although it seems extremely odd given there are no records.

Is it something to do with the formula fields?


Expanding out the formula in the where clause to use the actual lookup gives an additional Query Plan with a much better Cardinality:

select Id from DFB__ProductUdfMap__c 
where DFB__ProductUdf__r.DFB__ObjectType__c = 'Order'

enter image description here

The Cost is still greater than 1, so I'm not in the selective range yet.


Comment from @ca_peterson:

some of the internal stats about table size update nightly - maybe that job failed? Support can force an update

I created one mock custom record and left it overnight. Now when I get the query plan for the exact same query:

select Id from DFB__ProductUdfMap__c 
where DFB__ProductUdf__r.DFB__ObjectType__c = 'Order'

enter image description here

The original SOQL using the formula field in the where clause also produces the same Cost of 0.65 with an sObject Cardinality of 1.

2

I don't believe there's any published documentation on this but I do know that, at the time of writing at least, the table statistics used for query selectivity checking (and query plans) are updated on a nightly basis.

Based on that the easiest option you have is to wait up to 24hrs for this statistics update to run. I know independently to this that custom objects can re-use the key prefix, and seemingly actual physical location, or previously deleted custom objects.

My hypothesis is that your custom object is re-using an old object's physical location and the old statistics are somehow still associated with it. If I'm right then the nightly run should clear this up and your SObject Cardinality should be down to the expected 0, with the correspondingly low cost.

  • As you say, it seemed to correct the sObject cardinality in the sandbox after a period of time. – Daniel Ballinger Apr 9 '15 at 23:35
1

Please open a case to have a custom index created for the selective filter field.

  • I guess I could. But it still begs the question. There are literally no records to be indexed. – Daniel Ballinger Apr 9 '15 at 20:50

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.