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
ChucktChuckt 

Query Error: "System.QueryException: Non-selective query against large object type"


Does anyone know why the following query line:

List<Contact> contacts = new List<Contact> ([Select Id, Email from Contact where IsPersonAccount = false]);

Yields this error message:

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger LeadAssignmentFromImagitas caused an unexpected exception, contact your administrator: LeadAssignmentFromImagitas: execution of BeforeInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.LeadAssignmentFromImagitas: line 18, column 9

?

The query returns only 58 rows.

Any help is greatly appreciated.  Thank you.
Best Answer chosen by Admin (Salesforce Developers) 
Kirk F.ax361Kirk F.ax361
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) fails:

[select Id From OpportunityLineItem where IsDeleted = false and Custom_Product__c = :newCustomProduct.Id limit 1]

Adding "and Custom_Product__c != null" to the where clause prevents this error:

[select Id From OpportunityLineItem where IsDeleted = false and Custom_Product__c != null and Custom_Product__c = :newCustomProduct.Id limit 1]

Incidentally, the first, inefficient query worked fine in our Spring '08 release, but took about 3 seconds to return results.  Once we were upgraded to Summer '08, the same query caused errors. By adding to the where clause to filter out nulls, the error is prevented and improved query response time about 9X over the Spring '08 release.

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"



Message Edited by Kirk F on 06-16-2008 09:30 AM

All Answers

SuperfellSuperfell
IsPersonAccount is not indexed, so that's not applied to the estimated query size, you must have a lot of contacts total, and that's why you tripped the limit.
ChucktChuckt
Thank you Simon.  Is there any way to make that field indexed?  It seems a little strange that a query that returns so few rows fails because the contact object is large.  Is there some sort of workaround for this situation?
SuperfellSuperfell
As the field is not indexed, it has to look at all the rows to work out that there's only 58 that match.
I don't think you can make that particular field indexed.
icemft1976icemft1976
Hi there,

I had the same error code, are both of my select statements below causing this or just one? (the line quoted in the error msg below  is not either of these, but these are the only 2 queries in the code)

thanks!

if(   [select count() from Task where WhatId = :newTrigger.Id and Status != 'Completed' and Type = 'Sales Meeting Follow-up' and ActivityDate <= TODAY ] != 0)
{               
    for(Task temp : [      select Id, WhoId, Status, ActivityDate, Type,
                                    WhatId from Task t where WhatId = :newTrigger.Id and Status != 'Completed'
                                    and Type = 'Sales Meeting Follow-up' and ActivityDate <= TODAY   ])
    {
                       
        if(temp != null)
         {
                  temp.Status ='Completed';
                  update temp;   
         }
    }   
}

OpportunityDatestamping: execution of BeforeInsert

caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

Even if a field is indexed a filter might still not be selective when:

1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

Trigger.OpportunityDatestamping: line 33, column 17


Thanks for your help - AJ


Message Edited by icemft1976 on 02-20-2008 01:30 PM

Message Edited by icemft1976 on 02-21-2008 11:04 PM
AndreaRozAndreaRoz
Hi,
 
I am facing with the same problem and I need to find a solution... following a snippet of my Trigger:

for(Contact c : Trigger.new ){

Integer tot = Select count() from Contact Where ConcDupe__c= :c.ConcDupe__c

}

Where ConcDupe__c is a formula cusotm field and in the Contact Table I've got more than 100000 records... I get the QueryExcepiton.

Is it possible to define the ConcDupe__c as Indexed field? Otherwise Is there a workaround?

Any help is greatly appreciated.  Thank you.

--

Andrea Rosa

 
AndreaRozAndreaRoz

Hi to all,

I found a solution for my problem, when you define a field as External Id the field became a indexed field, and that's solve the problem!

Best Regards

--

andrea rosa
Kirk F.ax361Kirk F.ax361
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) fails:

