You need to sign in to do that
Don't have an account?
DBManager
"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.
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:
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
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......
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?
The logs will show you which queries are getting executed that many times. That's your best bet for now.
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. =)
Thanks for your reply.
To begin with, here are the other triggers involved:
Here:
And here:
And here is the test class I am running:
I will do some further testing today and see how it goes.
Thanks again.
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?
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:
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.
It seems this has solved the problem.
Any comments?
Thanks kindly.