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.throwIfQueryPageSizeIsNotSet 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.

const results = await session
    .query({ indexName: "Products/ByUnitsInStock" })
    .whereGreaterThan("UnitsInStock", 10)
    .all();
class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.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:

const results = await session
    .query({ indexName: "Products_ByUnitsInStock" })
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20) // skip 2 pages worth of products
    .take(10) // take up to 10 products
    .all(); // execute query
class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.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:

let stats;

const results = await session
    .query({ indexName: "Products/ByUnitsInStock" })
    .statistics(s => stats = s)
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20)
    .take(10)
    .all();

let totalResults = stats.totalResults;
class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.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:

const PAGE_SIZE = 10;
let pageNumber = 0;
let skippedResults = 0;
let results;
let stats;

do {
    results = await session
        .query({ indexName: "Products/ByUnitsInStock" })
        .statistics(s => stats = s)
        .skip((pageNumber * PAGE_SIZE) + skippedResults)
        .take(PAGE_SIZE)
        .whereGreaterThan("UnitsInStock", 10)
        .distinct()
        .all();

    skippedResults += stats.skippedResults;
    pageNumber++;
} while (results.length > 0);
class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Products.Select(product => new {    
            UnitsInStock = product.UnitsInStock
        })`;
    }
}

const PAGE_SIZE = 10;
let pageNumber = 0;
let skippedResults = 0;
let results;
let stats;

do {
    results = await session
        .query({ indexName: "Order/ByOrderLines/ProductName" })
        .statistics(s => stats = s)
        .skip((pageNumber * PAGE_SIZE) + skippedResults)
        .take(PAGE_SIZE)
        .all();

    skippedResults += stats.skippedResults;
    pageNumber++;
} while (results.length > 0);
class Orders_ByOrderLines_ProductName extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.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):

const PAGE_SIZE = 10;
let pageNumber = 0;
let results;

do {
    results = await session
        .query({ indexName: "Orders/ByStoredProductName" })
        .selectFields("Product")
        .skip(pageNumber * PAGE_SIZE)
        .take(PAGE_SIZE)
        .distinct()
        .all();

    pageNumber++;
} while (results.length > 0);
class Orders_ByStoredProductName extends AbstractIndexCreationTask {
    constructor() {
        super();

        this.map = `docs.Orders.SelectMany(
            order => order.Lines, 
            (order, line) => new {    
                Product = line.ProductName
            })`;

        this.store("Product", "Yes");
    }
}