+ Start a Discussion
Cool_DevloperCool_Devloper 

Question regarding limit of 10000 query rows in APEX class

Hi Friends,

 

Going through the facts mentioned in the apex documentation, it seems that the limit of 10000 query rows when the entry point is an APEX class is applicable to the total cumulative count of all the queries which are a part of that class.

 

For e.g, if i have 5 different queries as part of the same APEX controller class and each one of them retrieves 2001 records, then i would get an exception stating: "Too many query rows - 10005" ??

 

Just want to confirm if my understanding here is correct. Because till now my understanding was that it is applicable to each individual query!!

 

Thanks a ton!!

Cool_D

Best Answer chosen by Admin (Salesforce Developers) 
ThomasTTThomasTT

No. and that's a biiiiiiiiiiiiiiiiiiig no. no no.

 

First of all, it's not "10,000 records per query". It's "10,000 records per entry point (execution)".

 

> For e.g, if i have 5 different queries as part of the same APEX controller class and each one of them retrieves 2001 records, then i would get an exception stating: "Too many query rows - 10005" ??

 

I guess it's going to be "Too many query rows - 10001" and the last query will be aborted in the middle.

 

and, what I meant with biiiiiiiiiiiiiiiiiiig no, is that

 

 

1 entry point includs all workflow field update, trigger update,

workflow field update/trigger update triggered by workflow/trigger,

trigger triggered trigger triggered trigger..........

 

and the totall of query rows must be equal to or less than 10000. It's called a trigger chain. The chain can be loop. This is the biggest fear for us, because we can't estimate the entire number of records in 1 entry point (execution) could query, but 

 

WE MUST

 

If this is about trigger(s), the number is "1,000 x batch size records", which could be worse than from VF page / web serivece. 

 

 

So, imagine you have a trigger which update/insert records on update of a object. Imagine that you have a client or business analyst who can create a workflow with field update (on the object). You don't even know who they are, what they are going to do, but their workflow can trigger your trigger, and your trigger can start their workflow and the total query row must be less than 10,000 records.

 

Did I scare you? but this is the problem which  we really face every day. Really. But, when I was in Java development, nobody warned such a performant measurement. It's really good to have a limitation or warning before deplying to production (I'm talking as a manager/development lead rather than a developer).

 

 

Well, I would say, what else do we have to care about? We're enjoying a party in other's house whose name is "SFDC". What we need to care is, not to bother the host, and to enjoy the party!

 

It's a reasonable payment we have to make to enjoy the whole SaaS/PaaS.

 

ThomasTT

All Answers

ThomasTTThomasTT

No. and that's a biiiiiiiiiiiiiiiiiiig no. no no.

 

First of all, it's not "10,000 records per query". It's "10,000 records per entry point (execution)".

 

> For e.g, if i have 5 different queries as part of the same APEX controller class and each one of them retrieves 2001 records, then i would get an exception stating: "Too many query rows - 10005" ??

 

I guess it's going to be "Too many query rows - 10001" and the last query will be aborted in the middle.

 

and, what I meant with biiiiiiiiiiiiiiiiiiig no, is that

 

 

1 entry point includs all workflow field update, trigger update,

workflow field update/trigger update triggered by workflow/trigger,

trigger triggered trigger triggered trigger..........

 

and the totall of query rows must be equal to or less than 10000. It's called a trigger chain. The chain can be loop. This is the biggest fear for us, because we can't estimate the entire number of records in 1 entry point (execution) could query, but 

 

WE MUST

 

If this is about trigger(s), the number is "1,000 x batch size records", which could be worse than from VF page / web serivece. 

 

 

So, imagine you have a trigger which update/insert records on update of a object. Imagine that you have a client or business analyst who can create a workflow with field update (on the object). You don't even know who they are, what they are going to do, but their workflow can trigger your trigger, and your trigger can start their workflow and the total query row must be less than 10,000 records.

 

Did I scare you? but this is the problem which  we really face every day. Really. But, when I was in Java development, nobody warned such a performant measurement. It's really good to have a limitation or warning before deplying to production (I'm talking as a manager/development lead rather than a developer).

 

 

Well, I would say, what else do we have to care about? We're enjoying a party in other's house whose name is "SFDC". What we need to care is, not to bother the host, and to enjoy the party!

 

It's a reasonable payment we have to make to enjoy the whole SaaS/PaaS.

 

ThomasTT

