0

Followed trigger best practise.No queries inside the for Loop.

Error Msg:

caused by: System.QueryException: Non-selective query against large object type (more than 200000 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)
    1. 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)

This is the payload:

<Case_Migration__c>

    <Case__c>5000t000005kd6TAAQ</Case__c>

    <LegacyAccountId__c>0013400001TlWD5AAN</LegacyAccountId__c>

     <LegacyEntitlementId__c>550800000010EacAAE</LegacyEntitlementId__c>

     <LegacyProductId__c>01t1b000001IaY2AAK</LegacyProductId__c>

     <LegacyCreatedById__c>00580000001uegcAAA</LegacyCreatedById__c>

     <LegacyLastModifiedById__c>00580000001uegcAAA</LegacyLastModifiedById__c>

 <Legacy_Current_Action_Owner__c>akpabc2344abcde</Legacy_Current_Action_Owner__c>

  <Legacy_Escalated_By__c>akpabc2344abcde</Legacy_Escalated_By__c>

  <Legacy_Reported_By__c>akpabc2344abcde</Legacy_Reported_By__c>

  <Legacy_Transferred_By__c>akpabc2344abcde</Legacy_Transferred_By__c>

  <LegacyContactId__c>00334000020UxdUAAS</LegacyContactId__c>

  <Legacy_GSS_Alternate_Contact__c>akpabc2344abcde</Legacy_GSS_Alternate_Contact__c>

  <Legacy_Customer_Contact_Name__c>akpabc2344abcde</Legacy_Customer_Contact_Name__c>

  <LegacyParentId__c>akpabc2344abcde</LegacyParentId__c>

  <Legacy_GSS_Duplicate_Case__c>akpabc2344abcde</Legacy_GSS_Duplicate_Case__c>

  <Legacy_Duplicate_Of_Case__c>akpabc2344abcde</Legacy_Duplicate_Of_Case__c>

  <Legacy_CAP_Managed_Request__c>akpabc2344abcde</Legacy_CAP_Managed_Request__c>

  <LegacyOwnerId__c>00580000001uegcAAA</LegacyOwnerId__c>

  <Legacy_Product_Build_Number__c>NA</Legacy_Product_Build_Number__c>

  <Legacy_Product_Version__c>NA</Legacy_Product_Version__c>

  <LegacyBusinessHoursId__c>01m40000000CglEAAS</LegacyBusinessHoursId__c>

  <VMstar_ID__c>5001b000004Y9RHAA0</VMstar_ID__c>

</Case_Migration__c>

This Line causes the error: 

for(Case et : [select id,Vmstar_Id__c,GSS_Duplicate_Case__c,Duplicate_Of_Case__c,GSS_Build_Number__c, 
  GSS_Product_Version__c from Case 
                       where Vmstar_Id__c IN : SetOfParentId 
                       OR  Vmstar_Id__c IN :SetOfGSSDuplicateCase 
                       OR Vmstar_Id__c IN :SetOfLegacyDuplicateOfCase
                       OR GSS_Build_Number__c in :Set1
                       OR GSS_Product_Version__c in :Set11]){
                           if(et.id!=null){
                               MapOfParentId.put(et.Vmstar_Id__c,et.id);
                           }if(et.GSS_Duplicate_Case__c!=null){
                               MapOfGSSDuplicateCase.put(et.VMstar_Id__c,et.GSS_Duplicate_Case__c);              
                           }if(et.Duplicate_Of_Case__c!=null){
                               MapOfDuplicateOfCase.put(et.VMstar_Id__c,et.Duplicate_Of_Case__c);
                           }if(et.GSS_Build_Number__c!=null){
                               Set3.add(et.GSS_Build_Number__c);
                           }if(et.GSS_Product_Version__c!=null){
                               Set33.add(et.GSS_Build_Number__c);
                           }
                       }

Trigger class Code:

