+ Start a Discussion
gk_mygurugk_myguru 

Urgent help needed on : System.QueryException: Non-selective query against large object type

Please Suggest.

 

Hi ,

 

I am getting an System.QueryException of  : Non-selective query against large object type' when I Query on Task.  

 

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

 

Here is the Query on Task - 

 

 List<Task> ObjTaskLst = [Select t.OnStateClient__Call_ID__c,t.Call_Start_Time__cFrom Task t WHERE t.OnStateClient__Call_ID__c IN :ActivityIdDateTimeMap.keyset() LIMIT 1];

 

Here the field 'OnStateClient__Call_ID__c'  on the Task and is a part of the Package installed in the system.Hence can not modify this field.

 

 

Please Suggest.

 

 

 

gk_mygurugk_myguru

Hello All,

 

Can anyone please help me on this issue ? 

 

Please Suggest.

 

 

iBr0theriBr0ther

I suggest you create one more field and mark as external id that will be considerd as index.

gk_mygurugk_myguru

Hi PoorMan, 

 

Thanks for the suggestion.

 

I am not able to create additional field on task with 'extrenal ID checked'  .it gives me error : Error: Custom field index limit exceeded.

 

And even If I am able to create this filed , how can I use it in my query with the reference to the aprent object. ?   Could you please elabourate on this ... 

 

Please suggest 

iBr0theriBr0ther

Well, if you have had created it, this field must be assigned the unique value on event before insert. 

To use this field, in my experience, you have to include this field in the your query and also use in your WHERE clase as well.

It works fine myself when did as above solution.

 

Hope you can do it.

gk_mygurugk_myguru

 

Hi  PoorMan,

 

I have created a field- Call_Id__c on Task with the Extrenal ID check ; now I want to use it in a query , how would I use it ? 

I am still unclear about using it.

here is my query .....

 

 List<Task> ObjTaskLst = [Select t.OnStateClient__Call_ID__c,t.Call_Start_Time__cFrom Task t WHERE  t.OnStateClient__Call_ID__c IN :ActivityIdDateTimeMap.keyset() LIMIT 1];

 

 Please suggest

gk_mygurugk_myguru

Hi there,

 

I have used the Call_Id__c field in the query as per your suggestion but the query returns me 0 rows.   :(

 

 

List<Task> ObjTaskLst = [Select t.OnStateClient__Call_ID__c,Call_Start_Time__c,Call_Id__c From Task t  WHERE Call_Id__c != NULL AND OnStateClient__Call_ID__c !=NULL AND OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset() LIMIT 1 ];

 

I have got  stuck here anyone can help me please ....

 

Thanks.

iBr0theriBr0ther

Hi,

 

After creating an external id field, have you populated any value into that field yet?

Please ensure that this field have to be fullfilled with the unique value for all records


gk_myguru wrote:

 

Hi  PoorMan,

 

I have created a field- Call_Id__c on Task with the Extrenal ID check ; now I want to use it in a query , how would I use it ? 

I am still unclear about using it.

here is my query .....

 

 List<Task> ObjTaskLst = [Select t.OnStateClient__Call_ID__c,t.Call_Start_Time__cFrom Task t WHERE  t.OnStateClient__Call_ID__c IN :ActivityIdDateTimeMap.keyset() LIMIT 1];

 

 Please suggest



.

gk_mygurugk_myguru

Yes I have created the field with the unique value for it ....Still the query is returning me 0 records.....

iBr0theriBr0ther

So you have to do some investigation then. Please try to query with where condition one by one as follow, and System.debug the result:

 

1) WHERE Call_Id__c != NULL
2) OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset()
3) WHERE Call_Id__c != NULL  AND OnStateClient__Call_ID__c !=NULL
4) WHERE Call_Id__c != NULL  AND OnStateClient__Call_ID__c !=NULL  AND OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset() 

 

Also, I would like to see the value of OnStateClient__Call_ID__c and  ActivityIdDateTimeMap.keyset() . Please also debug these value.

gk_mygurugk_myguru

 

Here are the results for it ....

 

1) WHERE Call_Id__c != NULL

 

This Q returns me 13 rows  .... fine

 

2) OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset()

 

this Q throws an exception as we knw cause have not used Ext ID fld.

 

3) WHERE Call_Id__c != NULL  AND OnStateClient__Call_ID__c !=NULL

 

This Q returns me 6 rows  .... fine 

 

4) WHERE Call_Id__c != NULL  AND OnStateClient__Call_ID__c !=NULL  AND OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset() 

 

This Q returns me 0 rows i.e. NULL

 

 

I am able to print the values from this map  ActivityIdDateTimeMap.keyset() ..... There seems to be issue with the OnStateClient__Call_ID__c on task . ..

 

Please Suggest.

 

iBr0theriBr0ther

So the issue must be in:

 

   OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset()

 

Could you print out what are in OnStateClient__Call_ID__c and what are in ActivityIdDateTimeMap.keyset()?

 

 

 

 

gk_mygurugk_myguru

OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset()

Is the culprit and failing to return me the value.

 

 

Here is a key and a value exists in the map .....

 

ActivityIdDateTimeMap.keyset() contains (Key,value) as a  (String,Datetime)

 

|USER_DEBUG|[30]|DEBUG|####### ActivityIdDateTimeMap{MakeCall.1315617841.861=2011-09-21 03:48:03}

 

but I am failing to get it when I query it ...


iBr0theriBr0ther

Maybe we need more info. Please try this where clause again:

 

3) WHERE Call_Id__c != NULL  AND OnStateClient__Call_ID__c !=NULL

 

As you mentioned earlier, it returned 6 rows. Within this query please print out both OnStateClient__Call_ID__c and ActivityIdDateTimeMap.keyset() for those 6 rows; so you will be able to determine why the query return 0 record when  adding "OnStateClient__Call_ID__c IN : ActivityIdDateTimeMap.keyset()"

 

Could you also check which row that you expected to return?

gk_mygurugk_myguru

Hi PoorMan ,

 

I have a question .. when we define a field as a Extrenal Id field what we need to do ? I mean which steps we have to folllow.

 

when I created a field  Call_Id__c on Task ,  I cheked it  for Extrenal Id , and  for Unique Id  while we define it......

 

But when I query it on Task   Call_Id__c shows me a NULL value .... the problems seems to be there I belive.... how can we define this field to a initial value  ?

 

Please suggest

iBr0theriBr0ther

Ah, this means that you haven't populated any value into that field yet? If so, You have to populate the unique value into all the existing records for this field. Also everytime you insert a new record.

 

The advantage is you can bypass that crazy exception while including in WHERE clause, and another thing is you can use Upsert statement rather that inser or update by just providing the externa_id field when you upsert any records. System will manage to insert or update automatically by the unique value in the external id field.

 

Regarding your problem, plz populate the existing records first.  The easiest way is Apex Data Loader. Export them all, and consider what should be unique, and update back to server. Then try query again.

 

Hope you success :)