12

How to query more than 50000 records (78000 records) in apex and bind it in vf page in jqgrid and implement paging in it?

I tried querying through SOQL and assigning it to list.. Am unable to query more than 50000 records.

I tried offset but unfortunately offset doesnt accept more than 2000 as the number..

Tried database.getquerylocator and standardsetcontroller, but its returning only 10000 records and am able to bind it to jqgrid.

How to bind all the 78000 records to the page in jqgrid with paging (max 100 records per page)?

I have pasted the code below and in my assetcontroller am not able to give soql limit more than 10000. And I have even tried hitting the 100th Page (with each page having 100 records) and did con.getrecords() and again if I tried con.hasnext() it returns false, so it doesnt go beyond 10000 records at all..

@Jordan will it work if i give @remoteAction? if so what attributes (ex: datatype,url) do i need to change in my jqgrid ? and what else i need to do in my code?

// Visualforce page containing JQGrid :-


//Note AssetGridPage is a dummy page which will only return JSONresult given by the 
//controller and it is of contentType="application/x-JavaScript; charset=utf-8"

JQGridurl='https://c.cs11.visual.force.com/apex/AssetGridPage?core.apexpages.devmode.url=1&action=populateAssetSource';
var objectList = j$("#objectList").jqGrid(
                {
                        url: JQGridurl,                        
                        datatype:"json",
                        colNames: eval(ColNames),
                        colModel: eval(ColModel),
                        pager: '#objectListpager2',
                        toppager : true,                                                
                        rownumbers: true, 
                        gridview: true,
                        altRows: true,
                        enableSearch: false,
                        viewrecords: true,
                        sortname: 'AssetID',
                        sortorder: 'desc',              
                        rowNum:100,
                        rowList:[10,25,50,100],
                        ignoreCase:true,
                        //pagination:true,
                        loadtext:'Loading...'                        
                    }); 

// Asset Controller Code

                  
// Getter Method: JSONResult
    public String getJSONResult()
    {
        return returnJson;
    }

    public PageReference PageLoad() 
    {
        Map<string,string> params = ApexPages.currentPage().getParameters();
        if(params != null)
        {
            PageNum = Integer.ValueOf((params.get('page') != null)?params.get('page'):'1');
            PageSize = Integer.ValueOf((params.get('rows') != null)?params.get('rows'):'100');
            CurrentPage = Integer.ValueOf((params.get('page') != null)?params.get('page'):'1');
        }
        else 
        {
            PageNum = 1;
            PageSize = 100;
            CurrentPage = 1;
        }
        populateAssetSource();
        return null;
    }

    public void populateAssetSource()
    {   
        Decimal RecCount = 0;
        String SOQL = 'SELECT AssetID__c, AssetDescription__c FROM Asset__c limit 10000';
        con = new ApexPages.StandardSetController(Database.getQueryLocator(SOQL)); 
        con.setPageSize((Integer)PageSize);
        setPageNumber();
        // sets the number of records in each page set      
        //system.debug('RecCount::'+RecCount);
        //system.debug('PageSize::'+PageSize);
        //system.debug('CurrentPage::'+CurrentPage);
        if(con != null)
        {
            AllSearchAssets = getAllAssets((List<Asset__c>)con.getRecords());
            RecCount = con.getResultSize();
            String JSONString = JSON.serialize(AllSearchAssets);
            returnJson =  '{"total":' +  '"' + ((PageSize==0 && RecCount ==0)?0:(RecCount/PageSize).round(system.roundingMode.CEILING)) +'",';
            returnJson += '"page":' + '"' + ((RecCount == 0)?0:CurrentPage) +'",';
            returnJson += '"records":'  +  '"' + RecCount +'",';
            returnJson += '"rows" : ' + JSONString + '}';
        }
        else
        {
            returnJson = '{"total":"0","page":"0","records":"0","rows" : []}';
        }
    }

The following code is in AssetGridPage.. empty page which is hit by the jqgrid


<apex:page controller="AssetController" action="{!PageLoad}"
    contentType="application/x-JavaScript; charset=utf-8" showHeader="false" standardStylesheets="false" sidebar="false">
        {!JSONResult}
</apex:page>

@joshbirk so you are saying something like this?


List<asset__c> lstasset1 = Database.getquerylocator(Select id from asset__c where createddate > 'July-1-2012' and createddate < 'Aug-1-2012');

List<asset__c> lstasset2 = Database.getquerylocator(Select id from asset__c where createddate > 'Aug-1-2012' and createddate < 'Sep-1-2012');
lstasset1.addall(lstasset2);

And in the wrapper class of listasset1, add some pagenumber or some equivalent field. Foreach 100 increment the pagenumber variable.

Bind this lstasset1 to page based on some querystring filter and show only the 100. this is what you are saying right? if this is the case I cant go for a sorting of list. is there a possiblity to sort a list of wrapperclass type?

