+ Start a Discussion
DBManagerDBManager 

"Too Many SOQL Queries" - but no obvious mistakes!

I am quite stuck after trying every trick I can find in the book, having done plenty of research into the classic "Too many SOQL queries" error.

 

I have, for example, made sure that there is no SELECT statement or UPDATE within a FOR loop (which seem to be the most common mistakes).

 

And yet still the error occurs!

 

Here is the error:

System.LimitException: Too many SOQL queries: 101

Class.RecordUpdate.updateInvoices: line 54, column 9 Trigger.DepartmentTrigger1: line 66, column 5

 Here is the Trigger that is causing the problem:

trigger DepartmentTrigger1 on Account (after update) {

  Set<Id> accountIds = new Set<Id>();


for (Integer i = 0; i < Trigger.new.size(); i++){
    if (
    //Changing btw two Parent Companies
        (Trigger.new[i].Parent_Company__c != Trigger.old[i].Parent_Company__c && 
        Trigger.new[i].Department__c == TRUE && Trigger.old[i].Department__c == TRUE)||
    //Changing to a Department
        (Trigger.new[i].Department__c == true && Trigger.old[i].Department__c == false)||
    // Changing from a Department
        (Trigger.new[i].Department__c == FALSE && Trigger.old[i].Department__c == TRUE)
        ) {
        accountIds.add(Trigger.new[i].Id);
    }
}
  
    //Update Contacts
    RecordUpdate.updateContacts(accountIds);
    
    //Update Deals
    RecordUpdate.updateDeals(accountIds);    

    //Update Sales Invoices
    RecordUpdate.updateInvoices(accountIds);
    
}

 And here is the Class that is referenced above:

public with sharing class RecordUpdate {

public static void updateContacts(Set<Id> contactset){
    Contact[] relatedContacts =
        [SELECT f.id, f.Accountid, f.Account.Department__c, f.Account.Parent_Company__c, f.Parent_Company__c
        FROM Contact f
        WHERE f.Accountid IN :contactset];

    for ( Contact childc : relatedContacts ) {
        if (childc.Account.Department__c == TRUE) {
            childc.Parent_Company__c = childc.Account.Parent_Company__c;
        }
        else
            childc.Parent_Company__c = childc.AccountId;
    }
    update relatedContacts;
}


public static void updateDeals(Set<Id> dealset){
    List<Opportunity> relatedDeals =
        [SELECT g.id, g.Accountid, g.Account.RecordTypeId, g.Account.Department__c, g.Account.Parent_Company__c, 
        g.Parent_Company__c, g.Agency_Client__r.id , g.Agency_Client__r.Department__c, g.Agency_Client__r.Parent_Company__c
        FROM Opportunity g
        WHERE g.Accountid IN :dealset OR g.Agency_Client__r.id IN :dealset];
    
    for ( Opportunity childo : relatedDeals ) {
        if (childo.Account.Department__c == TRUE) {
            childo.Parent_Company__c = childo.Account.Parent_Company__c;
        }
        else if (childo.Account.RecordTypeId == '01220000000Dqq6'){
            if (childo.Agency_Client__r.Department__c == TRUE){
                childo.Parent_Company__c = childo.Agency_Client__r.Parent_Company__c;
            }
            else
                childo.Parent_Company__c = childo.Agency_Client__c;
        }
        else
            childo.Parent_Company__c = childo.AccountId;
    }
    update relatedDeals;
}


public static void updateInvoices(Set<Id> invoiceset){
    Sales_Invoices__c[] relatedInvoices =
        [SELECT s.Id, s.Company__c, s.Company__r.Parent_Company__c, s.Company__r.Department__c, s.Parent_Company__c, s.Agency_Client__r.Id
        FROM Sales_Invoices__c s
        WHERE s.Company__c IN :invoiceset OR s.Agency_Client__c IN :invoiceset
        FOR UPDATE];
    
    for ( Sales_Invoices__c childs : relatedInvoices ) {
        if (childs.Company__r.Department__c == TRUE) {
            childs.Parent_Company__c = childs.Company__r.Parent_Company__c;
        }
        else
            childs.Parent_Company__c = childs.Company__c;
    }
    update relatedInvoices;
}

}

 

 There are a few other triggers that are involved, but they are tiny in comparison to the above example.

 

Thanks for any help you can offer.

Best Answer chosen by Admin (Salesforce Developers) 
DBManagerDBManager

After much trial and error, I made a simple change to Department Trigger 1 that I think will solve the problem. 

 

The fix was to add a "Set.Size() >0" condition to the references of the Update Records class. So the trigger now reads:

 

