How to remove the query size limit for VF page

Question

I'm using standard set controller, with dynamic search options on VF page. Now soql limit 1000 it works perfect.

But i've to remove the soql querye limit. I.e. I can be able to fetch 70000 record also.

I'm updating record from my apex code via wrapper class.

IF I use read only= true and Standard set control pagination then can i able to fetch more than 50000 record.?

Now i'm try to use the offset in soql limit but when i use the offset is soql query, it will not fech the record(means offset word in query not working).

Please Guide for solution

My lat part of dynamic soql query is .

QueryLion += ' ORDER BY ' + String.escapeSingleQuotes(SortField) + ' ' + String.escapeSingleQuotes(SortDirection) + ' LIMIT '+String.escapeSingleQuotes(String.ValueOf(list_size))+' OFFSET ' +String.escapeSingleQuotes(String.ValueOf(counter));

even if i try below below hard code its not working

QueryLion += ' ORDER BY ' + String.escapeSingleQuotes(SortField) + ' ' + String.escapeSingleQuotes(SortDirection) + ' LIMIT 1000 offset 2';

My Vf page Screenshot. getting zer0 record when used offset.

Answer 1

The limit for the StandardSetController is 10,000:

The maximum record limit for StandardSetController is 10,000 records. Instantiating StandardSetController using a query locator returning more than 10,000 records causes a LimitException to be thrown. However, instantiating StandardSetController with a list of more than 10,000 records doesn’t throw an exception, and instead truncates the records to the limit.

The limit for OFFSET is 2,000:

The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.

Instead, if you really want to break the limits, you're going to have to go to JavaScript. There's a lot of potential solutions, but basically you're going to create a script to load all of the data into the client, and then render it client-side.

The only problem that you might run into is that there are various megabyte limits for how much data you can transfer at once. For example, any Visualforce response is a limit of 15,000,000 bytes, Apex Code is still going to be limited to 6,000,000 bytes of heap used, and so on.

One common technique I use is to paginate by ID values. That would go like this:

// Apex Code
@RemoteAction public static Account[] getAccounts(Id firstId) {
    return [SELECT Id FROM Account WHERE Id > :firstId ORDER BY Id LIMIT 2000];
}

---

// JavaScript
(function() {
    var cache = [],
        pageNumber = 1;
    function renderTable() {
        // Do your rendering here
    }
    function getResponse(data, event) {
        if(data && data.length) {
            data.forEach(function(v) { cache.push(v); });
            loadMoreRecords(data[data.length-1].Id);
        } else {
            renderTable();
        }
    }
    function loadMoreRecords(id) {
        MyController.getAccounts(id, getResponse);
    }
    loadMoreRecords(null);
})();

You might want to pre-query how many records there are, so you can render a progress bar. There's various libraries for jQuery, AngularJS, Backbone, and so on that can help you with the pagination. Here's an example written in jQuery that you can use to get started.

This would also be something you could write up in Lightning without too much effort. I've written a gist that demonstrates a quick pagination/sorting demo you could use to get started.