0

I have a trigger(AI/AU) on Event staging object, which creates Events once the records are inserted into Event Staging.

I am getting System.LimitException: Too many query rows: 50001 exception because there are 4-5 SOQl queries within helper class which fetches more than 50k records.

I need list of all user,leads and contacts email ids in 3 seperate lists(around 65k count). I need to check if the recepeint/Cc fields email ids already exist in that list and process them if so.

How do i make a check of more than 50k records.

I am calling this class from trigger.

TRIGGER::

trigger EventnEventRelationtrigger on Event_Staging__c(after update,after Insert) {



If(trigger.isAfter){
    If(Trigger.isInsert){
        if(EventnEventRelation.runOnce()){
        EventnEventRelation ERI=new EventnEventRelation();
        ERI.CreateEvent(Trigger.new);
        //ERA.DeleteOldEventStagings();
        }
        }


        If(Trigger.isupdate){
        if(EventnEventRelation.runOnce()){
        EventnEventRelation ERU=new EventnEventRelation();
        ERU.UpdateEvent(Trigger.new);        
    }
    }    

}}


CLASS::

Global class EventnEventRelation implements schedulable{
Public EventnEventRelation()
{

}
private static boolean run = true;
public static boolean runOnce(){
if(run){
    run=false;
    return true;
}else{
    return run;
}
}

global void execute(SchedulableContext sc)
{
    DeleteOldEventStagings();
}

Public void CreateEvent(List<Event_Staging__c> ESList)
{
    Set<Id> successfulEventIds = new Set<Id>();
    Set<string> UserEmailset=new set<string>();
    Set<string> allUserEmailset=new set<string>();
    Set<string> ConEmailset=new set<string>();
    Set<string> LeadEmailset=new set<string>();
    set<string> allemailset=new set<string>();
    //fetch list of all active Users
    List<profile> profids=[Select Id,name From profile where Name LIKE '%Chatter%'];
    List<user> userlist=[Select id,Name,Email,profileid FROM User where IsActive = TRUE AND profileId not IN:profids ];
    List<user> userlistall=[Select id,Name,Email FROM User where IsActive = TRUE  ];
    List<contact> conlist=[select id,name,email FROM contact];
    List<Lead> Leadlist=[select id,name,email FROM Lead];
    //Create a map of emailId and ID of users
    map<string,id> UserMap=new map<string,id>();
    map<string,id> UserMapall=new map<string,id>();
    map<string,id> conMap=new map<string,id>();
    map<string,id> leadMap=new map<string,id>();
    for(user us:userlist)
    {
        UserMap.put(us.email,us.id);
    }
    for(user usa:userlistall)
    {   
        allUserEmailset.add(usa.email);
        UserMapall.put(usa.email,usa.id);
    }

    List<Event_Staging__c  > Eventstagelist=[Select Event_Created_By__c,Meeting_UID__c,Event_Attendees__c,Event_CC__c FROM Event_Staging__c];
    //Create map of meetingUID and eventattendeed of eventstaging
    map<string,string> AttendeesMap=new map<string,string>();
    for( Event_Staging__c  Evs:Eventstagelist)
    {
        AttendeesMap.put(Evs.Meeting_UID__c,Evs.Event_Attendees__c+';'+Evs.Event_CC__c);
    }

    List<event> eventlist=new list<event>();
    List<eventrelation> eventrelationlist=new list<eventrelation>();
    List<eventrelation> maineventrelationlist=new list<eventrelation>();
    List<string> ErrormessageList = New List<string>();
    for(User Uu:userlist)
    {
        UserEmailset.add(uu.Email);
        //system.debug('User Email--------->'+uu.Email);
    }

    for(contact cc:conlist)
    {
        ConEmailset.add(cc.Email);
        conmap.put(cc.email,cc.id);
        //system.debug('User Email--------->'+cc.Email);
    }
    for(Lead le:Leadlist)
    {
        LeadEmailset.add(le.Email);
        leadmap.put(le.email,le.id);
        //system.debug('User Email--------->'+le.Email);
    }
    system.debug('users size------->'+UserEmailset.size());
    system.debug('contacts size------->'+ConEmailset.size());
    system.debug('Leads size------->'+LeadEmailset.size());

    system.debug('UserEmailset size>>>>---->>>>'+allemailset.size());
    For(Event_Staging__c ES: ESList)
    {
        If(UserEmailset.contains(ES.Event_Created_By__c) )
        {
            Event ev = new Event();
            Id UserId=UserMap.get(ES.Event_Created_By__c);
            ev.ownerId=UserId;
            system.debug('Event created By'+UserId);
            ev.Subject=ES.Event_Subject__c;
            ev.Location=ES.Event_Location__c;
            ev.StartDateTime=ES.Event_Start_Date__c;
            ev.EndDateTime=ES.Event_End_Date__c;
            ev.Description=ES.Meeting_UID__c;
            if(ES.Event_Duration_in_Minutes__c!=NULL){
            ev.DurationInMinutes=ES.Event_Duration_in_Minutes__c.intvalue(); }
            eventlist.add(ev);               
        }
        else
        {
            Event evt = new Event();
            evt.ownerId=UserInfo.getUserId();
            evt.Subject=ES.Event_Subject__c;
            evt.Location=ES.Event_Location__c;
            evt.StartDateTime=ES.Event_Start_Date__c;
            evt.EndDateTime=ES.Event_End_Date__c;
            evt.Description=ES.Meeting_UID__c;
            if(ES.Event_Duration_in_Minutes__c!=NULL){
            evt.DurationInMinutes=ES.Event_Duration_in_Minutes__c.intvalue(); }
            eventlist.add(evt);

        }

    }

    if(eventlist.size()>0 && eventlist!=NULL)
    { 
    try {
        Database.SaveResult[] EventResultList = Database.insert(eventlist,false);
        if (EventResultList != null){
            for (Database.SaveResult er : EventResultList) {
                if (er.isSuccess()) {
                    successfulEventIds.add(er.getId()); 
                    System.debug('Successfully inserted Event. Event ID: ' + er.getId());
                }

                else {
                    Database.Error[] errl = er.getErrors();
                    for(Database.Error err : errl){
                        ErrormessageList.add(err.getMessage()); 
                        System.debug(err.getStatusCode() + ' - ' + err.getMessage());
                    }
                }
            }
            If(ErrormessageList.size()>0 && ErrormessageList!=NULL)
            {
                sendmail(ErrormessageList);
            }
        }

        } catch (Exception e) {
            System.debug(e.getTypeName() + ' - ' + e.getCause() + ': ' + e.getMessage());
            }
    }
    List<Event> insertedEvents=[select id,description from Event WHERE id IN:successfulEventIds];
    set<string> EventMeetingUIDSet=new set<string>();
    List<Event_Staging__c> UpdateEventstaging = new list<Event_Staging__c>();

    list<Event_Staging__c> Eventsup = new list<Event_Staging__c>();
    Map<string,id> EventMap=new Map<string,id>();
    for(Event e:insertedEvents){
        EventMap.put(e.description,e.id);
    }

    for(Event ECB : insertedEvents)
    {
        EventMeetingUIDSet.add(ECB.description);
    }

    for(Event_Staging__c  EVSCB :[select id,Event_Subject__c,Meeting_UID__c FROM Event_Staging__c] )
    {
        If(EventMeetingUIDSet.contains(EVSCB.Meeting_UID__c))
        {
            EVSCB.IsEventCreated__c=TRUE;
            EVSCB.Event_ID__c =EventMap.get(EVSCB.Meeting_UID__c);
            UpdateEventstaging.add(EVSCB);
        }
    }

    If(UpdateEventstaging.size()>0 && UpdateEventstaging!=NULL)
    {
        update UpdateEventstaging;
    }

     for(event ev:eventlist)
    {
            string str=AttendeesMap.get(ev.Description);
            system.debug('UserMapall size---------'+UserMapall.size());
            system.debug('AttendeesMap size---------'+AttendeesMap.size());
            system.debug('string --------'+str);

            List<String> ReciepientMail= str.split(';');
            system.debug('ReciepientMail size------------' +ReciepientMail.size());
            system.debug('ReciepientMail size 1------------' +ReciepientMail[0]);
            system.debug('ReciepientMail size 1------------' +ReciepientMail[1]);
            system.debug('allUserEmailset>>>>---->>>>----' +allUserEmailset.size());

            for(integer i=0;i<ReciepientMail.size();i++)
            {
                Eventrelation evr=new eventrelation();
                If(allUserEmailset.contains(ReciepientMail[i]))
                {
                    evr.relationid=UserMapall.get(ReciepientMail[i]);
                }

                else If(ConEmailset.contains(ReciepientMail[i]))
                {
                    evr.relationid=conMap.get(ReciepientMail[i]);
                }

                else if(LeadEmailset.contains(ReciepientMail[i]))
                {
                    evr.relationid=leadMap.get(ReciepientMail[i]);
                }

                evr.eventid=ev.Id;
                evr.status='New';
                eventrelationlist.add(evr);

            }
            maineventrelationlist.addall(eventrelationlist);
            eventrelationlist.clear();
    }

    if(maineventrelationlist.size()>0 && maineventrelationlist!=NULL)
    {
    try {
        Database.SaveResult[] EventRelationResultList = Database.insert(maineventrelationlist,false);
        if (EventRelationResultList != null){
            for (Database.SaveResult sr : EventRelationResultList) {
                if (!sr.isSuccess()) {
                    Database.Error[] errlist = sr.getErrors();
                    for(Database.Error errr : errlist)
                        System.debug(errr.getStatusCode() + ' - ' + errr.getMessage());

                }
            }
        }
    } catch (Exception e) {
    System.debug(e.getTypeName() + ' - ' + e.getCause() + ': ' + e.getMessage());
    }
    }   
}    

Public void UpdateEvent(List<Event_Staging__c> updateESList)
{
    //List<Event_Staging__c>  ListEventstaging = updateESList;
    system.debug('updateESList size--------------'+updateESList.size());
    List<string> DelEvents=new List<string>();
    for(Event_Staging__c ESdel: updateESList){
        DelEvents.add(ESdel.Event_ID__c);
    }
    system.debug('DelEvents size--------------'+DelEvents.size());
    List<Event> DelList=[select id from Event WHERE id IN: DelEvents];
    system.debug('DelList size--------------'+DelList.size());
    If(DelList.size()>0 && DelList!=NULL)
    {
        Delete DelList;
    }
    createEvent(updateESList);
}

Public void sendmail(List<string> ErrorRecords)
{
      Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
      email.setSubject('Failed Events');
      email.setBccSender(false);    
      //string[] toaddress = New String[] {UserInfo.getUserEmail()};
      string[] toaddress = New String[] {'[email protected]'};
      //email.setCcAddresses(new String[] {'[email protected]'});    
      email.setSaveAsActivity(false); 
      String s='Hello '+UserInfo.getName()+',';
      s += '\n\nThere are '+ ErrorRecords.size() + ' records which did not get created as salesforce Events.\n';
      s += 'The following are the reasons for the failures:\n\n';
      integer i=0;
      for(string st : ErrorRecords){
          string ss=ErrorRecords.get(i);
          system.debug('Error in record---->>>>'+ss);
          i++;
          s +='\n'+ String.ValueOf(i) + '. ' + ss ;
      }
      s+='\n\nRegards,\n\nSalesforce Support Team';    
      email.setPlainTextBody(s);
      email.setToAddresses(toaddress);
      Messaging.SendEmailResult [] res =Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

}

Public void DeleteOldEventStagings()
{
    List<Event_Staging__c > DelList=new List<Event_Staging__c>();
    DelList=[Select Id,name,Event_Subject__c,IsEventCreated__c from Event_Staging__c where CreatedDate < LAST_N_DAYS:07 AND Event_End_Date__c < TODAY];
    If(DelList.size()>0 && DelList!=NULL){
    Delete DelList;
    }
}

}