trigger DepartmentTrigger1 on Account (after update) {

Set<Id> accountIds = new Set<Id>();

for (Integer i = 0; i < Trigger.new.size(); i++){
    if (
        //Changing btw two Parent Companies
        (Trigger.new[i].Parent_Company__c != Trigger.old[i].Parent_Company__c && 
        Trigger.new[i].Department__c == TRUE && Trigger.old[i].Department__c == TRUE)||
        //Changing to a Department
        (Trigger.new[i].Department__c == true && Trigger.old[i].Department__c == false)||
          // Changing from a Department
        (Trigger.new[i].Department__c == FALSE && Trigger.old[i].Department__c == TRUE)
    ){
        accountIds.add(Trigger.new[i].Id);
        
    }
}

if(accountIds.size() >0){
    RecordUpdate.updateContacts(accountIds);
    RecordUpdate.updateDeals(accountIds); 
    RecordUpdate.updateInvoices(accountIds);   
}

}

 

Can someone please let me know what they think?

 

The SOQL statements have reduced to 36 and I will add the conditonal to the other triggers as I enter them, but I want to be sure this is a suitable fix.

 

Thanks.

All Answers

Sam27Sam27

Well That's right,

 

the SOQL query should have been executed only 3 times.......and as much in this there doesn't seem to be any problem.

 

I would suggest you check the logs, it might provide you more insight......

DBManagerDBManager

Is that the logs at the bottom of the page when you run the test?

 

I suppose i should  go through them - I've been putting it off because they're so long.... =\

 

But thanks for looking!

 

Anyone else?

 

 

dkadordkador

The logs will show you which queries are getting executed that many times.  That's your best bet for now.

spraetzspraetz

Comment out the other tests in the class and run tests on jsut that class.  You can then get a clearer picture of the logs.  Also, please include the other triggers that you think aren't relavent because based on what you posted, those other triggers might be culprit. =)

DBManagerDBManager

Thanks for your reply.

 

To begin with, here are the other triggers involved:

trigger AgencyTrigger4 on Account (after update) {

//Create an empty set of Id's
Set<Id> accountSet = new Set<Id>();

//Fill that set with all Accounts where the Record Type changes away from Agency
for (Integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.new[i].RecordTypeId != '01220000000Dqq6' && Trigger.old[i].RecordTypeId == '01220000000Dqq6') {
        accountSet.add( Trigger.new[i].Id);
    }
}

//Create a list of Accounts where the Agency is in the above set
Account[] updateAccounts = [SELECT b.Id, b.Agency_Lookup__c FROM Account b WHERE b.Agency_Lookup__c IN :accountSet];


for ( Account accts :updateAccounts ) {
        accts.Agency_Lookup__c = NULL;
}

//Run the update
update updateAccounts;

}

 Here:

trigger ContactTrigger3 on Contact (after insert, after update) {

Map<Id, Contact> contactsMap = new Map<Id, Contact>();

for (Integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.isInsert) {
    contactsMap.put( Trigger.new[i].Id, Trigger.new[i]);
    }
    else
    if (Trigger.new[i].Accountid != Trigger.old[i].Accountid)  {
        contactsMap.put( Trigger.new[i].Id, Trigger.new[i]);
    }
}

RecordUpdate.updateContacts(contactsMap.keyset());

//for (Contact[] updateContacts : 
//    [SELECT c.id, c.Parent_Company__c, c.Accountid, c.Account.Department__c, c.Account.Parent_Company__r.id
//    FROM Contact c
//    WHERE c.id IN :contactsMap.keyset()]){
    
//        for (Contact child :updateContacts) {
//            if (child.Account.Department__c == TRUE) {
//                child.Parent_Company__c = child.Account.Parent_Company__r.id;
//            }
//            else
//                child.Parent_Company__c = child.Accountid;
//        }
        
//    update updateContacts;
    
//}
}

 And here:

trigger OpporunityTrigger6 on Opportunity (after insert, after update) {

Map<Id, Opportunity> dealMap = new Map<Id, Opportunity>();

for (Integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.isInsert) {
    dealMap.put( Trigger.new[i].Id, Trigger.new[i]);
    }
    if (Trigger.isUpdate) {
        if (Trigger.new[i].Accountid != Trigger.old[i].Accountid)  {
            dealMap.put( Trigger.new[i].Id, Trigger.new[i]);
        }
        else
        if (Trigger.new[i].Agency_Client__c != Trigger.old[i].Agency_Client__c)  {
            dealMap.put( Trigger.new[i].Id, Trigger.new[i]);
        }
    }
}

RecordUpdate.updateDeals(dealMap.keyset());

//Opportunity[] dealstoUpdate =
//[SELECT c.id, c.Parent_Company__c, c.Accountid, c.Account.Department__c, c.Account.RecordTypeId, c.Account.Parent_Company__c, 
//c.Agency_Client__c, c.Agency_Client__r.Parent_Company__c, c.Agency_Client__r.Department__c
//FROM Opportunity c WHERE c.id IN :dealMap.keyset()];

  
//for (Opportunity child :dealstoUpdate) {
//    if (child.Account.Department__c == TRUE) {
//        child.Parent_Company__c = child.Account.Parent_Company__c;
//    }
//    else if (child.Account.RecordTypeId == '01220000000Dqq6'){
//        if (child.Agency_Client__r.Department__c == TRUE){
//            child.Parent_Company__c = child.Agency_Client__r.Parent_Company__c;
//        }
//        else
//            child.Parent_Company__c = child.Agency_Client__c;
//    }
//    else
//        child.Parent_Company__c = child.AccountId;
//}
        