This was selected as the best answer
Cool_DevloperCool_Devloper

Thanks a ton Thomas for that elaborate explanation :) One more query i have ....

 

Well, in case if in my scenario, the data is huge and i need to query more then 10000 records, then is there a way to achieve this??

 

Thanks Again!!

Cool_D

ThomasTTThomasTT

First of all - before forgetting to tell you, count() is counted as query records, so you can't even count more than 10,000 records... this is painful...

 

Anyway, yes, there are some way to avoid this painful governor limit in some situations. In most situation with VF page, reading 10,000 records is quite unusual (except count())... people can't see more than 100 records in 1 page anyway. However, maintenance application, batch application may need to do that. I'm going to talk about such situations.

 

The concept is easy. If SFDC allows us to read 10,000 "per execution", then why don't you execute multiple executions till you read everything you need? 

 

1. @future

SFDC proides a way to make asynchronous calls from Apex, it's called future call. By adding @future annotation in your method (read manual for the detal), the call will be executed when SFDC has enough resource, asynchronouslly. It's asynchronous so it will be executed in a fresh new governor limit resource. However, since it's asynchronous, you can't get the result from the call itself (no return value). It's for a batch type of execution.

 

2. Apex Batch

Apex Batch is available since this summer. This is to execute massive data in batch mode. You provide a query, SFDC queries a batch of records and provides back to your batch method, and you execute your logic with the given records. SFDC will repeat this process untill all records queried by the query are processed. The batch job information is stored in database, and your apex code and yourself can watch the status from apex and GUI. This is also a batch type execution. You can't expect the result from your apex.

 

3. Asynchronous chain

This is what I call this mechnism. VF page can make asynchronous calls from javascript / markups. If you make a request for 10,000 records each, and control  requests in javascript, you can make multiple calls but can make it just like 1 call to users. You don't need to keep the status in the database as long as user keep the browser open.

 

 

This is my private org exposing as Site.

 

http://ss-developer-edition.na6.force.com/Prototypes  Asynchronous Call Chain

 

You may find it interested (not the org has only 5001 records so "Count 1" button can be ok, but "Count 2" is what you need to see).

 

It's just counting all MyVote__c records per Value__c. I can count any number of records, because it count 1,000 records per request, and continue it till it reachs to the last record. The Name is unique, so if 1 request keeps the name of the last record it read, the next request can read from the next name. The controller always keep updating the status, so VF page can check the status, and keep calling the controller until the status gets "completed".

 

This have to be carefully implemented, because error handling, asynchronous control, preventing from user's interuption, many things need to be cared.

 

 

<apex:page id="page" controller="VoteSummaryController"> <apex:form id="form"> <script> function disableButtons(){ disableButton(document.getElementById("page:form:pageBlock:pbb_vote:btn1")); disableButton(document.getElementById("page:form:pageBlock:pbb_vote:btn2")); } function enableButtons(){ enableButton(document.getElementById("page:form:pageBlock:pbb_vote:btn1")); enableButton(document.getElementById("page:form:pageBlock:pbb_vote:btn2")); } function disableButton(e){ e.setAttribute("disabled", "true"); e.className="btnDisabled"; } function enableButton(e){ e.removeAttribute("disabled"); e.className="btn"; } </script> <apex:actionFunction id="af_count_asynch" name="countVote" action="{!countVote}" oncomplete="if({!isRunning}){countVote();}else{enableButtons();}" reRender="pbs_vote, message"/> <apex:pageBlock id="pageBlock" title="Vote Summary"> <apex:pageBlockButtons id="pbb_vote" location="top"> <apex:commandButton id="btn1" value="Count 1" action="{!countNormal}" reRender="pbs_vote, message"/> <apex:commandButton id="btn2" value="Count 2" action="{!countAsynch}" onclick="disableButtons();" oncomplete="countVote();" reRender="pbs_vote, message"/> </apex:pageBlockButtons> Count 1: Count all records at once. If there are more than 10000 records, you'll get "System.Exception: Too many query rows: 10001" (but this org can't have that much data due to strage limit).<br/> Count 2: Count 1000 records at once, and keep calling untill end.<br/> <apex:pageMessages id="message"/> <apex:pageBlockSection id="pbs_vote" columns="2"> <apex:pageBlockSectionItem > <apex:outputlabel >Asynch Calls</apex:outputlabel> <apex:outputText value="{!loopCount}"/> </apex:pageBlockSectionItem> <apex:pageBlockSectionItem > <apex:outputlabel >Read Count</apex:outputlabel> <apex:outputText value="{!readCount}"/> </apex:pageBlockSectionItem> <apex:pageBlockTable id="pbt_vote" value="{!votes}" var="item"> <apex:column > <apex:facet name="header">Name</apex:facet> <apex:outputText value="{!item.name}"/> </apex:column> <apex:column > <apex:facet name="header">Count</apex:facet> <apex:outputText value="{!item.count}"/> </apex:column> </apex:pageBlockTable> </apex:pageBlockSection> </apex:pageBlock> </apex:form> </apex:page>

 

