Custom soql return too many rows

Question

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?

Answer 1

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;
    }
}