+ Start a Discussion
raj kiranraj kiran 

Non-selective query against large object type error

Hi All,

I am getting the Non-selective query against large object type error when leads are getting inserted in the below trigger at Select_equipments_basedon_EquipIds function.right now the amount of equipment records are around 2,403,122.  
My questions are 
1) is there any way i can query all the data without adding the created date as where clasue ? as i need to query all the query during the trigger execution. 
2) If query data is not possible on all the data, can there is any alternative ??
3) i am planning to change the data type of lead colum(Equip_ID_Serial_Contract__c) used as set string in below code to external id , so that i can pass the external id to the query for faster processing . I am not sure if this would be helpful there are some values null,just text,and even random text / numbers etc. 
 
trigger Lead_Owner_Assign_Marketo on Lead (before insert, before update,before delete) 
{

    List <Lead__c> leadEntry =  Lead__c.getall().values();    
    List <Product_Interest__c> PIEntry = Product_Interest__c.getall().values();
    List <Telequal_Queue_Status__c> TQStatus = Telequal_Queue_Status__c.getall().values();
    Account accRec;
    Lead oldRec;
    Lead leadOldRec;
    String PRIMARY = 'PRIMARY';
    String SECONDARY = 'SECONDARY';
    Boolean flag = false;
    Boolean gflag = false;
    Boolean billflag = false;
    Boolean UpdateFlag = False;
    Boolean MFound = False;
    Set<ID> accountIds = new Set<ID>();
    Set<String> marketoAccountIds = new Set<String>();
    Set<String> equipSerialContractIds = new Set<String>();
    Set<String> PostalCodes = new Set<String>();
    List<Equipment__c> listEquipments = new List<Equipment__c>();
    List<Contract__c> listContracts = new List<Contract__c>();
    List<Territory_User__c> listTerrAssignmts = new List<Territory_User__c>();
    List<ID> finalAccList = new List<ID>();
    List<Account> listAccounts = new List<Account>();
    List<AccountTeamMember> AccountTeamMembers = new List<AccountTeamMember>();
    Set<ID> allUserList = new Set<ID>();
    List<User> userDetailList = new List<User>();
    //Set<Id> level1ManagerIds = new Set<Id>();
    Map<String,ID> contractAccountMap = new Map<String,ID>();
    Map<String,List<Territory_User__c>> geoUserMap = new Map<String,List<Territory_User__c>>();
    Map<ID,Account>IdAccountMap = new Map<ID,Account>();
    Map<String,ID> RforceAccountIdMap = new Map<String,ID>();
    Map<ID,List<AccountTeamMember>> teamMemberMap = new Map<ID,List<AccountTeamMember>>();
    Map<ID,Boolean> userStatusMap = new Map<ID,Boolean>();
    Map<ID,String> User_ManagerEmail_Map = new Map<Id,String>();
    Map<ID,ID> level1ManagerMap = new Map<ID,ID>(); 
    Map<ID,String> Manager_ManagerLevel2Map = new Map<ID,String>();
    Map<String,Map<String,String>> LeadRoleMap = new Map<String,Map<String,String>>();
    //Restricting IS Updates based on IS User
    public string user2;
    if(!Test.isRunningTest()){
      Name_of_IS_User__c User1 =   Name_of_IS_User__c.getInstance();
      user2= user1.User_Name_of_IS_Interface_User__c  ;
    }
    else if(Test.isRunningTest()){
       user2 = 'is.integration@ricoh-usa.com';
    }
    
    Profile CurrentProfile = [Select Name from Profile where Id =: UserInfo.getProfileId()];
        
    
    Id AccId;
    Lead c = new Lead();
  
                
  
        
    List<RecordType> RecordTypeList = [Select Id,Name,SobjectType,IsActive From RecordType where SobjectType = 'Lead' AND IsActive = True];
    Map<String,ID> RecordIdNameMap = new Map<String,ID>();
    for(RecordType recType:RecordTypeList)
    {
        RecordIdNameMap.put(recType.Name,recType.Id);
    }
    
    List<QueueSobject> que = [Select Id, SobjectType, QueueId, Queue.Name from QueueSobject where SobjectType = 'Lead'];
    Map<String,ID> queMap = new Map<String,ID>();
    Map<String,Boolean> queStatMap = new Map<String,Boolean>();
    
    system.debug('****Building Queues Map****');
    for(QueueSobject ques: que)
    {                
        queMap.put(ques.Queue.Name,ques.QueueId);                
    }
    system.debug('****Queue Map****'+queMap);
    for(Telequal_Queue_Status__c tqs: TQStatus)
    {
        queStatMap.put(tqs.Name,tqs.Queue_Status__c);
    }
    system.debug('****Telequalification Queue Status Map****');
    if(!NonBatchStaticHelper.hasTaskOpptySkipVariable())
    {
        if(!UserInfo.getUserName().Contains(user2))
        {
            system.debug('****inside LeadConverisonStaticHelper check ****');
            if(trigger.isInsert || trigger.isUpdate)
            {
                for(Lead newLead: Trigger.new)
                {
                    newLead.Enterprise_Services_Opp__c = newLead.Enterprise_Services__c;
                    newLead.Equipment_Opp__c = newLead.Equipment__c;
                    newLead.IT_Services_Opp__c = newLead.IT_Services__c;
                    newLead.Other_Not_Yet_Determined_Opp__c = newLead.Other_Not_Yet_Determined__c;
                    newLead.Print_Assessment_Opp__c = newLead.Print_Assessment__c;
                    newLead.Production_Print_Opp__c = newLead.Production_Print__c;
                    newLead.Service_Contract_Opp__c = newLead.Service_Contract__c;
                    newLead.Supplies_Printers_Opp__c = newLead.Supplies_Printers__c;
                    newLead.Sub_Source__c = newLead.Lead_Sub_Source__c;
                    newLead.Sub_Source_Contact__c = newLead.Lead_Sub_Source__c;
                    newLead.LDS_Opp__c=newLead.LDS__c;    
                    newLead.RPPS_Opp__c=newLead.RPPS__c;  

                    system.debug('****Build All Necessary Lists for SOQL query****');
                    if(Trigger.isUpdate && newLead.OwnerId != queMap.get('SSA Queue') && newLead.OwnerId != queMap.get('Telequalification Queue'))
                    {
                        allUserList.add(newLead.OwnerId);   
                    }
                    if(newLead.Account_Name_1__c != Null)
                    {
                        accountIds.add(newLead.Account_Name_1__c);  
                    }
                    else
                    {
                        if(newLead.Marketo_Account_ID__c!= Null && !newLead.Account_Delete_Flag__c)
                        {
                            marketoAccountIds.add(newLead.Marketo_Account_ID__c);    
                        }
                        if(newLead.Equip_ID_Serial_Contract__c!= Null && !newLead.Account_Delete_Flag__c)
                        {
                            equipSerialContractIds.add(newLead.Equip_ID_Serial_Contract__c);    
                        }
                        if(newLead.Shipping_Postal_Code__c!= Null)
                        {
                            PostalCodes.add(newLead.Shipping_Postal_Code__c);
                        }
                        if(newLead.PostalCode!= Null)
                        {
                            PostalCodes.add(newLead.PostalCode);
                        }
                    }
                    
                    if(newLead.Creation_Mode__c!=null)
                    {
                        system.debug('****Edge/IKON.COM Lead****');
                        if(newLead.Lead_Type__c=='Equipment')
                        {
                            newLead.Equipment__c = True;                  
                        }                  
                        else if(newLead.Lead_Type__c=='Service Contract')
                        { 
                            newLead.Service_Contract__c = True;                  
                        }                  
                        else if(newLead.Lead_Type__c=='Supplies / Printers')
                        { 
                            newLead.Supplies_Printers__c = True;                 
                        }                  
                        else if(newLead.Lead_Type__c=='Print Assessment')
                        {   
                            newLead.Print_Assessment__c = True;                 
                        }
                    }
                    
                    if(Trigger.isUpdate)
                    {
                    
                        leadOldRec = Trigger.oldMap.get(newLead.Id);
                        if(leadOldRec.OwnerId!=newLead.OwnerId)
                        {
                            if(UserInfo.getUserId() == leadOldRec.OwnerId && CurrentProfile.Name=='Ricoh Sales Rep' 
                                && newLead.OwnerId != queMap.get('SSA Queue') && newLead.OwnerId != queMap.get('Telequalification Queue'))
                            {
                                newLead.addError('You can not transfer this lead to another Sales Rep');
                            }
                            else if(UserInfo.getUserId() == leadOldRec.OwnerId && CurrentProfile.Name=='Ricoh Sales Rep' 
                                && (newLead.OwnerId != queMap.get('SSA Queue') || newLead.OwnerId != queMap.get('Telequalification Queue')))
                            {
                                newLead.Manually_Assigned__c = True;
                            }
                            else if(CurrentProfile.Name=='Ricoh Sales Process Team' || CurrentProfile.Name=='Ricoh Sales Support Analyst')
                            {
                                newLead.Manually_Assigned__c=True;
                            }
                        }
                        
                        
                        c=newLead; 
                        
                    }
                    
                       
                        AccId=newLead.Account_Name_1__c;
                        c=newLead;
                        
                } 
            }
			if(!equipSerialContractIds.isEmpty())
            {
                listEquipments = Select_equipments_basedon_EquipIds(equipSerialContractIds); 
                if(!listEquipments.isEmpty())
                {
                    system.debug('****Collect all the AccountIds linked to Equipments****');
                    for(Equipment__c eq: listEquipments)
                    {
                        accountIds.add(eq.Account__c);    
                    }
                }
                
            }
        
}
}

        public List<Equipment__c> Select_equipments_basedon_EquipIds(Set<String> equipSerialIds){
            List<Equipment__c> equipList = [Select Id, Account__c,ORC_equip_id__c,Name From Equipment__c Where Name IN:equipSerialIds OR ORC_equip_id__c IN:equipSerialIds];
            return equipList;
        } 
        }
Any light on this will be of great help 
Thanks in advance .

Regards,
Raj