I need literally all the records. Documentation says that if I hit 120 sec. limit then I should refactor my query to retrieve less amount of data. The only option not to skip data but collect it part by part in multiple requests is LIMIT+OFFSET clause. But OFFSET doesn't support values greater than 2000, and even if did, the option is poor due to data race. Among remaining options there is getUpdated request, which is limited to last 30 days, and Async SOQL, which requires a custom object to store the result. The latter seems helpful only when the query itself takes a magnitude more time than the non-async query to the result object, i.e. some GROUP BY aggregations, filters etc. In my case the result object will have as many records as the source.
Is there any reliable approach to get such a big listing? I'm dealing with tens of millions records in organization. Nested SELECT queries are eventually impossible due to timeout. Even a trivial "SELECT Id" fails if it's CaseHistory. Haven't found any satisfactory answer yet.
Best Answer
Since, it looks like you need to query entire Case table, PK Chunking might work best for this. https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/async_api_headers_enable_pk_chunking.htm.
There are many techniques for this, out of which you have also mentioned a few. I would suggest to try the different options, check this trail (https://trailhead.salesforce.com/content/learn/modules/large-data-volumes/conduct-data-queries-and-searches) and see what works best for you.