+ Start a Discussion
rivereridanusrivereridanus 

Need to optimize this apex class/query

Hi everyone,  I have a VF report that I have made the pulls together a single conceptual record based on information in mutiple different SObjects. I wrote this controller class to feed the report, and it works and does exactly what I need. However, report takes between 20 to 30 seconds to run because the query is seriously not optimized (written that way to avoid hitting governor limits)- sometimes we even get timeout errors and have to wait until another time to run the report.

What I'm trying to do is come up with a way to recode this so that it is more efficient and we don't timeout without hitting governor limits.  I'm not looking for someone to recode it for me, but if anyone has any thoughts on how to do this, I'd really appreciate hearing it!

Thanks in advance!

public class DiaperReportController 
{ 
   public List<DiaperReportDummy> results {get;set;}

   public DiaperReportController()
   {
       results = getResults();
   }

   public DiaperReportDummy[] getResults() 
   { 
      results = new List<DiaperReportDummy>();
      
      //query for all the data 
      Date d = System.today() - 59; //will get all ongoing diaper people who havent gotten a donation in 60 days
      Contact[] allContacts = [select Name, Phone, OtherPhone, Contact_Info_Notes__c, Description, Diapers_Needed__C, Diaper_Size__c, Diaper_Size_Date__c, AccountId, Id, npe01__Primary_Address_Type__c, MailingStreet, MailingCity, MailingState, MailingPostalCode, npe01__Secondary_Address_Type__c, OtherStreet, OtherCity, OtherState, OtherPostalCode from Contact where (npo02__LastCloseDate__c < :d or npo02__LastCloseDate__c = null) and (Diapers_Needed__c = True or Num_Diapers_Dependents__c > 0) order by MailingPostalCode];
      Dependent__c[] allDependents =  [select Contact__c, Name, Diapers__c, Diaper_Size__c, Diaper_Size_Date__c, Gender__c, Age__c from Dependent__c where (Contact__r.Diapers_Needed__c = True or Contact__r.Num_Diapers_Dependents__c > 0)]; 
      Opportunity[] allOpptys = [select Account.Id, CloseDate, Amount, Description, Name, RecordTypeId, Campaign.Id from Opportunity where Is_Last_Donation__c = True and Contact_Is_Ongoing_Diapers__c = True and RecordTypeId = '012i0000000RGFh'];
      OpportunityLineItem[] allOLIs = [select Opportunity.Id, Description, Product2.Name, Quantity, TotalPrice from OpportunityLineItem where Opportunity.Is_Last_Donation__c = True and Opportunity.Contact_Is_Ongoing_Diapers__c = True and Opportunity.RecordTypeId = '012i0000000RGFh'];
      
      //find all the related data and put into dummy class 
      for(Contact c: allContacts) 
      {
      Dependent__c[] lstDeps = new List<Dependent__c>();
      OpportunityLineItem[] lstOLIs = new List<OpportunityLineItem>();
      Opportunity lastopty;
      
         //get the related Dependents 
         for(Dependent__c dep: allDependents) 
         { 
           if(dep.Contact__c == c.id) 
            { 
               
               lstDeps.add(dep);    
            } 
         }
         //get the most recent sent donation
         for(Opportunity opp: allOpptys)
         {
             if (opp.AccountId == c.AccountId)
             {
                 lastopty = opp;
                 break;
             }
             else
             {
                 lastopty = null;
             }
         }
         //get the products of the most recent sent donation- if there is one
         if (!(lastopty == null))
         {
             for(OpportunityLineItem oli: allOLIs)
             {
                 if (oli.OpportunityId == lastopty.Id)
                 {
                     lstOLIs.add(oli);
                 }
             }
         }
         
         //create the Dummy class and add to the result list 
         results.add(new DiaperReportDummy(c, lstDeps, lastopty, lstOLIs)); 
         
      } 
      return results; 
   }
   
   //Inner class to hold relevant details for each Contact
   class DiaperReportDummy 
   { 
       public Contact contact { get; set; }  
       public Dependent__c[] dependents { get; set;} 
       public Opportunity opportunity {get;set;}
       public OpportunityLineItem[] opptylineitems {get;set;}
       
       public DiaperReportDummy(Contact con1, Dependent__c[] dep1, Opportunity opp1, OpportunityLineItem[] oli1) 
       { 
          this.contact = con1; 
          this.dependents = dep1;
          this.opportunity = opp1; 
          this.opptylineitems = oli1;
       } 
   }

}

 
Best Answer chosen by rivereridanus
Chamil MadusankaChamil Madusanka
I noticed the following issue in your code. You have to change your code according to that.
  • You can quesry Opportunity and OpportunityLineItem Objects together because they are parent-child relationship. use the nested query. According to that change your code.

All Answers

Chamil MadusankaChamil Madusanka
I noticed the following issue in your code. You have to change your code according to that.
  • You can quesry Opportunity and OpportunityLineItem Objects together because they are parent-child relationship. use the nested query. According to that change your code.
This was selected as the best answer
rivereridanusrivereridanus
Thanks Chamil!  You're completely right. I hadn't thought about that. This will definitely shave some time off. I can even do a custom relationship between Contact and Dependent to remove that extra loop as well.  Found this great resource for examples of nested queries: http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

Thanks again for your help- much appreciated!