0

I have a query with query plan as below:

enter image description here

The lowest cost is greater than 1, It means query is not selective.

Trigger context:

I used this query in a before insert trigger on a empty object and it threw the error "System.QueryException: Non-selective query against large object type"

Visualforce context:

I used the same in Visualforce as below and it is working fine.

<apex:page showHeader="false" sidebar="false" controller="TestVFController">
    <apex:form>
        <apex:commandButton action="{!getDataController}" rerender="panel-id" value="Click Me!"></apex:commandButton>
    </apex:form>

    <apex:outputPanel id="panel-id">
        <apex:outputText value="{!totalRows}"></apex:outputText>
    </apex:outputPanel>

</apex:page>



public with sharing class TestVFController {

    public Integer totalRows{get;set;}

    public PageReference getDataController(){
        totalRows = [SELECT Id FROM Large_Object__c WHERE UnIndexedColumn__c = 'Test'].size();
        return NULL;
    }
}

Batch Context:

I used the same query in Batch start method(tried returning queryLocator and list of objects) and execute method. In all variations, it ran fine.

From Make SQL Query selective documentation:

A non-selective query may cause different programmatic elements (like an Apex trigger or batch Apex class) to fail. When querying large objects special design considerations must be taken into consideration.

Error in trigger context make totally sense because triggers should finish quickly.

Documentation says, we can expect this error in other contexts but it is running fine. Does system ever throw Query not selective error in Batch and Visualforce transactions?

  • What is number of records in your object? – Santanu Boral May 14 '18 at 2:08
  • @SantanuBoral Total number of records: 2942008 – javanoob May 14 '18 at 2:11
  • 1
    Ultimately, while it's hard to say without knowing the exact purpose of your trigger query, you should aim for finding optimizations that make these queries selective enough. For example I had one org where I needed to do a complicated multi-level query of deep account hierarchies in a trigger. The optimization I created was an "Account Depth" Integer field plus a bulkified way to query hierarchies that leveraged Platform Cache. – Charles T May 14 '18 at 12:31
  • @CharlesT I am not looking for how to resolve this error. I am interested in knowing more if the same query throws error in other contexts like Batch, Visaulforce etc. The document I linked says, this error can happen in Batch context but it is running fine. Thanks! – javanoob May 14 '18 at 13:03
2

There are unique limitations when running large, non-selective queries in a trigger context:

Developers receive an error message when a non-selective query in a trigger executes against an object that contains more than 200,000 records. To avoid this error, ensure that the query is selective.

(Emphasis mine).

That same exception isn't thrown at the 200,000 record threshold in other contexts, although not-selective queries can certainly time out in any context.

  • So in other contexts threshold is higher than trigger context and error won't be same as in trigger context but query will time out. Am I getting it right? – javanoob May 14 '18 at 2:50
  • Yeah, it's just a different performance metric. The 200k metric applies only in trigger context. – David Reed May 14 '18 at 3:02

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.