Running into the following issue only during async processes (i.e. Batch Apex):
- Code used is same for both sync and async apex. Both essentially pass a single record into the method
- Batch scope is 1 and is ran without specifying sharing so in "Without Sharing" or system mode
- Query executed by sys admin in dev console so all records available to user
- Table currently has 161K records in it
return [ SELECT Id, c2g__AccountValue__c FROM c2g__codaTransactionLineItem__c WHERE c2g__Transaction__r.c2g__DocumentNumber__c in :documentRefs AND c2g__LineType__c = 'Account' ];
documentRefs is a Set
In the query plan the cost is < 1.0 however it is close
Now, when the class method is executed during the batch process we get the Non-Selective Query Error. When the exact same record is run through the process via the dev console it runs without error.
According to the docs, the cost is defined as
The cost of the query compared to the Force.com Query Optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective
Which would mean that the query appears selective since the cost is < 1. Well maybe it is not saying < 1 is selective but it is just saying > 1 is not. Hmmm...
After reviewing the query that was given to us by the vendor, I restructured it a bit to query via the parent:
SELECT Id, (Select ID, c2g__AccountValue__c From c2g__TransactionLineItems__r Where c2g__LineType__c = 'Account') From c2g__codaTransaction__c Where c2g__DocumentNumber__c in ('SIN1235')
Which reduces the cost to 0.677. I then take all the records from the subquery(s) and add them to the list of results
This resolved the issue for now but I still do not know why Batch apex caused the NSQ to be thrown when it works fine during sync transactions. I would like to be able to ensure it does not appear again so I was hoping someone here knows the reason for the issue.
Unfortunately at this time the fields being used in the filter are text fields and I would prefer to not have to create a new indexed field to use as it would be a project in itself to add the field, update the records, and then update all the code.
So to summarize the question: Why does the NSQ error get thrown during batch apex and not during sync transactions given the exact same query is ran and the scope size is one.