And one more limit we will be hitting is no. of query rows. we cant have more than 50000 records queried per transaction :(

So I guess even this solution wouldnt work.. Any Suggestions?

12

I've done jqGrid before in Apex with @RemoteActions and it's definitely the way to go. I think with Ajax toolkit and SOAP, you'll have too much XML bloat compared to JSON for large sets of records.

As for loading all 78000 records, I would say you should stick to the limits of loading 2000 records at a time and than breaking those into further subsets of 100 records / page on the client side.

To pagination with large record sets I would recommend using the standard set controller for pagination. Just make sure to include an order clause to keep the records returning in the same order.

Than load the next 2000 records in each subsequent call. And reduce the number of records you return from the server to speed things up in between 'set loads' as you get to the end of each set and need more pages.

You can also count the number of records and return it in a complex object from the @RemoteAction to keep your pagination accurate:

public class returnType{
  List<sObject> objects {get; set;}
  Integer totalRecords {get; set;}
  Integer nextPage {get; set;}
  Integer lastPage {get; set;}
}

UPDATE

Ok using the SSC to maintain pagination (at least this is the way I would do it with jqGrid:

@RemoteAction
global static returnType pagination(Integer pageNumber){
   ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Id FROM sObject ORDER BY Id LIMIT 50000]);
   ssc.setPageNumber(pageNumber);
   ssc.Pagesize(2000);

   returnType retVal = new returnType();
   retVal.objects = ssc.getRecords();
   retVal.nextPage = pageNumber + 1;
   retVal.previousPage = pageNumber - 1;

   return retVal;
}

I don't have my original code in front of me, but generally, the SSC should be re-instantiated with each call. As for getting past 50k records, and to your total set, the query locator should continue to the end of the set reguardless of size taking the larger records towards the end of the set as you get to the end of [0..n-1] pages.

In practice I would use an ORDER BY clause to ensure your records are consistently returned in the same order to ensure that your getting all results.

  • How is he going to manage loading it chunks of 2000? The Offset clause won't go higher than 2000 and he can't query the full amount via RemoteAction because he'll exceed the governor limit of 50,000 SOQL rows. He could possibly handle it by creating an auto-number field on the object and using that as part of the where clause. – E.J. Wilburn Aug 22 '12 at 19:25
  • 1
    And Jordan thanks for your response, I have already mentioned in my post that i have used standardsetcontroller and database.gequerylocator am able to fetch 10000 records from that and bind it to jqgrid and on clicking next and previous buttons or setting page numbers in jqgrid, am able to pull that particular page and show in jqgrid. I am displaying only 100 records per page. since i have used standardset controller am able to achieve this.. but database.getquerylocator doesnt return more than 10000 records. If its more than 10000 (say 78000), it says "Too many query locator rows: 10001" – Sathya Aug 23 '12 at 11:31
  • 1
    This is exactly the same code that I have written @jordan.baucke but it's not going beyond 10000. I mean even if I say limit 10001 at the end of my query inside the database.getquerylocator it's throwing too many query rows 10001 error. Except that am not using remote action... Instead in the jqgridurl property I am specifying the URL and jqgrid by default appends the page number to the URL query string when I click next. processing the URL through the same code That you have written & converting it to list<wrapper class> and doing Json.serialize and returning json formatted string for jqgrid – Sathya Aug 24 '12 at 4:30
  • 2
    @Sathya - you might also look at SlickGrid as an alternate to jqGrid. It is much rougher and simpler - but handles large datasets well. – joshbirk Aug 24 '12 at 22:38
  • 1
    There are a horde of comments here but I wanted to point out that it may be better to order by Id which is new summer 13 so you dont lose records with the same created date between chunks. – Phil Rymek May 15 '13 at 17:28
10

There's a lot to consider here:

  1. You'll probably want to use JavaScript Remoting as the transport since you may need to be dealing with more than 1000 records at a time
  2. If OFFSET can't work, you can use another field as a "marker". It depends on how your data is structured, how often it gets bulk loaded, etc. Some people have used a standard field like CreatedDate - others a custom autonumber. Both have their caveats but can generally be bludgeoned into working.
  3. Technically, be wary of how much data you are putting into the interface at any one time. You can technically shove a lot into the page, but some browsers/scripts/devices will only be able to handle so much.
  • Thanks for your responses Josh : 1. I understand i am using more than 1000 records but at a time am displaying only 100 in the grid. Not sure how javascript remoting will help here? 2. Since I have 78000 records OFFSET doesnt work for me as it doesnt accept more than 2000. and If i have a createdate or some field, my records will be filtered and i wouldnt be able to show the data to users. I have mentioned my scenario in one of the comments below jordan's answer. can you please take a look? 3. I want to show only 100 records in the jqgrid per page total data needs to bound in jqgrid is 78000 – Sathya Aug 23 '12 at 11:47
3

You can use the AJAX Toolkit to query via Salesforce's Web Service API via javascript. That will allow you to query any amount by pulling back chunks of up to 2000 records at a time using the query and queryMore calls.

See the AJAX Toolkit documentation here: http://www.salesforce.com/us/developer/docs/ajax/index.htm

2

Although I doubt you really want to load that many records in one go, you can set Read-Only mode on your Visualforce page to relax the limit on how many rows can be queried:

<apex:page controller="myController" readOnly="true">

Read-Only mode is really intended for use where you'll summarize the results, not to return zillions of rows.

See "Working with Large Sets of Data" in the Visualforce Developer's Guide for more information about Read-Only mode.

  • There's still an issue of the request time itself. If working with large sets of data, you'll more than likely encounter "Time limit exceeded" exceptions on the request. – James Loghry Aug 23 '12 at 0:50
  • Hi Alderete, I will just be displaying 100 records at the max in the page and not more than that but my data size is 78000 records. Only if i had to show everything in the same page at a time in the grid without any paging i would need this. moreover am doing other dml operations in the page, so i cant give readonly=true... Even though we give readonly=true we can bind only a list of 10000 records at the max to a VF page, that too if it needs to be shown at 1 shot. So this doesnt solve my problem :( Thanks for your input anyways.. – Sathya Aug 23 '12 at 10:48

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.