+ Start a Discussion
symantecAPsymantecAP 

Too Many Query Rows 50001

Hi Developers 

 

I am trying to build a page to display Products and related Price Books in a VF Page. Here is the code and the error 

 

Error : System.LimitException: Too many query rows: 50001

 

 

 

 

public class ProductPriceBook {

    public class productInfo {
    
        public Product2 prod{get; set;}
        public List<PriceBook2> priceBooks {get; Set;}
        public productInfo() {
        priceBooks = new List<PriceBook2>();
        
        }
        
    }
    
    public List<ProductInfo> products {get; set;}
    
    public ProductPriceBook() {
        products = new List<productInfo >();
       
        List<Product2> prods = [Select Name, Description, Family, id from Product2];
       
        List<PricebookEntry> pEntries = [Select id, product2Id, pricebook2id from PricebookEntry ];
        Map<Id, Pricebook2> priceBooks = new Map<id, Pricebook2>([Select id, name from PriceBook2]);
        
        
        for (Product2 prod : prods ) {
            
            ProductInfo pInfo = new ProductInfo();
            pInfo.prod = prod;
            for (PricebookEntry pe : pEntries)  {
                if (pe.product2Id == prod.id) {
                    pInfo.pricebooks.add(priceBooks.get(pe.pricebook2id));
                }
            }
            products.add(pInfo );
            
        }
        
        
        
        
    }
}

 

 

 

 

<apex:page controller="ProductPriceBook">
    <apex:pageBlock >
         <apex:pageblockTable value="{!products}" var="product">
             <apex:column value="{!product.prod.name}" />
              <apex:column >           
                  <apex:repeat value="{!product.pricebooks}" var="priceBook">
                      {!priceBook.name} <br/>
                  </apex:repeat>
                </apex:column>  
                <apex:column value="{!product.prod.Description}" />
                 <apex:column value="{!product.prod.Family}" />           
           </apex:pageblockTable>
    </apex:pageBlock>
</apex:page>

 

 

Cory CowgillCory Cowgill

The exception is being thrown because the maximum number of rows you can return in SOQL calls in Apex is 50000.

 

The three SOQL calls you have below are not filtering (I.E. they do not have a WHERE clause). In addition, they do not have a LIMIT clause which could prevent this exception.

 

In your VF Page, you could try to limit this to only Products in a specific Pricebook, or only products of a certain type, etc.

 

It looks like your org has an extensive catolog, so think about the use case. Even if you could display 50,000 products on a page, who is going to be able to read that?

 

If you add Where and Limit clauses to your SOQL, you'll be able to resolve the error.

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql.htm

symantecAPsymantecAP

Hi Cory

 

Thank you so much for the suggestion. I added the where clause and here is another error message i get 

 

Error : Collection size 25,834 exceeds maximum size of 1,000.

 

 List<PricebookEntry> pEntries = [Select id, product2Id, pricebook2id from PricebookEntry where Name='EMEA 2009/Q4 VAD (USD/EURO)'];
        Map<Id, Pricebook2> priceBooks = new Map<id, Pricebook2>([Select id, name from PriceBook2]);
        

 

 

 

Cory CowgillCory Cowgill

Collection objects in Apex can only have 1,000 records. This applies to List, Set, and Maps.

 

So now while you have reduced the amount of records returned, you're still returning too many records to display in one page. You are hitting a limit on the collections objects.

 

The way this is handled usually is to put a LIMIT 1000 on the end of your SOQL which is storing results in a List.

 

Then, if the results is 1000 display a message to the user specifying that there is possibly more than 1000 results, please refine search criteria.

 

 

Shashikant SharmaShashikant Sharma

First I would like to tell you that collections Set, Map and List have no limits, you can add as many records in them in apex class.But your problem is that data retrieved from your three SOQL is returning you more than 50000  records and in a single context you can only retrieve only 50000. You can use Limit.getQueryLimit() and other limit methods to know the limits. For now you can add any condition or Limit in your SOQL. If you want all the records than I would suggest you use action poller control of apex.

