+ Start a Discussion
philbophilbo 

SOQL qry throws NON-SELECTIVE QUERY error only in trigger context ???

Hi all,

 

hoping someone can QUICKLY weigh in with a sol'n here as I have minimal time to get this resolved.

 

I have an Apex trigger that builds a fairly complex dynamic SOQL query based on the trigger's input records.  When the trigger executes the query, it throws a 'Non-selective query against large object type' error.

 

However - when I scrape that query out of the trigger and run it anonymously, it runs successfully.  When I put it in its own static class method and run the method, it runs successfully.

 

I have tried both class and trigger on API versions 15.0 and 20.0 with consistent results.

 

I am not that interested in this point in how my query could be restructured to avoid the exception, although I will say that the fields it returns AND the fields in its query clause are all lookup fields (plus the IsDeleted flag).  What is of crucial interest to me is - why does the query behave differently between trigger and non-trigger context?  Is this to be expected, and if so, can someone please point me to the documentation that says it is to be expected? 

 

Thanks.  Anxiously awaiting a response....

 

steve456steve456

i need more clarity

 

philbophilbo

Nope - it makes no difference.

 

For extra colour, here are the particulars of the object model:

 

Two custom objects : mySObj__c and emplSObj__c.

Object mySObj__c looks up to standard Account record via Account__c lookup fld.

Object mySObj__c looks up to emplSObj__c record via Empl__c master-detail fld.

Object emplSObj__c looks up to standard User record via User__c lookup fld.

 

The problematic query is assembled within a mySObj__c Delete trigger, and looks like this:

 

select
  Account__c , Empl__c , Empl__r.User__c
from
  MySObj__c
where
  Empl__r.User__c != Null and
  Empl__r.User__r.IsActive = True and
  (
    Id in ( trgId1 , trgId2 , ... <all MySObj__c Ids in trigger> ) or
    ( 
      ( Empl__c = emplId1 and Account__c = acctId1 ) or
      . . . <repeat for each MySObj__c in trigger> . . .
    )
  )
  ALL ROWS

 

There are 104,000 MySObj__c records total in the system, 64,000 of which are Deleted and 40,000 of which are NOT Deleted.

 

Any trigger batch size of 5 or over causes this query to throw the NON-SELECTIVE QUERY exception.

 

So:  why?  Why does it fail in the trigger but not outside the trigger?

 

 

Starz26Starz26

You will get this error when a trigger context returns more than 100,000 records:

 

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

 

also

 

"Since a formula field can't be custom indexed, the query won't be selective, regardless of how many records have actually 'ValueA'. Remember that filtering on a formula field should be avoided, especially when querying on large objects, since the formula needs to be evaluated for every Account record on the fly."

 

I understand that the query is "selective" but it is not selective enough...

 

Take the records down to less than 100,000 see if you still get the error.

 

For reference:  http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

philbophilbo

Just to be clear - the total size of the MySObj__c SObject is 104,000 records, including Deleted records.  The query in question returns only a tiny fraction of that number - on the order of the trigger batch size; e.g. for a batch size of 50, the query returns between 50 and about 100 rows.

 

So it's not that.

 

Seems to me that my query clauses are ALL on indexed fields (except maybe for the IsDeleted clause), by virtue of them being lookup fields.

 

Never mind <beats head against wall>.  I just went ahead and completely restructured my query, re-wrote the trigger to expect the new result set, and am moving forward.

Starz26Starz26

I feel your pain.

 

I had a query in a trigger where the max possible records to return was approx 400. I got this error as well. The wuery I was using used the WHERE xxx IN: (SET OF ID's).

 

The error did not get thrown in the sandbox (fewer records) but once it was moved to the full sandbox it gave that error.

 

Maybe someone can give a clear answer as to why this occurs...