+ Start a Discussion
St2018St2018 

Non-selective query against large object type (more than 200000 rows) Class.QueryCacheUtility.queryLineItems: line 585 column 1

It is happening on the MAP_Line_Item_Forecast__c  I dont understand why I get this error. I am new to Apex. Here is the code below
public List<MAP_Line_item_Forecast__c> queryLineItems(Set<Id> dealIds, Set<Id> leaseIds, Set<Id> actualsWithoutLeaseIds, Set<String> buildingUnitsForSpecs) { 
      system.debug('buildingUnitsForSpecs: ' + buildingUnitsForSpecs);
        return [select Id, BuildingUnit__c, Building__c, DBA__c,
            Lead_Id__c, Latest_Amendment_Deal__c, Is_Perm_Activity__c, Square_Footage__c, Category_Code__c, 
            LOB__c, Lob__r.Lob_Name__c, Lob__r.Business_Group__c, lob__r.Parent_LOB__c, lob__r.parent_lob__r.id,
            Lease_Id__c, Lease_Id__r.RecordTypeId, Lease_Id__r.RecordType.Name, 
            Deal_Id__r.Atm__c, Deal_Id__r.Kiosk__c, Deal_Id__r.Antenna__c, Deal_Id__r.RecordTypeId,Deal_Id__r.RecordType.Name, deal_id__r.StageName,
            Start_Date__c, End_Date__c, Date_of_Possession__c, Unit__c, MAP_Line_Item_Source__c, Company__c,
            isActive__c, // necessary so we can check the field as line items are updated for overlap 
            (select Id, Year__c,
                Jan__c, Feb__c, Mar__c, Apr__c, May__c, Jun__c, Jul__c, Aug__c, Sep__c, Oct__c, Nov__c, Dec__c,
                Jan_Actual__c, Feb_Actual__c, Mar_Actual__c, Apr_Actual__c, May_Actual__c, Jun_Actual__c, Jul_Actual__c, Aug_Actual__c, Sep_Actual__c, Oct_Actual__c, Nov_Actual__c, Dec_Actual__c 
                from MAP_Monthly_Forecast_Stats__r) 
            from MAP_Line_Item_Forecast__c 
            where isActive__c = true and ( 
                ( Deal_Id__c in :dealIds ) 
                or ( Lease_Id__c in :leaseIds )
                or ( Actual_Id__c in :actualsWithoutLeaseIds )
                //or ( Latest_Amendment_Deal__c in :amendedDealIds ) 
                or ( MAP_Line_Item_Source__c = :MAP_Constants.MAP_LINE_ITEM_SOURCE_SPEC_DEAL and BuildingUnit__c in :buildingUnitsForSpecs)
            )];
  }

 
Raj VakatiRaj Vakati

If you're running a trigger on objects that have more than 200,000 records, it's possible that you'll receive the error, "System.QueryException: Non-selective query against large object type." We'll go over the a few possible fixes. 


To fix the issue Add some indexed field to SOQL where condition ( may be some of the standard index fields like name .Id etc )
 
public List<MAP_Line_item_Forecast__c> queryLineItems(Set<Id> dealIds, Set<Id> leaseIds, Set<Id> actualsWithoutLeaseIds, Set<String> buildingUnitsForSpecs) { 
      system.debug('buildingUnitsForSpecs: ' + buildingUnitsForSpecs);
        return [select Id, BuildingUnit__c, Building__c, DBA__c,
            Lead_Id__c, Latest_Amendment_Deal__c, Is_Perm_Activity__c, Square_Footage__c, Category_Code__c, 
            LOB__c, Lob__r.Lob_Name__c, Lob__r.Business_Group__c, lob__r.Parent_LOB__c, lob__r.parent_lob__r.id,
            Lease_Id__c, Lease_Id__r.RecordTypeId, Lease_Id__r.RecordType.Name, 
            Deal_Id__r.Atm__c, Deal_Id__r.Kiosk__c, Deal_Id__r.Antenna__c, Deal_Id__r.RecordTypeId,Deal_Id__r.RecordType.Name, deal_id__r.StageName,
            Start_Date__c, End_Date__c, Date_of_Possession__c, Unit__c, MAP_Line_Item_Source__c, Company__c,
            isActive__c, // necessary so we can check the field as line items are updated for overlap 
            (select Id, Year__c,
                Jan__c, Feb__c, Mar__c, Apr__c, May__c, Jun__c, Jul__c, Aug__c, Sep__c, Oct__c, Nov__c, Dec__c,
                Jan_Actual__c, Feb_Actual__c, Mar_Actual__c, Apr_Actual__c, May_Actual__c, Jun_Actual__c, Jul_Actual__c, Aug_Actual__c, Sep_Actual__c, Oct_Actual__c, Nov_Actual__c, Dec_Actual__c 
                from MAP_Monthly_Forecast_Stats__r) 
            from MAP_Line_Item_Forecast__c  Where ID IN :<> OR NAME IN <> 
            isActive__c = true and ( 
                ( Deal_Id__c in :dealIds ) 
                or ( Lease_Id__c in :leaseIds )
                or ( Actual_Id__c in :actualsWithoutLeaseIds )
                //or ( Latest_Amendment_Deal__c in :amendedDealIds ) 
                or ( MAP_Line_Item_Source__c = :MAP_Constants.MAP_LINE_ITEM_SOURCE_SPEC_DEAL and BuildingUnit__c in :buildingUnitsForSpecs)
            )];
  }





Options to resolve the error
1. You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.
2. A custom index on the field.
3. A possible quick fix may be to make the field in question an external ID. Since external IDs are indexed automatically, this will create the index and may solve the problem.

In Classic, go to Setup -> Create -> Objects then click the Object name. 
In LEX, go to Setup -> Object Manager  then Object name.
Then, edit field name and check the box External ID.

NOTE: If this does help your query performance, we recommend that you still log a case with support to implement a custom index in it's place. This is not a long term solution for performance improvements and has other designed use cases.
Read our Make SOQL query selective help article for more information on indexing.



Update your code as below 

 
KrishnaAvvaKrishnaAvva
Hi Stephanie,

This is happening because the query is returning huge number of records violating one or more governor limits.
Please make the query more selective - use indexed fields, use more where conditions.

There are elaborate explanations/solutions on how to optimize your SOQL for this problem. Please find the details.
https://developer.salesforce.com/forums/?id=9060G000000BfX8QAK
https://help.salesforce.com/articleView?id=000002493&type=1

Please mark this as SOLVED if it had helped you. Thanks!

Regards,
Krishna Avva