Cory CowgillCory Cowgill

In Apex Context you can have larger lists, up to heap size, but if your binding your collection in a VF page from a Apex Class (Controller, Extension, ETC) you get the following exception if the collection is 1000.

 

Collection size 1,100 exceeds maximum size of 1,000.

 

I should have clarified that.

 

symantecAPsymantecAP

Thanks Cory and Shashikanth

 

I have tried putting the limits and where clause. Now I get Product list but it doesnt show the price book entry related to those products.

public class ProductPriceBook {

    public class productInfo {
    
        public Product2 prod{get; set;}
        public List<PriceBook2> priceBooks {get; Set;}
        public productInfo() {
        priceBooks = new List<PriceBook2>();
        
        }
        
    }
    
    public List<ProductInfo> products {get; set;}
    
    public ProductPriceBook() {
        products = new List<productInfo >();
       
        List<Product2> prods = [Select Name, Description, Family, id from Product2 limit 1000];
       
        List<PricebookEntry> pEntries = [Select id, product2Id, pricebook2id from PricebookEntry where Name='EMEA 2009/Q4 VAD (USD/EURO)' ];
        Map<Id, Pricebook2> priceBooks = new Map<id, Pricebook2>([Select id, name from PriceBook2 ]);
        
        
        for (Product2 prod : prods ) {
            
            ProductInfo pInfo = new ProductInfo();
            pInfo.prod = prod;
            for (PricebookEntry pe : pEntries)  {
                if (pe.product2Id == prod.id) {
                    pInfo.pricebooks.add(priceBooks.get(pe.pricebook2id));
                }
            }
            products.add(pInfo );
            
        }
        
        
        
        
    }
}

 Thanks

Again

 

symantecAPsymantecAP

 

Hi I am getting another error now. 

 


System.LimitException: Too many script statements: 200001


public class ProductPriceBook1 {



    public class productInfo {
    
        public Product2 prod{get; set;}
        public List<PriceBook2> priceBooks {get; Set;}
        public productInfo() {
        priceBooks = new List<PriceBook2>();
        
        }
        
    }
    
    public List<ProductInfo> products {get; set;}
    
    public ProductPriceBook1() {
        products = new List<productInfo >();
       
        List<Product2> prods = [Select Name, Description, Family, id from Product2 limit 1000];
        
        List<Id>prodIds = new List<Id>();
        for (Product2 p : prods)
                prodIds.add(p.id);
       
        List<PricebookEntry> pEntries = [Select id, product2Id, pricebook2id from PricebookEntry where product2Id in :prodIds ];
          List<id> pbIds = new List<Id>();
        for (PriceBookEntry pe : pEntries)
                   pbIds.add(pe.pricebook2Id);
        Map<Id, Pricebook2> priceBooks = new Map<id, Pricebook2>([Select id, name from PriceBook2 where id in :pbIds ]);
        
        
        for (Product2 prod : prods ) {
            
            ProductInfo pInfo = new ProductInfo();
            pInfo.prod = prod;
            for (PricebookEntry pe : pEntries)  {
                if (pe.product2Id == prod.id) {
                    pInfo.pricebooks.add(priceBooks.get(pe.pricebook2id));
                }
            }
            products.add(pInfo );
            
        }
        
        
        
        
    }
}

 

 

 

tggagnetggagne

How did you get around the script statement limit?

symantecAPsymantecAP

Used Limit and WHERE CLAUSES

 

 

 

raj123raj123

Hi all , 

 

I have limit the now of rows returned to 50000

 

and i am putting the queried rows in a list;

 

I am trying to update this list after i am makeing changes, the doubt i am having is does this update statement thow any exception.

 

for testing purpose i am not insering 50000 records and updating.

 

I am see that there is limit in no of rows displayed on theh pageblock table from the list, but i am just using this list inside the apex and its no where reference in VF,

 

Does the update statement run smoothly?