Immediate help required. Thanks in advance.

Improve this question

1 Answer 1

Reset to default
0

Although there could be many improvements that can be suggested on the above code, here are some specific points to help you:

  • We should design our triggers in such a way that data they access is limited based on records coming into trigger. For example if I want to make sure that I do not insert a duplicate contact with same email, I will try to find out contacts that are in database and are of same email address as coming in trigger. I should not pull all possible contacts and then iterate to find a match.
  • As part of solution you can create a set similar to

public void CreateEvent(List<Event_Staging__c> ESList) { Set<String> emailSet=new Set<String> (); for(Event_Staging__c es:ESList){ emailSet.add(es.Event_Created_By__c) }

and then can use it in your subsequesnt SOQLs like List<user> userlist=[Select id,Name,Email,profileid FROM User where IsActive = TRUE AND profileId not IN:profids and email in:emailSet ]; List<user> userlistall=[Select id,Name,Email FROM User where IsActive = TRUE and email in:emailSet]; List<contact> conlist=[select id,name,email FROM contact and email in:emailSet]; List<Lead> Leadlist=[select id,name,email FROM Lead and email in:emailSet];

This will limit the number of records accessed by your code and thereby will resolve your problem.

Improve this answer
2
  • Thanks Mukesh..adding to that, I habe a query. I have 10 SOQL queries in my helper class.. I read trigger processes in batches of 200. if I insert 1 record, it firs once->10 SOQL will be consumed...... If I insert 199/200 records, it should be same......I wanted to know how many SOQL will be consumed if I insert 250 records...n how many SOQL will be consumed if I insert 5000 records in a go thorugh DL... I have this query from long time
    – Laxman
    Oct 5, 2016 at 13:33
  • the 200 batch division of trigger creates asynchronous threads. That means each thread will have its own governor limits. So overall SOQL usage would be same as used by 200 records. Record 201 -250 will be processed by different thread which will have new governor limits.
    – Mukesh Verma
    Oct 5, 2016 at 13:37

You must log in to answer this question.

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