9

Running into the following issue only during async processes (i.e. Batch Apex):

  1. Code used is same for both sync and async apex. Both essentially pass a single record into the method
  2. Batch scope is 1 and is ran without specifying sharing so in "Without Sharing" or system mode
  3. Query executed by sys admin in dev console so all records available to user
  4. Table currently has 161K records in it

The Query:

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

enter image description here

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

enter image description here

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.

  • If they pass a single record, can you put a limit 1 in the query or does the query return more than one record? – crmprogdev Aug 28 '16 at 15:28
  • 1
    @crmprogdev - No, there is a potential for multiple records being returned. The Batch executes on a different object and this query pulls in the codaTransaction(s) related to the batch scope (size of 1). So there could be multiple returned – Eric Aug 28 '16 at 15:31
  • Is your batch run without sharing? Perhaps that could cause the divergent behavior you're seeing. – Adrian Larson Aug 28 '16 at 15:44
  • 1
    @AdrianLarson - The batch does not specify sharing so yes it runs without sharing. Dev console is ran using sys admin profile so no sharing restrictions there. Also forcing through async using code not using without sharing works as well. Only happens during batch. Added this info to question – Eric Aug 28 '16 at 15:48
0

Are you developing a managed package or is c2g something you installed? If you don't have access to make configuration changes there's not much you can do.

That being said, if you make c2g__DocumentNumber__c an external id field it will automatically become an indexed field. As long your where clause limits the query to the number of records listed in the Force.com Query Optimizer - Custom Index of the Query Optimization Cheat Sheet you should be good. While cost over 1 is always non-selective, this cheat sheet will tell how to ensure that the query is selective based on the number of records in the table.

|improve this answer|||||
  • c2g is a FinancialForce managed package. Appreciate the answer but it does not address the question as to why in Sync transaction query is selective yet in Async Apex the exact same query is Non-Selective. Additionally the cost of the query is < 1.0 according to the query plan – Eric Aug 31 '16 at 13:31

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.