public static void PopulateCaseValues(List<Case_Migration__c> triggerNew){  
    Map<Id,String> mapOfLegacyAccountId = new Map<Id,String>();
    Map<String,Id> mapOfAccount = new Map<String,Id>();
    Map<Id,String> MapOfLegacyEntitlementId = new Map<Id,String>();
    Map<String,Id> MapOfEntitlementId = new Map<String,Id>();

    Map<Id,String> MapOfLegacyProductId = new Map<Id,String>();
    Map<String,Id> MapOfProductId = new Map<String,Id>();


    Map<Id,String> MapOfLegacyCurrentActionOwner = new Map<Id,String>();
    Map<String,Id> MapOfCurrentActionOwner = new Map<String,Id>();

    Map<Id,String> MapOfLegacyEscalatedBy = new Map<Id,String>();
    Map<String,Id> MapOfEscalatedBy = new Map<String,Id>();

    Map<Id,String> MapOfLegacyReportedBy = new Map<Id,String>();
    Map<String,Id>  MapOfReportedBy= new Map<String,Id>();

    Map<Id,String> MapOfLegacyTransferredBy = new Map<Id,String>();
    Map<String,Id> MapOfTransferredBy = new Map<String,Id>();

    Map<Id,String> MapOfLegacyContactId = new Map<Id,String>();
    Map<String,Id> MapOfContactId = new Map<String,Id>();

    Map<Id,String> MapOfLegacyGSSAlternateContact = new Map<Id,String>();
    Map<String,Id> MapOfGSSAlternateContact = new Map<String,Id>();

    Map<Id,String> MapOfLegacyCustomerContactName = new Map<Id,String>();
    Map<String,Id> MapOfCustomerContactName = new Map<String,Id>();

    Map<Id,String> MapOfLegacyCAPManagedRequest = new Map<Id,String>();
    Map<String,Id> MapOfCAPManagedRequest = new Map<String,Id>();

    Map<Id,String> MapOfLegacyOwnerId = new Map<Id,String>();
    Map<String,Id> MapOfOwnerId = new Map<String,Id>();

    Map<Id,String> MapOfLegacyBusinessHoursId = new Map<Id,String>();
    Map<String,Id> MapOfBusinessHoursId = new Map<String,Id>();
    set<string> SetOfBusinessHoursId=new set<string>();

    Map<Id,String> MapOfLegacyParentId= new Map<Id,String>();
    Map<String,Id> MapOfParentId = new Map<String,Id>();        
    set<string> SetOfParentId=new set<string>();

    Map<Id,String> MapOfLegacyGSSDuplicateCase = new Map<Id,String>();
    Map<String,Id> MapOfGSSDuplicateCase = new Map<String,Id>();        
    set<string> SetOfGSSDuplicateCase=new set<string>();

    Map<Id,String> MapOfLegacyDuplicateOfCase = new Map<Id,String>();
    Map<String,Id> MapOfDuplicateOfCase = new Map<String,Id>();        
    set<string> SetOfLegacyDuplicateOfCase=new set<string>();

    //18.Legacy_Product_Build_Number__c
    Map<Id,String> MapOfLegacyProductBuildNumber = new Map<Id,String>();
    Map<String,Id> MapOfProductBuildNumber = new Map<String,Id>();

    set<string> Set1=new set<string>(); 
    set<string> Set2=new set<string>(); 
    set<string> Set3=new set<string>(); 
    set<string> Set4=new set<string>(); 

    Map<Id,String> MapOfLegacyProductVersion = new Map<Id,String>();
    Map<String,Id> MapOfProductVersion = new Map<String,Id>();

    set<string> Set11=new set<string>(); 
    set<string> Set22=new set<string>(); 
    set<string> Set33=new set<string>(); 
    set<string> Set44=new set<string>();


    for(Case_Migration__c cm : triggerNew){

        if(cm.LegacyAccountId__c!=null){
            mapOfLegacyAccountId.put(cm.Case__c, cm.LegacyAccountId__c);
        }
        if(cm.LegacyEntitlementId__c!=null){
            MapOfLegacyEntitlementId.put(cm.Case__c, cm.LegacyEntitlementId__c);
        }
        if(cm.LegacyProductId__c!=null){
            MapOfLegacyProductId.put(cm.Case__c, cm.LegacyProductId__c);

        }
        if(cm.Legacy_Current_Action_Owner__c!=null){
            MapOfLegacyCurrentActionOwner.put(cm.Case__c, cm.Legacy_Current_Action_Owner__c);

        }
        if(cm.Legacy_Escalated_By__c!=null){
            MapOfLegacyEscalatedBy.put(cm.Case__c, cm.Legacy_Escalated_By__c);

        }
        if(cm.Legacy_Reported_By__c!=null){
            MapOfLegacyReportedBy.put(cm.Case__c, cm.Legacy_Reported_By__c);

        }
        if(cm.Legacy_Transferred_By__c!=null){
            MapOfLegacyTransferredBy.put(cm.Case__c, cm.Legacy_Transferred_By__c);

        }
        if(cm.LegacyContactId__c!=null){
            MapOfLegacyContactId.put(cm.Case__c, cm.LegacyContactId__c);

        }
        if(cm.Legacy_GSS_Alternate_Contact__c!=null){
            MapOfLegacyGSSAlternateContact.put(cm.Case__c, cm.Legacy_GSS_Alternate_Contact__c);

        }
        if(cm.Legacy_Customer_Contact_Name__c!=null){
            MapOfLegacyCustomerContactName.put(cm.Case__c, cm.Legacy_Customer_Contact_Name__c);

        }
        if(cm.Legacy_CAP_Managed_Request__c!=null){
            MapOfLegacyCAPManagedRequest.put(cm.Case__c, cm.Legacy_CAP_Managed_Request__c);

        }
        if(cm.LegacyOwnerId__c!=null){
            MapOfLegacyOwnerId.put(cm.Case__c, cm.LegacyOwnerId__c);

        }
        if(cm.LegacyBusinessHoursId__c!=null){
            MapOfLegacyBusinessHoursId.put(cm.Case__c, cm.LegacyBusinessHoursId__c);

        }
        if(cm.Legacy_Product_Build_Number__c!=null && cm.LegacyProductId__c!=null){
            MapOfLegacyProductBuildNumber.put(cm.Case__c, cm.Legacy_Product_Build_Number__c);
            Set1.add(cm.Legacy_Product_Build_Number__c);
            Set2.add(cm.LegacyProductId__c);
        }
        if(cm.Legacy_Product_Version__c!=null && cm.LegacyProductId__c!=null){
            MapOfLegacyProductVersion.put(cm.Case__c, cm.Legacy_Product_Build_Number__c);
            set11.add(cm.Legacy_Product_Version__c);
            set22.add(cm.LegacyProductId__c);
        }
        if(cm.LegacyParentId__c!=null){
            MapOfLegacyParentId.put(cm.Case__c, cm.LegacyParentId__c);
            SetOfParentId.add(cm.LegacyParentId__c);

        }
        if(cm.Legacy_GSS_Duplicate_Case__c!=null){
            MapOfLegacyGSSDuplicateCase.put(cm.Case__c, cm.Legacy_GSS_Duplicate_Case__c);
            SetOfGSSDuplicateCase.add(cm.Legacy_GSS_Duplicate_Case__c);
        }
        if(cm.Legacy_Duplicate_Of_Case__c!=null){
            MapOfLegacyDuplicateOfCase.put(cm.Case__c, cm.Legacy_Duplicate_Of_Case__c);
            SetOfGSSDuplicateCase.add(cm.Legacy_Duplicate_Of_Case__c);
        }
    }

    if(!mapOfLegacyAccountId.isempty()){
        for(Account acc : [select id,Vmstar_Id__c from Account where Vmstar_Id__c IN : mapOfLegacyAccountId.values()]){
            mapOfAccount.put(acc.Vmstar_Id__c,acc.id);

        }
    }

    if(!MapOfLegacyEntitlementId.isempty()){
        for(Entitlement et : [select id,Vmstar_Id__c from Entitlement where Vmstar_Id__c IN : MapOfLegacyEntitlementId.values()]){
            MapOfEntitlementId.put(et.Vmstar_Id__c,et.id);
        }
    }

    if(!MapOfLegacyProductId.isempty()){
        for(Product2 et : [select id,Vmstar_Id__c from Product2 where Vmstar_Id__c IN : MapOfLegacyProductId.values()]){
            MapOfProductId.put(et.Vmstar_Id__c,et.id);
        }
    }

    if(!MapOfLegacyCurrentActionOwner.isempty()){
        for(User et : [select Vmstar_Id__c from User where Vmstar_Id__c IN : MapOfLegacyCurrentActionOwner.values()]){
            MapOfCurrentActionOwner.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(!MapOfLegacyEscalatedBy.isempty()){
        for(User et : [select id,Vmstar_Id__c from User where Vmstar_Id__c IN :MapOfLegacyEscalatedBy.values()]){
            MapOfEscalatedBy.put(et.Vmstar_Id__c,et.id);
        }
    }

    if(!MapOfLegacyReportedBy.isempty()){
        for(User et : [select id,Vmstar_Id__c from User where Vmstar_Id__c IN :MapOfLegacyReportedBy.values()]){
            MapOfReportedBy.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(!MapOfLegacyTransferredBy.isempty()){
        for(User et : [select id,Vmstar_Id__c from User where Vmstar_Id__c IN :MapOfLegacyTransferredBy.values()]){
            MapOfTransferredBy.put(et.Vmstar_Id__c,et.id);
        }
    }

    if(!MapOfLegacyContactId.isempty()){
        for(Contact et : [select id,Vmstar_Id__c from Contact where Vmstar_Id__c IN : MapOfLegacyContactId.values()]){
            MapOfContactId.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(!MapOfLegacyGSSAlternateContact.isempty()){        
        for(Contact et : [select id,Vmstar_Id__c from Contact where Vmstar_Id__c IN : MapOfLegacyGSSAlternateContact.values()]){
            MapOfGSSAlternateContact.put(et.Vmstar_Id__c,et.id);
        }
    }

    if(!MapOfLegacyCustomerContactName.isempty()){
        for(Contact et : [select id,Vmstar_Id__c from Contact where Vmstar_Id__c IN : MapOfLegacyCustomerContactName.values()]){
            MapOfCustomerContactName.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(!MapOfLegacyCAPManagedRequest.isempty()){
        for(CAP_Managed_Request__c et : [select id,Vmstar_Id__c from CAP_Managed_Request__c where Vmstar_Id__c IN :MapOfLegacyCAPManagedRequest.values()]){
            MapOfCAPManagedRequest.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(!MapOfLegacyOwnerId.isempty()){
        for(User et : [select id,Vmstar_Id__c from User where Vmstar_Id__c IN :MapOfLegacyOwnerId.values()]){    
            if(et.Vmstar_Id__c.startsWith('005')){
                MapOfOwnerId.put(et.Vmstar_Id__c,et.id);
            }
        }

    }

    if(!MapOfLegacyBusinessHoursId.isempty()){
        for(Group et : [select id,DeveloperName from Group where DeveloperName IN :MapOfLegacyBusinessHoursId.values()]){    
            if(et.DeveloperName.startsWith('00G')){
                MapOfOwnerId.put(et.DeveloperName,et.id);
            }
        }
    }

    if(SetOfBusinessHoursId.size()>0){
        for(Business_Hour__c et : [select id,Vmstar_Id__c from Business_Hour__c where Vmstar_Id__c IN : SetOfBusinessHoursId]){
            MapOfBusinessHoursId.put(et.Vmstar_Id__c,et.id);
        }
    }


    if(Set2.size()>0){
        for(Product2 ct:[select id,Vmstar_Id__c from Product2 where Vmstar_Id__c IN:Set2]){
            if(ct.Vmstar_Id__c!=null){
                system.debug('vmstarid:'+ct.Vmstar_Id__c);
                Set4.add(ct.id);
            }
        }
    }

    if(set22.size()>0){
        for(Product2 ct:[select id,Vmstar_Id__c from Product2 where Vmstar_Id__c IN:set22]){
            if(ct.Vmstar_Id__c!=null){
                Set44.add(ct.id);
            }
        }
    }

    SetOfParentId.remove(null);
    Set1.remove(null);
    SetOfGSSDuplicateCase.remove(null);
    SetOfLegacyDuplicateOfCase.remove(null);
    Set11.remove(null); 
    system.debug('SetOfParentId---------->'+SetOfParentId+'Set1------------->'+Set1+'SetOfLegacyDuplicateOfCase------->'+SetOfLegacyDuplicateOfCase+'Set11-------------->'+Set11+'SetOfGSSDuplicateCase---->'+SetOfGSSDuplicateCase);
    if(SetOfParentId.size()>0 || Set1.size()>0 || SetOfGSSDuplicateCase.size()>0 || SetOfLegacyDuplicateOfCase.size()>0 ||Set11.size()>0){
        for(Case et : [select id,Vmstar_Id__c,GSS_Duplicate_Case__c,Duplicate_Of_Case__c,GSS_Build_Number__c,GSS_Product_Version__c from Case 
                       where Vmstar_Id__c IN : SetOfParentId 
                       OR  Vmstar_Id__c IN :SetOfGSSDuplicateCase 
                       OR Vmstar_Id__c IN :SetOfLegacyDuplicateOfCase
                       OR GSS_Build_Number__c in :Set1
                       OR GSS_Product_Version__c in :Set11]){
                           if(et.id!=null){
                               MapOfParentId.put(et.Vmstar_Id__c,et.id);
                           }if(et.GSS_Duplicate_Case__c!=null){
                               MapOfGSSDuplicateCase.put(et.VMstar_Id__c,et.GSS_Duplicate_Case__c);              
                           }if(et.Duplicate_Of_Case__c!=null){
                               MapOfDuplicateOfCase.put(et.VMstar_Id__c,et.Duplicate_Of_Case__c);
                           }if(et.GSS_Build_Number__c!=null){
                               Set3.add(et.GSS_Build_Number__c);
                           }if(et.GSS_Product_Version__c!=null){
                               Set33.add(et.GSS_Build_Number__c);
                           }
                       }
    }
Improve this question

2 Answers 2

Reset to default
6

This error has to do with the trigger context, not with the literal location of the query in the source code. It looks like your query is located within a trigger handler, meaning it's executed after being called from the trigger and therefore is running in trigger context.

So let's look at this query.

SELECT id, Vmstar_Id__c, GSS_Duplicate_Case__c, Duplicate_Of_Case__c, GSS_Build_Number__c, GSS_Product_Version__c 
FROM Case 
WHERE Vmstar_Id__c IN : SetOfParentId 
    OR Vmstar_Id__c IN :SetOfGSSDuplicateCase 
    OR Vmstar_Id__c IN :SetOfLegacyDuplicateOfCase 
    OR GSS_Build_Number__c in :Set1 
    OR GSS_Product_Version__c in :Set11

We don't really care about the SELECT right now; we're only concerned with FROM and WHERE. Based on the error, we already know that the FROM object, Case, has more then 200,000 records and is therefore subject to selectivity rules, so we need to determine why the WHERE clause is non-selective.

We've got three fields here, Vmstar_Id__c, GSS_Build_Number__c, and GSS_Product_Version__c.

From Make SOQL query selective:

The performance of a SOQL will depend on the presence of a selective filter. If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be un-selective and will require a full table scan.

What we have here is a composite filter (joined by OR), so

The optimizer will determine if each of the simple filters is selective, and if in aggregate the number of returned records doesn't exceed the thresholds mentioned above. If these conditions are met, the filter will be selective.

For each of these filters and these three fields, then, we need to answer a couple of questions:

  • Is the field indexed?

  • Is the filter sufficiently narrow to pass the selectivity threshold of

    10 percent of the first million targeted records and 5 percent all records after that first million. In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records

    both individually and in combination?

You'll need to figure these things out based on your knowledge of the org's configuration, and by debugging the size and content of the Set variables you're using to filter your query.

For more information, read Working with Very Large SOQL Queries.

Fixes will be determined by what you find; you might need, for example, to request a custom index if none of these fields are indexed, or you might need to tweak your query and your application logic to allow yourself to filter on an index standard field first to make the query selective, or you might need to do some batching to ensure that your Sets don't pull back a result set that is larger than the selectivity threshold.

Possible Quick Fix

Make sure your Set filters don't contain a null value. (Not that the Set itself is null, but that a null value is added to the Set). That can easily cause selectivity failures because the filters match too many records.

Improve this answer
13
  • I havenot get this point? ..Fixes will be determined by what you find; you might need, for example, to request a custom index, or to tweak your query and your application logic to allow yourself to filter on an index standard field first.
    – Annappa
    Apr 22, 2019 at 11:57
  • if set size is going null how can I fix it
    – Annappa
    Apr 22, 2019 at 12:00
  • 1
    @SFDCFAN I'd really recommend reading through the entirety of the documentation I linked about determining selectivity. It's very situation-specific, and you may need to do some research in your org to determine exactly why this query is not being treated as selective. I've expanded my answer a bit in an effort to clarify.
    – David Reed ♦
    Apr 22, 2019 at 12:41
  • 1
    @SFDCFAN You need at least one selective filter, which does require an index. This is covered in detail in the linked documentation. It is a complex area of work and I really recommend reading through those pieces.
    – David Reed ♦
    Apr 22, 2019 at 16:59
  • 2
    @SFDCFAN I'm not sure how you meant that, but I don't really appreciate how the tone comes across. As I have already said several times, you'll need to do some legwork to determine exactly why this query isn't selective, based on the resources I've shared. Since I have said my piece, I'm not going to continue engaging in this thread.
    – David Reed ♦
    Apr 23, 2019 at 10:36
0

On a first attempt I would suggest to put all the values in these 3 sets- SetOfParentId, SetOfGSSDuplicateCase and SetOfLegacyDuplicateOfCase into a single set e.g. newSetOfIds and modify the query to : SELECT id, Vmstar_Id__c, GSS_Duplicate_Case__c, Duplicate_Of_Case__c, GSS_Build_Number__c, GSS_Product_Version__c FROM Case WHERE Vmstar_Id__c IN :newSetOfIds
OR GSS_Build_Number__c in :Set1 OR GSS_Product_Version__c in :Set11

If you still get the same error you should refer to what is sugested by @David Reed

Improve this answer
1
  • Tried. No luck..
    – Annappa
    Apr 23, 2019 at 4:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .