System.QueryException: Non-selective query against large object type (more than 100000 rows).

trigger FindMarketTerritoryFromMarket on Territory_Market__c (before insert, before update) { Map<Id,Territory_Market__c> territoryMarket= new Map<Id,Territory_Market__c>(); for(Territory_Market__c t : Trigger.new) { if(t.Parent_Market__c!=Trigger.oldMap.get(t.Id).Parent_Market__c){ territoryMarket.put(t.Id,t); System.debug(t.Parent_Market__c); } } List<Account> updateAccount = new List<Account>(); Map<Id,String> setAccount= new Map<Id,String>(); for(Id ids : territoryMarket.KeySet()){ List<Account> account = new List<Account>(); account = [Select a.Name From Account a limit 1]; } }

this is my code. I removed update account code from this.

This is simple code not doing anything yet,still it fails at account = [Select a.Name From Account a limit 1]; line.

It giveme error :

Review all error messages below to correct your data.
Apex trigger FindMarketTerritoryFromMarket caused an unexpected exception, contact your administrator: FindMarketTerritoryFromMarket: execution of BeforeUpdate 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.FindMarketTerritoryFromMarket: line 31, column 8

I tried to pass only not null value still same problem. Then I rmoved all where condition and trying very simple query,still didn't help.

Can someone let me know workaroud for this problem?

Thanks

August 27, 2009
·
Answer
·
Like
0
·
Follow
0

paul-lmi
this error is thrown when you try to query a field in an object that isn't indexed, and that object has a lot of rows, or your query returns way too many rows. take a look at all the fields you're referencing in all objects in this code, and determine if they are indexed (per documenation). if you need a custom index, you need to contact support.

August 27, 2009
·
Like
0
·
Dislike
0

learnSF
Hi,

Thanks for reply.

I have query on index field as well and as you see it is just limit by 1,somehow its not aallowing to do query on account from this Market Territory object.

Any other guess?

August 27, 2009
·
Like
0
·
Dislike
0

ahab1372
no-selective means that you do not have a a WHERE statement in the query that would limit the results. I think the limit 1 only returns one row, the query still runs against all records.
Why is your querynot more specific? With this code you just get a more or less random result. Don't you want a particular Account?

Furthermore, the query is inside a for-loop which will cause problems with the governor limits. All queries should be outside loops.
Message Edited by ahab1372 on 08-27-2009 11:17 AM

August 27, 2009
·
Like
0
·
Dislike
0

learnSF
Hi,

thanks for reply.

My actual query is below

List<Account> account = new List<Account>();
account = [Select a.Market_Territory__c, a.Id From Account a where
a.ShippingState!=null and a.Shipping_County__c !=null and
a.ShippingState =:territoryMarket.get(ids).Territory_State__c and
a.Shipping_County__c =:territoryMarket.get(ids).Territory_County__c and
a.RecordTypeId!=null and
(a.RecordTypeId =: recType1 or a.RecordTypeId=:recType3)];

But this is not working and giving me same error.

If I give specific account Id then it works but here I need to retrive account Id from query.

This query if I write inside foor loop or outside for loop ,giving me same error,

this query give me 150 recrod of account.

It would be great if you cna help me to query this account and get the result.

Thanks.

August 27, 2009
·
Like
0
·
Dislike
0

ahab1372
look at tyhis thread, it explains why:
http://community.salesforce.com/sforce/board/message?board.id=apex&thread.id=2882&view=by_date_ascending&page=1

If your query does not filter by a field that is indexed, or if the filter uses Null, you might run in this error. One possible solution is adding an index to one of the fields by marking as "External ID" in the UI
Since your query uses a lot of null I am not sure if that helps. I don't know if the Record Type is indexed by default.

Btw the a.RecordTypeId!=null and
is redundant because you are specifying two particular record types in the query so it should not retrun any accounts with record type null

HTH

Arnt