Querying: Paging
Paging, or pagination, is the process of splitting a dataset into pages, reading one page at a time. This is useful for optimizing bandwidth traffic and hardware usage or simply because no user can handle huge amounts of data at once.
Warning
Starting from version 4.0, if the page size is not specified on client side, the server will assume int.MaxValue (2,147,483,647) and all the results will be downloaded. It is recommended to set a page size explicitly to avoid long response times caused by sending excessive amounts of data over the network or high memory consumption caused by handling large quantities of documents.
You can also set DocumentConventions.setThrowIfQueryPageSizeIsNotSet convention to true to guard yourself from executing queries without the page size explicitly set. We recommend turning this convention on, especially during development or testing phases to detect early the queries that potentially can return an excessive amount of results.
Performance
By default, if the number of returned results exceeds 2048, the server will issue a Performance Hint notification (visible in the Studio) with information about query details. You can decide if this behavior is desired or not.
The threshold can be adjusted by changing the PerformanceHints.MaxNumberOfResults configuration value.
Example I - No Paging
The queries below will return all the results available.
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.toList();public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}Example II - Basic Paging
Let's assume that our page size is 10, and we want to retrieve the 3rd page. To do this, we need to issue following query:
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.skip(20) // skip 2 pages worth of products
.take(10) // take up to 10 products
.toList(); // execute querypublic static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}Finding the Total Results Count When Paging
While paging, you sometimes need to know the exact number of results returned from the query. The Client API supports this explicitly:
Reference<QueryStatistics> stats = new Reference<QueryStatistics>();
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.statistics(stats)
.whereGreaterThan("UnitsInStock", 10)
.skip(20)
.take(10)
.toList();
int totalResults = stats.value.getTotalResults();public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}While the query will return with just 10 results, totalResults will hold the total number of matching documents.
Paging Through Tampered Results
For some queries, the server will skip over some results internally and invalidate the totalResults value. When executing a distinct query or index producing multiple index entries per document (a fanout index), then totalResults will contain the total count of matching documents found, but it will not take into account results that were skipped as a result of the distinct operator.
Whenever skippedResults is greater than 0 and a query involved some non-stored fields, it implies that we skipped over some results in the index.
In order to do proper paging in those scenarios, you should use skippedResults when telling RavenDB how many documents to skip. For each page, the starting point should be .skip((currentPage * pageSize) + skippedResults).
For example, let's page through all the results:
List<Product> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;
Reference<QueryStatistics> stats = new Reference<>();
do {
results = session
.query(Product.class, Products_ByUnitsInStock.class)
.statistics(stats)
.skip((pageNumber * pageSize) + skippedResults)
.take(pageSize)
.whereGreaterThan("UnitsInStock", 10)
.distinct()
.toList();
skippedResults += stats.value.getSkippedResults();
pageNumber++;
} while (results.size() > 0);public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}List<Order> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;
Reference<QueryStatistics> stats = new Reference<>();
do {
results = session
.query(Order.class, Order_ByOrderLines_ProductName.class)
.statistics(stats)
.skip((pageNumber * pageSize) + skippedResults)
.take(pageSize)
.toList();
skippedResults += stats.value.getSkippedResults();
pageNumber++;
} while (results.size() > 0);public static class Orders_ByOrderLines_ProductName extends AbstractIndexCreationTask {
public Orders_ByOrderLines_ProductName() {
map = "docs.Orders.SelectMany(order => order.Lines, (order, line) => new {" +
" Product = line.ProductName " +
"})";
}
}The situation would be different if a distinct query and a projection applied to stored fields only. To get the correct results here, you shouldn't include skippedResults
into the paging formula. Let's take a look at the example (note the usage of store method in the index definition):
List<Orders_ByStoredProductName.Result> results;
int pageNumber = 0;
int pageSize = 10;
do {
results = session
.query(Orders_ByStoredProductName.Result.class, Orders_ByStoredProductName.class)
.selectFields(Orders_ByStoredProductName.Result.class, "Product")
.skip(pageNumber * pageSize)
.take(pageSize)
.distinct()
.toList();
pageNumber++;
} while (results.size() > 0);public static class Orders_ByStoredProductName extends AbstractIndexCreationTask {
public static class Result {
private String product;
public String getProduct() {
return product;
}
public void setProduct(String product) {
this.product = product;
}
}
public Orders_ByStoredProductName() {
map = "docs.Orders.SelectMany(order => order.Lines, (order, line) => new {" +
" Product = line.ProductName" +
"})";
store("product", FieldStorage.YES);
}
}
Related Articles