/** * VoteSummaryController * Description: * This is a custom controller for Vote Summary page (a sample of Asynchronous Call Chain). * This provides normalCount() (1 synch call) and countAsynch() / countVote() (multi asynch calls). * For multi-asynch calls, countAsynch() initializes the status, and

* countVote() counts 1000 recourds per call. */ public class VoteSummaryController { /***** Inner Classes *****/ /** * Inner Class - Vote Count * Description: * This is a class to have a pair of name and count. */ public class VoteCount { public string name {get; set;} public integer count {get; set;} public VoteCount(string name, integer count){this.name = name; this.count = count;} } /***** Private Variables *****/ private Map<String, integer> intMap = new Map<String, integer>(); // To keep a pair of name and count temporary private string lastName = null; // The MyVote__c.Name of the last MyVote__c record read in the previous call /***** Public Properties *****/ public List<VoteCount> votes { get; set; } // a list of VoteCount (sorted by name) public integer loopCount {get; set;} // # of loop count public integer readCount {get; set;} // # of records read public boolean isRunning {get; set;} // a flag which is true during an asycn call chain /***** Constructor *****/ public VoteSummaryController (){ votes = new List<VoteCount>(); loopCount = 0; readCount = 0; } /***** Action Methods *****/ ////// Normal Count ///// /** * Action Method - countNormal * Description * This queries all MyVote__c records and count per name. * This will hit the governor limit (Max query record 10000) */ public PageReference countNormal() { votes.clear(); intMap.clear(); loopCount = 0; readCount = 0; for(MyVote__c v : [ select Value__c from MyVote__c ]){ if(intMap.containsKey(v.Value__c)){ intMap.put(v.Value__c, intMap.get(v.Value__c) + 1); }else{ intMap.put(v.Value__c, 1); } readCount ++; } loopCount++; Set<string> keys = intMap.keySet(); for(string s : keys) votes.add(new VoteCount(s, intMap.get(s))); return null; } ////// Asynch Call Chain Count ///// /** * Action Method - countAsynch * Description * This initializes statuses for asynch call chain. */ public PageReference countAsynch() { votes.clear(); intMap.clear(); lastName = '0'; loopCount = 0; readCount = 0; isRunning = true; return null; } /** * Action Method - countVote * Description * This count 1000 MyVote__c records. If any error occurs, isRunning is set false. * If this loop count nothing, isRunning set false, too. */ public PageReference countVote() { integer intLimit = Limits.getLimitQueryRows() - Limits.getQueryRows(); integer count = 0; try{ for(MyVote__c v : [ select Name, Value__c from MyVote__c where Name > :lastName order by Name limit 1000 ]){ count++; if(intMap.containsKey(v.Value__c)){ intMap.put(v.Value__c, intMap.get(v.Value__c) + 1); }else{ intMap.put(v.Value__c, 1); } lastName = v.Name; } if(count == 0){ isRunning = false; return null; } loopCount ++; readCount += count; Set<string> keys = intMap.keySet(); votes.clear(); for(string s : keys) { votes.add(new VoteCount(s, intMap.get(s))); } for(integer i=0;i<votes.size();i++){ integer index = i; for(integer j=i;j<votes.size();j++){ if(votes[index].count < votes[j].count) index = j; } VoteCount temp = votes[i]; votes[i] = votes[index]; votes[index] = temp; } }catch(Exception e){ ApexPages.addMessages(e); isRunning = false; return null; } return null; } }

 

 

 ThomasTT

 

maxoutmaxout

Got this at line 1 of apex class:

System.LimitException: Too many query locator rows: 10001

 

Had try to update a previous version with just comments, but still got the same error. Any idea?

The previous version was just sucessfully saved to server.

 

Have not begun to test yet before looking around.  Cheeers