function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
lopezclopezc 

caused by: System.Exception: Too many SOQL queries: 21

hi All,

Everytime a contact is updated I have to look for the lead that matches with it and update it too. My problem is that I have a lot of Contact updated at the same time and they are imported with data Loader. At this time the ApexApplication give me a lot of error with this message:

contactAfterUpdate: execution of AfterUpdate

caused by: System.Exception: Too many SOQL queries: 21

Trigger.contactAfterUpdate: line 90, column 24

I attach you my code and the line 90 is in bold:

// List of Lead IDs
List<ID> leadIDs = new ID[] {};

// List to hold the leads that will be updated
List<Lead> leadsToUpdate = new Lead[] {};
// List to hold the Contacts that changed and have an open & active account now
List<Contact> ContactwithAccount = new Contact[] {};
//Map of Contacts, indexed by matching lead ids - added 03/04/08 SH
Map<ID, Contact> cMap = new Map<ID, Contact>();

// Loop through all the incoming contacts and get the lead ID
for(Integer j = 0; j<Trigger.new.size(); j++) {
//If the lead__c change from a value null to a value not null we have to look for the lead that marches the contact and update the lead
if (Trigger.new[j].Lead__c != null && Trigger.old[j].Lead__c == null){
ContactwithAccount.add(Trigger.new[j]);
leadIDs.add(Trigger.new[j].Lead__c);
//insert matching contact info into map - added 03/04/08 SH
cMap.put(Trigger.new[j].Lead__c, Trigger.new[j]);
}
}


// issue one query to get all leads for all incoming IDs and store in map (to avoid Apex restrictions)
Map<ID, Lead> matchingLeads = new Map<ID, Lead> ([SELECT Id, Status, MMB__c, Market_Update__c FROM Lead WHERE status = 'Open' and id IN :leadIDs]);
// loop through the incoming contacts again, for each, get the lead from the map
for(Contact loopContact : ContactwithAccount){
Lead foundLead = matchingLeads.get(loopContact.Lead__c);
if(foundLead!=null){
foundLead.Status = 'Converted';
foundLead.Date_Converted__c = System.today();
leadsToUpdate.add(foundLead);
}

}
// finally update any leads that have been marked for update
update leadsToUpdate;

//empty Lists for updated objects - added 03/04/08 SH
List<Event> updateE = new List<Event>();
List<Task> updateT = new List<Task>();
List<Note> insertN = new List<Note>();
List<Note> deleteN = new List<Note>();
List<Attachment> insertA = new List<Attachment>();
List<Attachment> deleteA = new List<Attachment>();
List<CampaignMember> insertCM = new List<CampaignMember>();
List<CampaignMember> deleteCM = new List<CampaignMember>();

//loop through lead events and assign them to corresponding contact from cMap - added 03/04/08 SH
for (Event[] arre : [Select Id, WhoId from Event where WhoId IN :matchingLeads.KeySet()]) {
for(Event e : arre){
e.WhoId = cMap.get(e.WhoId).Id;
updateE.add(e);
}
}
update updateE;

//loop through lead tasks and assign them to corresponding contact from cMap - added 03/04/08 SH
for (Task[] arrt : [Select Id, WhoId from Task where WhoId IN :matchingLeads.KeySet()]) {
for(Task t : arrt){
t.WhoId = cMap.get(t.WhoId).Id;
updateT.add(t);
}
}
update updateT;

//loop through campaign member records for lead and recreate for contact, then delete lead version
for (CampaignMember[] arrcm : [Select Id, ContactId, LeadId, CampaignId, Status from CampaignMember where LeadId IN :matchingLeads.KeySet()]) {
for(CampaignMember cm : arrcm){
CampaignMember cmNew = new CampaignMember(CampaignId = cm.CampaignId, ContactId = cMap.get(cm.LeadId).Id, Status = cm.Status);
insertCM.add(cmNew);
deleteCM.add(cm);
}
}
insert insertCM;
delete deleteCM;

for (Note[] arrn : [Select Body, Id, IsPrivate, OwnerId, ParentId, Title from Note where ParentId IN :matchingLeads.KeySet()]) {
for(Note n : arrn){
Note nNew = new Note(Body = n.Body, IsPrivate = n.IsPrivate, OwnerId = n.OwnerId, Title = n.Title, ParentId = cMap.get(n.ParentId).Id);
insertN.add(nNew);
deleteN.add(n);
}
}
insert insertN;
delete deleteN;

for (Attachment[] arra : [Select Id, Body, BodyLength, ContentType, IsPrivate, Name, OwnerId, ParentId from Attachment where ParentId IN :matchingLeads.KeySet()]) {
for(Attachment a : arra){
Attachment aNew = new Attachment(Body = a.Body, ContentType = a.ContentType, IsPrivte = a.IsPrivate, Name = a.Name, OwnerId = a.OwnerId, ParentId = cMap.get(a.ParentId).Id);
insertA.add(aNew);
deleteA.add(a);
}
}
insert insertA;
delete deleteA;
}


I guess that there are too many query in that line but I don't know I could do it in another way... any help???

Many Thanks



BoxBox

Your solution is to process your data in bulk so that you are not making calls to the database for each member of the trigger.  Always remember that in one SOQL call you can retrieve many records so if you construct sets of records that you want to process and retrieve in one call you will not upset the governor.

If you work through the cookbook and the APEX manual there are some very good examples of how to achieve bulk processing on the platform.

lopezclopezc
Hi!

I have been readying the documentation and I try to use Maps:

Note[] arrn = [Select Body, Id, IsPrivate, OwnerId, ParentId, Title from Note where ParentId IN :matchingLeads.KeySet()];
      for(Note n : arrn){
        Note nNew = new Note(Body = n.Body, IsPrivate = n.IsPrivate, OwnerId = n.OwnerId, Title = n.Title, ParentId = cMap.get(n.ParentId).Id);
        insertN.add(nNew);
        deleteN.add(n);
      }

So Here, I take the notes from the Leads that I want to convert and attach them to the new contact. But I don't know why it doesn't like it. I don't think I am retriving more than 21 records anyway and If I do, 21 is not too many, isn't it? There is no more than 10 matchingLeads either. Do you know what I am doing wrong?