//update dealstoUpdate;
    
}

 And here is the test class I am running:

@isTest
private class TestDepartmentConvert2 {

static TestMethod void TestCompanyChanges (){

//Create records
Account department1= new Account (Name='dept1');
insert department1;

Contact contact1 = new Contact (LastName='contact1', AccountId=department1.Id, RecordTypeId='01220000000Dqq9');

Opportunity deal1 = new Opportunity (Name='deal1', AccountId=department1.Id, Stagename='proposal', CloseDate=date.today());

Sales_Invoices__c invoice1 = new Sales_Invoices__c (Company__c=department1.Id, Parent_Company__c=department1.Id, SUN_Account_No__c='D');

Account parent1 = new Account (Name='parent1');

Account parent2 = new Account (Name='parent2');

Account agency1 = new Account(Name='Agency1', RecordTypeId='01220000000Dqq6');

Test.startTest();
insert contact1;
insert deal1;
insert invoice1;
insert parent1;
insert parent2;
insert agency1;
Test.stopTest();



//Update department
department1.Department__c=TRUE;
department1.Parent_Company__c=parent1.Id;
department1.RecordTypeId='012R00000004l05';
update department1;


//Update department with new parent
department1.Parent_Company__c=parent2.Id;
update department1;


//Update Department to non-department
department1.Department__c=FALSE;
department1.Parent_Company__c=NULL;
department1.RecordTypeId='01220000000Dqq8';
update department1;


//Update deal to new Company
deal1.AccountId=parent2.Id;
update deal1;


//Update parent2 to be rep by agency
parent2.Agency_lookup__c=agency1.Id;
update parent2;

//Update Deal to be for agency
deal1.AccountId=agency1.Id;
deal1.Agency_Client__c=parent2.Id;
update deal1;


//Update parent2 to Department
parent2.Department__c=TRUE;
parent2.Parent_Company__c=parent1.ID;
parent2.RecordTypeId='012R00000004l05';
update parent2;


//Update parent1 to be with agency
parent1.Agency_lookup__c=agency1.Id;
update parent1;


//Update Deal to different agency client
deal1.Agency_Client__c=parent1.Id;
update deal1;


//Update Contact to new Company
contact1.AccountId=parent2.Id;
update contact1;

//Update Agency to non-Agency
agency1.RecordTypeId='01220000000Dqq8';
update agency1;


//Delete Records
delete department1;

}
}

 

 

I will do some further testing today and see how it goes.

 

Thanks again.

DBManagerDBManager

So I refreshed the Sandbox - taking copies of all of the triggers and classes that were relevant.

 

I added my test class first, it got up to 13 SOQL queries.

 

I added the class I'm referencing from the Triggers next, still 13.

 

then I added the first Trigger I gave above - Department Trigger 1 and BAM! up to 60SOQL triggers.

 

So something is not right here... 

 

More testing required - but in the mean time, does anyone have any ideas?

DBManagerDBManager

After much trial and error, I made a simple change to Department Trigger 1 that I think will solve the problem. 

 

The fix was to add a "Set.Size() >0" condition to the references of the Update Records class. So the trigger now reads:

 

trigger DepartmentTrigger1 on Account (after update) {

Set<Id> accountIds = new Set<Id>();

for (Integer i = 0; i < Trigger.new.size(); i++){
    if (
        //Changing btw two Parent Companies
        (Trigger.new[i].Parent_Company__c != Trigger.old[i].Parent_Company__c && 
        Trigger.new[i].Department__c == TRUE && Trigger.old[i].Department__c == TRUE)||
        //Changing to a Department
        (Trigger.new[i].Department__c == true && Trigger.old[i].Department__c == false)||
          // Changing from a Department
        (Trigger.new[i].Department__c == FALSE && Trigger.old[i].Department__c == TRUE)
    ){
        accountIds.add(Trigger.new[i].Id);
        
    }
}

if(accountIds.size() >0){
    RecordUpdate.updateContacts(accountIds);
    RecordUpdate.updateDeals(accountIds); 
    RecordUpdate.updateInvoices(accountIds);   
}

}

 

Can someone please let me know what they think?

 

The SOQL statements have reduced to 36 and I will add the conditonal to the other triggers as I enter them, but I want to be sure this is a suitable fix.

 

Thanks.

This was selected as the best answer
DBManagerDBManager

It seems this has solved the problem.

 

Any comments?

 

Thanks kindly.