+ Start a Discussion
abinayaabinaya 

too many soql queries

Hi all,

 

I am facing too many soql queries in 2 of my triggers. 

 

In the first trigger i am facing issue in List<Candidate__c> candidatelist = [Select Id, Placed__c, Star_Candidate__c from candidate__c where id =: candidateids limit 1];

 

In the second trigger i am facing issue in listCM = [Select Candidate__c, Requirement__c from Candidate_Mapping__c where id =:contentId];

 

Please find the triggers below. 

 

trigger candidatestartedupdate on Candidate_Mapping__c (after update)
{

if(Trigger.isUpdate)
{

string testId = Trigger.new[0].Id;

set<ID> candidateids = New set<ID>();

set<ID> requirementids = New set<ID>();

// List<Candidate_Mapping__c> cm1 = [select candidate__c, requirement__c, status1__c,Candidate_started__c from Candidate_Mapping__c where id =:testId limit 1];

for(Candidate_Mapping__c cm : Trigger.new)
{

if (cm.Candidate_started__c == 'yes')
{
requirementids.add(cm.requirement__c);

candidateids.add(cm.candidate__c);

}

}

List<Requirement__c> reqlist = [Select Id, won__c from requirement__c where id in : requirementids limit 1];

system.debug('reqlist size:' + reqlist.size());

List<Candidate__c> candidatelist = [Select Id, Placed__c, Star_Candidate__c from candidate__c where id =: candidateids limit 1];

system.debug('candidatelist size:' + candidatelist.size());


for(integer i = 0 ; i< reqlist.size(); i++)
{
reqlist[i].won__c = true;
}

for(integer j = 0 ; j< candidatelist.size(); j++)
{
candidatelist[j].Star_Candidate__c = true;
candidatelist[j].Placed__c = true;
}

update reqlist;

update candidatelist;

}


}

 

 

 

trigger updateresumessubmitted on Candidate_Mapping__c (after insert, after update)
{

integer ressubmitted;
integer ressubmitted1;
string UserId = UserInfo.getUserId();
System.Debug('UserId =' + UserId);

Candidate_Mapping__c cm = Trigger.new[0];

List<Aggregateresult> ar= new List<Aggregateresult>();
//Map<Integer,Aggregateresult> getRequirement = new Map<Integer,Aggregateresult>();
set<Id> RequirementIds = new Set<Id>();
List<Requirement__c> reqlist = new List<Requirement__c>();
List<Candidate_Mapping__c> listCM = new List<Candidate_Mapping__c>();

if(Trigger.isUpdate || Trigger.isInsert)
{

string contentId = Trigger.new[0].Id;

listCM = [Select Candidate__c, Requirement__c from Candidate_Mapping__c where id =:contentId];

for(Candidate_Mapping__c cm1 : listCM)
{
system.debug('cm1:' + cm1);

RequirementIds.add(cm1.Requirement__c);
}

reqlist = [Select Id, Submitted_Resumes__c, status__c,cumulative_resumes_submitted__c from requirement__c where id =:RequirementIds limit 1];
system.debug('reqlist :' +reqlist);

ar = [SELECT count(Id) cnt FROM Candidate_Mapping__c where (Status1__c != 'rejected' and Status1__c != 'LR Rejected' ) and requirement__c in:RequirementIds];
system.debug('ar:' +ar);

for(AggregateResult ar1 : ar)
{
ressubmitted = Integer.valueOf(ar1.get('cnt'));
system.debug('ressubmitted :' +ressubmitted);

for(requirement__c req : reqlist)
{
req.cumulative_resumes_submitted__c = ressubmitted;
system.debug('cumulative_resumes_submitted__c :' + req.cumulative_resumes_submitted__c);
system.debug('status__c12 :'+ req.status__c);
if(req.status__c == 'Open')
{
system.debug('status__c123 :'+ req.status__c);
for(integer i = 0 ; i< reqlist.size(); i++)
{
reqlist[i].Submitted_Resumes__c = ressubmitted;
}
}

if(req.status__c == 'Re-open' )
{
system.debug('status__c1 :'+ req.status__c);
system.debug('cm.Status1__c11 :'+ cm.Status1__c);
system.debug('submitted_resumes :' + req.Submitted_Resumes__c);
system.debug('cm.LRdate__c :' + cm.LRdate__c);
if(cm.status1__c == 'applied' && cm.createdbyid == UserId || cm.status1__c == 'LR Approved' && cm.createdbyid == UserId || cm.status1__c == 'Approved' && cm.createdbyid == UserId )
{
system.debug('status__c00:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c + 1;
system.debug('submitted_resumes0 :' + req.Submitted_Resumes__c);
}
else if (cm.status1__c == 'LR Rejected' && cm.createdbyid == UserId && req.Submitted_Resumes__c == 0 || cm.status1__c == 'Rejected' && cm.createdbyid == UserId && req.Submitted_Resumes__c == 0)
{
system.debug('status__c 1123:'+ req.status__c);
req.Submitted_Resumes__c = 0;
system.debug('submitted_resumes3 :' + req.Submitted_Resumes__c);
}

else if (cm.status1__c == 'rejected' || cm.status1__c == 'LR Rejected')
{
// system.debug('cm.LRdate__c :' + cm.LRdate__c);
system.debug('status__c 1122:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c - 1;
system.debug('submitted_resumes2 :' + req.Submitted_Resumes__c);
}
else
{
system.debug('status__c 1121:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c;
system.debug('submitted_resumes1 :' + req.Submitted_Resumes__c);
}
}

system.debug('cm.Status1__c :'+ cm.Status1__c);
system.debug('status__c :'+ req.status__c);
system.debug('submitted_resumes3 :' + req.Submitted_Resumes__c);

upsert reqlist;
system.debug('reqlist1:' +reqlist);

//}

}
}
}

}

Dhaval PanchalDhaval Panchal
I think it is falling in recursion. Check your trigger individually, means first disable first trigger and test and then enable first and disable second trigger and then test.
abinayaabinaya
based on ur reply, i have activated updateresumessubmitted trigger and deactivated candidatestartedupdate trigger. Then i run the test class, still i am facing too many soql errors in listCM = [Select Candidate__c, Requirement__c from Candidate_Mapping__c where id =:contentId]; line of updateresumessubmitted trigger
Dhaval PanchalDhaval Panchal
Please check your calling stack. Because total number of queries call counted cumulatively. So it is possible that at the point of calling above query, it has already passed limitation. So see all queries from starting point to this error, It is also possible that some other trigger is fired before this query.
Avidev9Avidev9
Well the code looks good !
Seems like you are facing "Too Many SOQL rows" . Correct me if I am wrong.

If so I fear that you have to make the queries more specific and add some filters so that it pulls less than 50000 records. And if you have to somehow report on 50k + records you have to opt for batch apex.
abinayaabinaya
No it is System.LimitException: Too many SOQL queries: 101 error only
gbu.varungbu.varun

Hi,

 

Trigger is calling itself after again and again. So you are getting exception. Run your trigger only for required fields when change happen for these fields.

Avidev9Avidev9
Well seems like a case of recursion but I still doubt over it! Because you have only two soql per trigger and to do 101 queries the trigger has to iterate atleast 25 times.

But the catch is salesforce doesnt allow that kind of stack depth, I guess probably only 20.


So my guess would be you have some other trigger on the related object that is not optimized and is having query inside for loop