3

I have a custom report which i need to build on visual force page which is displayed on a force.com site. I have to display count() of records on a custom object which has more than 500k records. I am using a soql query like this SELECT COUNT() FROM ACS_USER__c WHERE (CreatedDate >= :fromDate AND CreatedDate <= :toDate)]; This query is throwing a governor limit of more than 150,000 records limit. What will be the best way to do this to avoid the governor limit?

4

I think you cannot get the complete count using a SOQL, not 100% sure. Initially I thought you could use a @readOnly annotated method and then get the count but then i think that cannot be done as the you will be executing the query from the context of a VF page. But I can think there are ways of doing this, As of now I can think of 2 methods(I am sure there will be easier way of doing this):

1.) You can make a http request using the REST API. Before using this code make sure you add the salesforce url in the remote site setting. The response you get contains contains a atribute called "totalSize", which gives you the count of number of rows returned by the query (which is mentioned in the REST URL).You can run the below code in the developer console to check. to get the use the below code:

    HttpRequest req = new HttpRequest();
 req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v20.0/query/?q=SELECT+name+from+Account');
 req.setMethod('GET');

string autho = 'Bearer '+ userInfo.getsessionId();
 req.setHeader('Authorization', autho);

 Http http = new Http();
 HTTPResponse res = http.send(req);
 string response = res.getBody();
 string totalCount = response.substring(response.indexOf('totalSize":')+11, response.indexOf(','));
system.debug('get the total count '+ totalCount);

2.) As you mentioned you are using a VF page, you can do the rest call in the VF page itself which is easier way, using jquery:

$.ajax({
url : "https://yourinstance/services/data/v20.0/query/?q=SELECT+name+from+Account",
headers : {"Authorization": "Bearer "+ AddSessionId}, //make sure you add the sessionId
contentType : "application/json"
}).done(function(response){
    console.log('The count is '+ response.totalSize);

});
  • <apex:page readonly="true"> sets the readonly status for VF pages. – Richard Durrant Jul 12 '13 at 9:19
  • @RichardDurrant: How does adding the readOnly attribute in page help? – Sam Jul 12 '13 at 9:32
  • It's in response to your first point about whether you can set VF pages to read only... – Richard Durrant Jul 12 '13 at 9:34
  • @theGreatDanton Thanks for your reply. Now if i use a rest api to make a call, will i not hit the governor limit of more than 50,000 records? – buyan kumar Jul 12 '13 at 10:23
  • 1
    Nope, you will not hit the governer limit as the REST will return only 2000 records at max per call, but the response will also contain the total number of records returned by the query, so as you wanted only the count i suggested this approach... I tried with a query which returned 39 million records, I was a able to get the count.. – Sam Jul 12 '13 at 11:17
1

With the Summer '18 Release there was a new feature for this:

Basically, the COUNT() no longer contributes towards the query row limit (typically 50,000 records). Instead in counts once towards that limit. There is some additional consideration when using GROUP BY, but generally you can count a far greater number of records now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.