+ Start a Discussion
Esther Thacker 6Esther Thacker 6 

Query Governor Limits

Hello,

I am creating code that allows me to store the information of my products, such as name, description, price and their relationship in one place on a Visualforce Page. To gather it I am using queries to get all the information I need regarding the products and their relationships. So far the code that I have exceeds the SOQL governor limits. I took out some of the relationships but still having the products and their first relationship causes the limit to be 29 which is too big if I want to add the rest of the relationships and information like I need to do. Since I am new to Apex coding, would anyone be able to look at my code and determine why the queries are running so much? I have looked at the forums and the suggestions that Salesforce has on governor limits and tried my best to follow the advice, but I am not sure I am implementing it properly since I am fairly new to using Apex and programming.

Here is my code:
 
public class CCQueries9 {
    
    List<DisplayProduct> productsParent; 
     
    //Queries the Product Information
    public List<DisplayProduct> getProducts() {
        if(productsParent == null) {
            productsParent = new List<DisplayProduct>();
            List<Product2> item = [SELECT Id, Name, HTML_Description__c FROM Product2 WHERE Account_Relatable__c = true ORDER BY Order__c];
            for(Product2 prod : item ) {
                productsParent.add(new DisplayProduct(prod));            
            }        
        }
        return productsParent; 
    }//End of Query Code
    
    
    public class DisplayProduct {
        
        List<Id> productParentId = new List<Id>();
        List<Id> ChildrenProductIds = new List<Id>();
        List<DisplaySuite> productsChildren {get;set;}
        
        private Product2 product;
        public DisplayProduct(Product2 item) {
            
           this.product = item;
           
            productParentId.add(this.product.Id); 
           
        }
        
        public String name {
            get {return product.Name;}
        }
         
        public String description {
            get {return product.HTML_Description__c;}
        }
        //Code to determine Query Limits
          public String GetCharlie {
            get {return String.valueOf(Limits.getQueries());}
        }

        //Query for the Childrens of Products 
        public List<DisplaySuite> getProductsChildren() {
        	
            if(productsChildren == null) {
            	
            	
         
            List<Product_Relationship__c> relationships = new List<Product_Relationship__c>(); 

         	 
            relationships = [SELECT Master_Product__c, Child_Product__c, CalculatorOrder__c, Child_Product__r.Name 
            				 FROM Product_Relationship__c 
            				 WHERE Master_Product__c IN : productParentId   
            				 ORDER BY CalculatorOrder__c,Child_Product__r.Name]; 
            
            
            for(Product_Relationship__c c:relationships){
               ChildrenProductIds.add(c.Child_Product__c);
            }
            
            if(ChildrenProductIds != null) {
			// query the product2 table for those products in the relationship, store in the final list        
                productsChildren = new List<DisplaySuite>();
                List<Product2> item = [SELECT Id, Name, HTML_Description__c FROM Product2 WHERE Id IN :ChildrenProductIds ORDER BY Order__c]; 
                for(Product2 children : item ) { 
                    productsChildren.add(new DisplaySuite(children));            
                }
            }        
            }
            return productsChildren;
        }
      
        
    }
    
    public class DisplaySuite {
        
        List<Id> productParentId = new List<Id>();
        List<Id> ChildrenProductIds = new List<Id>();
        List<DisplaySuite> productsChildren {get;set;}
        
        private Product2 product;
        
        public DisplaySuite(Product2 item) {
            this.product = item;
            productParentId.add(this.product.Id); 
        }
        public String name {
            get {return product.Name;}
        }
        public String description {
            get {return product.HTML_Description__c;}
        }
        //Code to determine Query Limits
          public String GetCharlie {
            get {return String.valueOf(Limits.getQueries());}
        }
            
     }
      
        
    }

And here is the page that I am calling the information on.
 
<apex:page showHeader="false" sidebar="false" Controller="CCQueries9" standardStylesheets="false">
       
    <apex:repeat value="{!products}" var="pitem"> <!-- Repeat for Product Information -->
      
        <apex:outputPanel >
                
            <apex:outputText escape="false" value="{!pitem.Name}"/><br/>
          		{! pitem.getCharlie } <br/>
            <div style="margin-left: 30px;">
           
            <apex:repeat value="{!pitem.productsChildren}" var="sitem"> <!-- Repeat for Suite Information -->
            
                <apex:outputPanel >                  
           
                    <apex:outputText escape="false" value="{!sitem.Name}"/><br/>
                    	{! sitem.getCharlie } <br/>
          	        	 
                </apex:outputPanel>
          
            </apex:repeat>   
            
            </div>
        
        </apex:outputPanel>
    
    </apex:repeat>  
    
</apex:page>

Thanks for any advice in advance!  
David Zhu 8David Zhu 8
Can you try if you get governor limit error in developer console?
SELECT Id, Name, HTML_Description__c FROM Product2 WHEREAccount_Relatable__c = true ORDER BY Order__c

If yes, it means you have large number of produt2 which exceeds the limits. Single query result limit is 2000 records. 
I would suggest you optimize you query, like adding: limit 100.

SELECT Id, Name, HTML_Description__c FROM Product2 WHEREAccount_Relatable__c = true limit 100 ORDER BY Order__c
Esther Thacker 6Esther Thacker 6
The limitation is not the number of records, this is the error that I am getting:

Number of SOQL queries: 101 out of 100

What I'm doing is looping through the products and finding their children and then looping through the children and finding their children, and displaying this information in repeats and subrepeats to the page. How do I fix the amount of queries? 
GauravGargGauravGarg
Hi Esther,

Could you please provide the debug log and the line number where you are getting 101 SOQL error.

Thanks
Gaurav
Skype: gaurav62990
Esther Thacker 6Esther Thacker 6
I didn't get the error in a debug log. It was e-mailed to me from Salesforce.

This is the e-mail that I received:

Operation: /apex/DataRepeat
 
By user/organization: 005E0000007aJhC/00D4C0000000fJ7
 
Caused the following Apex resource warnings:
 
Number of SOQL queries: 101 out of 100
 
(these emails can be disabled from the user detail page for this user)

I don't know how to access the debug log.
GauravGargGauravGarg

Hi Esther,

apex/DataRepeat is the visual force which is workng at the back-end we need to debug it to find the root cause.

Thanks,

Gaurav

Esther Thacker 6Esther Thacker 6
My apologies for being so inexperience, but as I stated before, I am new to programming and Apex so I am not too familiar with debugging as I have never needed to use it before in my programming experience. I am using Eclipse with the Force.com IDE to program my code, but from my research I cannot debug unless I have added the ability to do so on my salesforce account. So if you know another way in which I can debug I would greatly appreciate the help. In the meantime, I do not have a debug log with which I can provide. 
GauravGargGauravGarg
Hi Esther,

Please contact me on skype gaurav62990.

We can debug this issue together. 

Thanks,
Gaurav