+ Start a Discussion
Marty C.Marty C. 

How to paginate query ordered by two lookup or master-detail fields?

I have a query that returns over 190,000 records, and I need to break those records up into smaller chunks for performance and presentation in Visualforce. Each chunk is supposed to presented on a single page, and pagination is implemented using the OFFSET and LIMIT clauses in SOQL. A simplified version of the query containing the key elements is shown below:

SELECT Account__c, Product2__c, MyValue__c
FROM MyObject__c
ORDER BY Account__r.Name, Product2__r.Name

As implied, Account__c and Product2__c are both relationships to the standard Account and Product2 objects. For what it's worth, Account__c is a Master-Detail(Account) field and Product2__c is a Lookup(Product) field.

I want to limit each page to a consistent number of Account-Product2 combinations. But I am having trouble doing this because each combination can have a variable number of MyObject__c records.

What would be a good solution for implementing consistent pagination in this situation? I have tried creating a "composite ID" formula field by concatenating Account__c and Product2__c. But the MyObject__c query times out when I add "AccountProductCompositeId__c IN :compositeIdSet" to the WHERE clause, even when the number of combinations per page is limited to 25.

At this point I am thinking of running two queries to pull in more information than I need, and then use Apex to drop the excess data before presenting the data on the Visualforce page.

Hi , 

You should try Batch apex, 


This is probably because the fields you query in the WHERE clause aren't indexed, this way your query will take longer, and will eventually time out with large data sets.

I suggest taking a look at the following webinar, it explains perfectly how to handle big data sets, and what you can do to improve your query times.

Webinar: Extreme Salesforce Data Volumes