1

What is my best option to return the results of a query where the soql query by default returns too many rows (>50k)?

From my understanding, SELECT COUNT() still cannot return a result >50k, but my use case requires the result to show ~75k (and growing). Thoughts?

  • One option I'm thinking about is creating a new sObject that could do a roll-up summary that I could then query... Any better options out there? – Nathan Williams Jul 26 '15 at 18:07
  • You can try @ReadOnly annotation option available if you requirement is not to do write operation. – San Jul 26 '15 at 18:34
3

Have you tried using the @ReadOnly annotation on your code?

If you are using a scheduled job or calling the code from a Webservice or from a VisualForce page, you can use this annotation (as long as it's the top-level calling code - ie a VF page without the readOnly attribute set to true could not call a readonly WebService.

Using @ReadOnly allows you to perform virtually unlimited queries on the database with one, rather large caveat: No DML (and a few other restrictions)

Take a look at the docs here

Interestingly enough, there is a difference between the readOnly attribute of a VF page and the @ReadOnly annotation:

  • Using the readOnly attribute limits you to 1,000,000 records retrieved. There is probably a limit here to try to avoid heap size issues.
  • @ReadOnly is effectively unlimited.

EDIT I just performed a test to see if custom settings were affected by this restriction and I can report back that they are NOT affected!! This means that if you really really want to perform some DML, you could save your sObject in serialized form in a custom setting and use some other scheduled job to perform the modification.

This would work well if you are only modifying 1 or 2 objects, but would get pretty unweildy if you wanted to do more than that. Here is my test code to prove the webservice works:

global class ReadOnlyTestWS {
    @ReadOnly
    webservice static void test(){
        Account[] accts = [SELECT Name, Id FROM Account];

        Test_ReadOnly__c tr = Test_ReadOnly__c.getInstance('Test');
        tr.Test__c = true;
        update tr;
    }
}
  • Ugh, you had me really excited for a moment until I did a quick bit of research and learned it's not available to REST services... success.salesforce.com/ideaView?id=08730000000kourAAA – Nathan Williams Jul 26 '15 at 19:11
  • Any other ideas? – Nathan Williams Jul 26 '15 at 19:11
  • 1
    It's not available as a REST service, but you can still call that webservice from outside of Salesforce - you can expose it using sites - here's a good example: developer.salesforce.com/page/… – Caspar Harmer Jul 26 '15 at 20:47
  • To avoid the need for a full re-write (until I can at least do some more research), I decided to use the /query/q= API provided by salesforce, which allows me to skirt the issue. SELECT COUNT() queries there can be well over 50k rows and perform very quickly. – Nathan Williams Jul 26 '15 at 20:50
  • Sounds like a good solution - and I wasn't aware that there were higher limits there. Good to know. – Caspar Harmer Jul 26 '15 at 21:10

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.