Apex Code Development You need to sign in to do that
Don't have an account?
symantecAP 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>
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
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]);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.
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.
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.
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
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 ); } } }How did you get around the script statement limit?
Used Limit and WHERE CLAUSES
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?