Pros and Cons of Choosing Pagination in Salesforce.
Visualforce StandardSetController
The
StandardSetController is an extremely powerful tool with built-in functionality
that you can use to greatly simplify the custom code in your Visualforce pages.
Because the server returns only the data for the page being requested, the
StandardSetController can significantly reduce view state, especially compared
to the view state you would get while using SOQL.
Pros
· Manages data sets on the server, which reduces page state and
increases performance
· Allows you to paginate over large data sets that have up to
10,000 records
· Includes built-in functionality such as next, previous, first,
last, getResultSize, and other methods that can simplify your page
· Allows you to paginate forward and backward or to any page
within the result set
· Uses a server-side cursor to cache the entire result set, so
results will not change if data changes in the database while a user is
paginating from one page to the next
Cons
· Has built-in functionality that results in a very small increase
in view state size
· Can be used only in Apex
You can find more
information about StandardSetController in our Visualforce documentation here.
Example: Retrieving Data in Apex to Paginate with the StandardSetController
public class opportunityList {
public ApexPages.StandardSetController setCon {
get {
if(setCon == null) {
setCon = new
ApexPages.StandardSetController(Database.getQueryLocator(
[SELECT Name, StageName,
Amount, Account.Name, Account.Named_Account__c FROM Opportunity where
Account.BillingState = ‘California’ and Status__c = ‘Open’]));
}
return setCon;
}
set;
}
// Initialize setCon and return a list of records
public List getOpportunities() {
return (List) setCon.getRecords();
}
}
OFFSET Clauses within SOQL Queries
With Salesforce SOQL,
you can add an OFFSET clause, as well as a limit specifying a start point and a
range of records to retrieve for a page, within your queries. This
functionality allows you to implement your own pagination with SOQL queries without
having to store the entire data set in memory. As a user moves from one page to
the next, each page request executes a query to return the specific records
required for that page.
Pros
· Can be used in SOQL executed within the SOAP API, the REST API,
or Apex
· Gives you the ability to build robust pagination using the
Force.com APIs without having to store the entire data set in the Web
application
Cons
· Supports OFFSET clauses only for ranges of 2,000 or fewer
records
· No server-side cursor is created to cache the full result set
for future OFFSET queries. This means that what you see in each page of results
reflects what is in the database at the time that you submitted your request.
So if someone modifies the underlying data while you're paginating over large
data sets, what you saw in the previous or following pages might have changed.
· As users move from one page the next in the result set, each of
their requests for page results executes a query against the database.
· If you want to build advanced pagination functionality that
displays a specific number of result pages, you must use a SOQL query to
retrieve the total number of records before you execute the query containing
the OFFSET clause. Executing this SOQL query to count the records affects
performance and limits usage.
While many developers
view OFFSET as the tool of choice for pagination, we will see that there are
many cases in which it is not the tool of choice from a performance
perspective.
To read more about
OFFSET, see its Force.com SOQL
and SOSL Reference topichere.
Example:
Retrieving Data With OFFSET to build Custom Pagination
This query returns the first 25 records of our result set.
SELECT Name, StageName, Amount, Account.Name,
Account.Named_Account__c
FROM Opportunity
WHERE Account.BillingState = ‘California’
AND Status__c = ‘Open’
ORDER BY Account.Name
LIMIT 25
OFFSET 0
When the user selects the next page in the result set, this query returns the next set of 25 records.
SELECT Name, StageName, Amount, Account.Name,
Account.Named_Account__c
FROM Opportunity
WHERE Account.BillingState = ‘California’ and Status__c = ‘Open’
ORDER BY Account.Name
LIMIT 25
OFFSET 25
