+ Start a Discussion
Cris9931Cris9931 

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];

​​​​​​​
Bryan LeamanBryan Leaman
You have at least 8 queries inside for-loops. No wonder you're getting an error! You listed the 1 where the error is happening at the moment, but if that one is corrected the error sill just show up on one of the other ones next like a game of whack-a-mole.

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:
insert newTimesheets;

Good luck!

 
Cris9931Cris9931
THat's a nice explanation. I will try that :)
Cris9931Cris9931

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);
	}