function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
StenEStenE 

Non-Selective query problem.

Ok. Very strange problem here. 

 

I have an application which runs a very sophisticated query on the lead object. The Where clause contains 4 field. Non of them is containing null values. 

 

* IsDeleted

* IsConverted

* KeyA (custom, external id)

* KeyB (custom, external id)

 

If i run this query via an visualforce page it runs fine and results in only 1 record. If this exactly the same query is triggered via a trigger on the object it crashes with the non selective query execption. 

 

Who can help me in getting this explained?

 

Thanks,

Sten

Alok_NagarroAlok_Nagarro

Hi,

 

Can you please post your query also ?
StenEStenE

 

Select Id from Lead where IsConverted = false and IsDeleted = false and (awduplicate__awkeya__c like '%:-:CN-344364:-:%' or awduplicate__awkeyb__c like '%:-:CN-344364:-:%')

 

A little simplified, but this is it. 
Alok_NagarroAlok_Nagarro

Hi,

 

When using a field in your "where" clause of a query, it's best to try to have that field indexed by checking the "external ID" checkbox when you define that field.  (Note: for every object, only a limited number of fields can be indexed: see the online help for the limit.)

 

Something else to keep in mind:  filter out nulls in your Apex query.  For example, this query (which does not filter out Nulls) may fail:

[Select Id from Lead where IsConverted = false and IsDeleted = false and (awduplicate__awkeya__c like '%:-:CN-344364:-:%' or awduplicate__awkeyb__c like '%:-:CN-344364:-:%')]

 

Adding "and awduplicate__awkeya__c != null AND awduplicate__awkeyb__c != null" to the where clause prevents this error:


[Select Id from Lead where IsConverted = false AND IsDeleted = false and awduplicate__awkeya__c != null AND awduplicate__awkeyb__c != null AND(awduplicate__awkeya__c like '%:-:CN-344364:-:%' or awduplicate__awkeyb__c like '%:-:CN-344364:-:%')]

 

it seems the salesforce query optimizer operates differently others you might be used to: in some SQL variants, checking for equality against a column would implicitly filter out rows where that same column is null. This does not seem to be part of the salesforce.com query optimizer. So you might want to get into the habit of adding "where [column] != null" to your queries just as much as you do "where IsDeleted = false".
StenEStenE

Ok.

 

But that is a problem as the awduplicate__awkeyb__c column is empty in 80% of the time. This column is used as an overflow when the awduplicate__awkeya__c column is over the 255 char limit. 

 

The fields are external ids.

 

Your solution doesn't explain why the same query works in VFcontroller and not in a trigger?
dmchengdmcheng

Out of curiosity, what happens when you run the query in the Developer Console (or IDE execute anonymous window)?
Jerun JoseJerun Jose

Hi,

 

I can only think that the query in the controller (if it is an extension over a standard controller) runs in user mode, thereby on a smaller volume of data.

 

A trigger runs in system mode against the entire data set.

 

Also, Alok's solution seems to be the only one I can think of too. If the field awduplicate__awkeyb__c can be blank, just try switching the where clause locations to

 

[Select Id from Lead where IsConverted = false AND IsDeleted = false AND (awduplicate__awkeya__c != null and awduplicate__awkeya__c like '%:-:CN-344364:-:%') or ( awduplicate__awkeyb__c != null and awduplicate__awkeyb__c like '%:-:CN-344364:-:%')]

 
StenEStenE

Hi Guys,

 

I tried everything as you suggested.

 

1. Tried to execute the query via developer console. It runs perfectly no error or exceptions.

2. Changed the query so it has the null values in place. (like Jerun proposed) still crashes on trigger and not in developer console or VF Page (extension)

 

Any other ideas?

 

Thanks,

Sten