Apex Code Development You need to sign in to do that
Don't have an account?
too many soql queries : 201
Can anyone help me why I have this error: Too many soql queries inside my code?
for(SVMXC__SVMX_Event__c svEv: listSvmxEvents )
{
String key = '' + svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c + svEv.SVMXC__StartDateTime__c.date();
SVMXC__Timesheet_Day_Entry__c tde = dayEntries.get(key);
Map<ID, ID> technicianTImesheet = new Map<Id,ID>();
technicianTImesheet.put(svEv.Id, svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c);
//if there are no timesheet daily summary create them -> Start
if(tde == null)
{
List<SVMXC__Timesheet__c> latestTimesheet = [SELECT Id,SVMXC__User__c,Name,SVMXC__Start_Date__c, Day_of_the_week__c FROM SVMXC__Timesheet__c WHERE SVMXC__User__c =: technicianTImesheet.values() ORDER BY SVMXC__Start_Date__c DESC LIMIT 1];
String technicianSalesforceId = svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c;
Date dateServiceMaxEvent = svEv.SVMX_PS_Customer_Start_Date__c;
String dayOftheWeek = svEv.Day_of_the_week__c;
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Monday' && tm.Day_of_the_week__c == 'Monday')
{
System.debug('Monday');
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Tuesday' && tm .Day_of_the_week__c == 'Monday')
{
System.debug('Tuesday ' +svEv.Id );
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 1;
System.debug('newTimesheet.SVMXC__Start_Date__c dateServiceMaxEvent - 1' + newTimesheet.SVMXC__Start_Date__c);
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Wednesday' && tm .Day_of_the_week__c == 'Monday')
{
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 2;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Thursday' && tm .Day_of_the_week__c == 'Monday')
{
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 3;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm: latestTimesheet){
if(dayOftheWeek == 'Friday' && tm.Day_of_the_week__c == 'Monday')
{
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 4;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Saturday' && tm .Day_of_the_week__c == 'Monday')
{
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 5;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
for(SVMXC__Timesheet__c tm : latestTimesheet){
if(dayOftheWeek == 'Sunday' && tm.Day_of_the_week__c == 'Monday')
{
SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c();
newTimesheet.SVMXC__User__c = technicianSalesforceId;
newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 6;
newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6;
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
if(existingTimesheet.size() == 0)
{
insert newTimesheet;
}
}
}
}
The error code is happening here:
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
You need to collect all the criteria for the SOQL queries up front, then run the queries one time and deal with the results within your loop.
Also, you should not perform an insert within the loop either. Collect all the new records in a list and insert the whole list at the end of the process.
Frankly, I'm not sure of the whole picture or structure of your timesheets and day entries is here, the relationships between the Start_Date, Customer_Start_Date, and Day_of_the_Week fields. A key to understanding how to rewrite this may be in how you're building the "dayEntries" map.
Maybe start off with something like this to build a map of timesheets by user (latest only) & user + start date: (!!! untested code !!!)
// collect all users up-front and run the latest timesheet query once! Map<ID, ID> technicianTImesheet = new Map<Id,ID>(); for(SVMXC__SVMX_Event__c svEv: listSvmxEvents ) { technicianTImesheet.put(svEv.Id, svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c); } Map<Id, SVMXC__Timesheet__c> latestTimesheetByUser = new Map<Id, SVMXC__Timesheet__c>(); Map<String, SVMXC__Timesheet__c> timesheetByUserAndStartDate = new Map<Id, SVMXC__Timesheet__c>(); for(AggregateResult ar : [ SELECT SVMXC__User__c,Name,SVMXC__Start_Date__c, Day_of_the_week__c FROM SVMXC__Timesheet__c WHERE SVMXC__User__c =: technicianTImesheet.values() ORDER BY SVMXC__Start_Date__c DESC LIMIT 20000 // ??? need to be sure we will NOT load 20k records for 1 user, may need to set a limit on how old the start dates can be! ]) { // Only add the first entry retrieved for a user -- that will be the latest one if (!latestTimesheetByUser.containsKey(ts.SVMXC__User__c)) { latestTimesheetByUser.put(ts.SVMXC__User__c, ts); } // Now build the map with user + start date String userStartDateKey = ts.SVMXC__User__c + DateTime.newInstance(rs.SVMXC__Start_Date__c, Time.newInstance(0,0,0)).format('YYYY-mm-dd'); if (!timesheetByUserAndStartDate.containsKey(userStartDateKey)) { timesheetByUserAndStartDate.put(userStartDateKey, ts); } } // collection of all new timesheets to insert into the database List<SVMXC__Timesheet__c> newTimeSheets = new List<SVMXC__Timesheet__c>();Then as your processing listSvmxEvents you can test for the latest timesheet existing for a user with this:
SVMXC__Timesheet__c latestTimesheet = latestTimesheetByUser.get(svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c); if (latestTimesheet!=null) { // you have a latest timesheet record! // do your stuff }And before inserting a new timesheet you can see if it exists already with something like this, and add it to the list of new timesheets to insert:
String userStartDateKey = ts.SVMXC__User__c + DateTime.newInstance(rs.SVMXC__Start_Date__c, Time.newInstance(0,0,0)).format('YYYY-mm-dd'); if (!timesheetByUserAndStartDate.containsKey(userStartDateKey)) { newTimesheets.add(newTimesheet); timesheetByUserAndStartDate.put(userStartDateKey, newTimesheet); }Then at the end, insert all the new timesheets at once:
Good luck!
Question, what you mean by ts o this line?
// Only add the first entry retrieved for a user -- that will be the latest one if (!latestTimesheetByUser.containsKey(ts.SVMXC__User__c)) { latestTimesheetByUser.put(ts.SVMXC__User__c, ts); }