[select Id From OpportunityLineItem where IsDeleted = false and Custom_Product__c = :newCustomProduct.Id limit 1]

Adding "and Custom_Product__c != null" to the where clause prevents this error:

[select Id From OpportunityLineItem where IsDeleted = false and Custom_Product__c != null and Custom_Product__c = :newCustomProduct.Id limit 1]

Incidentally, the first, inefficient query worked fine in our Spring '08 release, but took about 3 seconds to return results.  Once we were upgraded to Summer '08, the same query caused errors. By adding to the where clause to filter out nulls, the error is prevented and improved query response time about 9X over the Spring '08 release.

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"



Message Edited by Kirk F on 06-16-2008 09:30 AM
This was selected as the best answer
AndreaRozAndreaRoz

Hi Kirk

thank you very much for your helpful reply, now it works.

Best regards
--
Andrea Rosa

 

 

 
hamayoun65hamayoun65

Hi,

 

Can somehere point me to where in the SFDC documentation it talks about indexed fields?  I don't seem to be able to find this info.

 

Thx,

Hamayoun

rtuttlertuttle

This is the closest thing I could find to mentioning anything about indexed fields:

 

http://www.salesforce.com/us/developer/docs/api_meta/Content/customfield.htm

 

Read the piece on "indexed".  It flat out says a field is indexed when externalid is set or the field is unique.  If anyone knows of anywhere else it discusses indexed fields I'd be interested in reading that as well. 

 

-Richard 
micwamicwa

I have the same problem but my fields are lookup relationships so I can't set them to external Id.

Any idea how to solve this?

melchisholm22melchisholm22
List <Lead> sObjleads = [select MobilePhone from Lead where MobilePhone != null and MobilePhone IN :leadMap.KeySet() limit 1];
 
The above query gives me the same problem. MobilePhone is a standard field therefore cannot be indexed. Why does it still give the error since I have put a limit already and filtered null values too?
 
I hope somebody can help me. Thanks! 
Message Edited by melchisholm22 on  07-24-2009 04:21 AM


chtomchtom
The reason you still receive the error is because the field you are using in your Where clause is not indexed, so the query still needs to look through all the leads in your org to find the ones you are trying to get. 
 
You could try copying the MobilePhone field into a custom hidden field that is indexed.  Then use the new hidden field in your query.  It seems a little redundant, but it might work.


melchisholm22melchisholm22
Hi!

Thanks for the suggestion but the problem is we have consumed 3 index fields already. Is there any other way?


GuyClairboisGuyClairbois
with the Winter 11 release done last weekend we also started getting this error message on our sandbox.


It looks like adding the != NULL statements solved the problem again, so thank you so much for posting that here.


 


Guy


 


 


padma_2700padma_2700
Hi ,
 
Could you please take time and share your answer,if you are able to resolve it.
Actually I am also facing the same issue in our project.
 
Thanks ahead,
 
Padmavathi D


gowthamgowtham
Me too getting the same error i have tried addng !null and is delete also not able to find out


Kelly KKelly K
This method worked for me too:
 
Query before:
[SELECT Id, HasResponded, First_Responded_Date_Time__c, Contact.AccountId, ContactId, CampaignId
FROM CampaignMember
WHERE Contact.AccountId IN :AccountIds AND HasResponded = true
ORDER By First_Responded_Date_Time__c DESC];
 
Query After:
[SELECT Id, HasResponded, First_Responded_Date_Time__c, Contact.AccountId, ContactId, CampaignId
FROM CampaignMember
WHERE Contact.AccountId IN :AccountIds AND ContactId != null AND HasResponded = true
ORDER By First_Responded_Date_Time__c DESC];


Sheldon LazarusSheldon Lazarus
Hi Guys I need help with my query, I am recieving the same error:
select id, name, Group_KYC_Contact__c, (select Contact_Role_s_at_Client__c, Active_User__c, Inactive__c, name from contacts) from account where id in : acc_ids]

Please could some one help