You are currently browsing legacy 5.0 version of documentation. Click here to switch to the newest 6.2 version.
    We can help you with migration to the latest RavenDB
    Contact Us Now
    See on GitHub

    Page Query Results

    • Paging:
      Paging is the process of fetching a subset (a page) of results from a dataset, rather than retrieving the entire results at once. This method enables processing query results one page at a time.

    • Default page size:
      If the client's query definition does Not explicitly specify the page size, the server will default to int.MaxValue (2,147,483,647). In such case, all results will be returned in a single server call.

    • Performance:
      Using paging is beneficial when handling large result datasets, contributing to improved performance.
      See paging and performance here below.

    • Paging policy:
      To prevent executing queries that do not specify a page size, you can set the ThrowIfQueryPageSizeIsNotSet convention, which can be useful during development or testing phases.

    • In this page:

    No paging example 

    // A simple query without paging:
// ==============================

List<Product> allResults = session
    .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
    .Where(x => x.UnitsInStock > 10)
    .OfType<Product>()
    .ToList();

// Executing the query on the Northwind sample data
// will result in all 47 Product documents that match the query predicate.
    // A simple query without paging:
// ==============================

List<Product> allResults = await asyncSession
    .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
    .Where(x => x.UnitsInStock > 10)
    .OfType<Product>()
    .ToListAsync();

// Executing the query on the Northwind sample data
// will result in all 47 Product documents that match the query predicate.
    // A simple DocumentQuery without paging:
// ======================================

List<Product> allResults = session.Advanced
    .DocumentQuery<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
    .WhereGreaterThan(x => x.UnitsInStock, 10)
    .OfType<Product>()
    .ToList();

// Executing the query on the Northwind sample data
// will result in all 47 Product documents that match the query predicate.
    public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
    public class IndexEntry
    {
        public int UnitsInStock { get; set; }
    }
    
    public Products_ByUnitsInStock()
    {
        Map = products => from product in products
            select new
            {
                UnitsInStock = product.UnitsInStock
            };
    }
}
    from index "Products/ByUnitsInStock"
where UnitsInStock > 10

    Paging examples

    Retrieve a specific page:

    // Retrieve only the 3'rd page - when page size is 10:
// ===================================================

List<Product> thirdPageResults = session
    .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
     // Get the query stats if you wish to know the TOTAL number of results
    .Statistics(out QueryStatistics stats)
     // Apply some filtering condition as needed
    .Where(x => x.UnitsInStock > 10)
    .OfType<Product>()
     // Call 'Skip', pass the number of items to skip from the beginning of the result set
     // Skip the first 20 resulting documents
    .Skip(20)
     // Call 'Take' to define the number of documents to return
     // Take up to 10 products => so 10 is the "Page Size"
    .Take(10)
    .ToList();

// When executing this query on the Northwind sample data,
// results will include only 10 Product documents ("products/45-A" to "products/54-A")

int totalResults = stats.TotalResults;

// While the query returns only 10 results,
// `totalResults` will hold the total number of matching documents (47).
    // Retrieve only the 3'rd page - when page size is 10:
// ===================================================

List<Product> thirdPageResults = await asyncSession
    .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
     // Get the query stats if you wish to know the TOTAL number of results
    .Statistics(out QueryStatistics stats)
     // Apply some filtering condition as needed
    .Where(x => x.UnitsInStock > 10)
    .OfType<Product>()
     // Call 'Skip', pass the number of items to skip from the beginning of the result set
     // Skip the first 20 resulting documents
    .Skip(20)
     // Call 'Take' to define the number of documents to return
     // Take up to 10 products => so 10 is the "Page Size"
    .Take(10)
    .ToListAsync();

// When executing this query on the Northwind sample data,
// results will include only 10 Product documents ("products/45-A" to "products/54-A")

int totalResults = stats.TotalResults;

// While the query returns only 10 results,
// `totalResults` will hold the total number of matching documents (47).
    // Retrieve only the 3'rd page - when page size is 10:
// ===================================================

List<Product> thirdPageResults = session.Advanced
    .DocumentQuery<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
     // Get the query stats if you wish to know the TOTAL number of results
    .Statistics(out QueryStatistics stats)
     // Apply some filtering condition as needed
    .WhereGreaterThan(x => x.UnitsInStock, 10)
    .OfType<Product>()
     // Call 'Skip', pass the number of items to skip from the beginning of the result set
     // Skip the first 20 resulting documents
    .Skip(20)
     // Call 'Take' to define the number of documents to return
     // Take up to 10 products => so 10 is the "Page Size"
    .Take(10)
    .ToList();

// When executing this query on the Northwind sample data,
// results will include only 10 Product documents ("products/45-A" to "products/54-A")

int totalResults = stats.TotalResults;

// While the query returns only 10 results,
// `totalResults` will hold the total number of matching documents (47).
    public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
    public class IndexEntry
    {
        public int UnitsInStock { get; set; }
    }
    
    public Products_ByUnitsInStock()
    {
        Map = products => from product in products
            select new
            {
                UnitsInStock = product.UnitsInStock
            };
    }
}
    from index "Products/ByUnitsInStock"
where UnitsInStock > 10
limit 20, 10 // skip 20, take 10

    Page through all results:

    // Query for all results - page by page:
// =====================================

List<Product> pagedResults;
int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = session
        .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
         // Apply some filtering condition as needed
        .Where(x => x.UnitsInStock > 10)
        .OfType<Product>()
         // Skip the number of results that were already fetched
        .Skip(pageNumber * pageSize)
         // Request to get 'pageSize' results
        .Take(pageSize)
        .ToList();
    
    pageNumber++;
    
    // Make any processing needed with the current paged results here
    // ...
}
while (pagedResults.Count > 0); // Fetch next results
    // Query for all results - page by page:
// =====================================

List<Product> pagedResults;
int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = await asyncSession
        .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
         // Apply some filtering condition as needed
        .Where(x => x.UnitsInStock > 10)
        .OfType<Product>()
         // Skip the number of results that were already fetched
        .Skip(pageNumber * pageSize)
         // Request to get 'pageSize' results
        .Take(pageSize)
        .ToListAsync();
    
    pageNumber++;
    
    // Make any processing needed with the current paged results here
    // ...
}
while (pagedResults.Count > 0); // Fetch next results
    // Query for all results - page by page:
// =====================================

List<Product> pagedResults;
int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = session.Advanced
        .DocumentQuery<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
         // Apply some filtering condition as needed
        .WhereGreaterThan(x => x.UnitsInStock, 10)
        .OfType<Product>()
         // Skip the number of results that were already fetched
        .Skip(pageNumber * pageSize)
         // Request to get 'pageSize' results
        .Take(pageSize)
        .ToList();
    
    pageNumber++;
    
    // Make any processing needed with the current paged results here
    // ...
}
while (pagedResults.Count > 0); // Fetch next results
    public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
    public class IndexEntry
    {
        public int UnitsInStock { get; set; }
    }
    
    public Products_ByUnitsInStock()
    {
        Map = products => from product in products
            select new
            {
                UnitsInStock = product.UnitsInStock
            };
    }
}
    from index "Products/ByUnitsInStock"
where UnitsInStock > 10
limit 0, 10 // First loop will skip 0, take 10

// The next loops in the code will each generate the above RQL with an increased 'skip' value:
// limit 10, 10
// limit 20, 10
// limit 30, 10
// ...

    Paging and performance

    Better performance:

    It is recommended to explicitly set a page size when making a query that is expected to generate a significant number of results. This practice has several benefits:

    • Optimizes bandwidth usage by reducing data transfer between the server and client.
    • Prevents delays in response times caused by sending too much data over the network.
    • Avoids high memory consumption when dealing with numerous documents.
    • Ensures a more manageable user experience by not overwhelming users with massive datasets at once.

    Performance hints:

    • By default, if the number of returned results exceeds 2048, the server will issue a "Page size too big" notification (visible in the Studio) with information about the query.

    • This threshold can be customized by modifying the value of the PerformanceHints.MaxNumberOfResults configuration key.

    • As suggested by the hint, you may consider using Streaming query results instead of paging.

    Figure 1. Performance Hint

    Performance Hint

    Paging through tampered results

    • The QueryStatistics object contains the TotalResults property,
      which represents the total number of matching documents found in the query results.

    • The QueryStatistics object also contains the SkippedResults property.
      Whenever this property is greater than 0, that implies the server has skipped that number of results from the index.

    • The server will skip duplicate results internally in the following two scenarios:

      1. When making a Projection query with Distinct.

      2. When querying a Fanout index.

    • In those cases:

      • The SkippedResults property from the stats object will hold the count of skipped (duplicate) results.

      • The TotalResults property will be invalidated -
        it will Not deduct the number of skipped results from the total number of results.

    • In order to do proper paging in those scenarios:
      include the SkippedResults value when specifying the number of documents to skip for each page using:
      (currentPage * pageSize) + SkippedResults.

    • See the following examples:

    A projection query with Distinct:

    List<ProjectedClass> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = session
        .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
        .Statistics(out QueryStatistics stats)
        .Where(x => x.UnitsInStock > 10)
        .OfType<Product>()
         // Define a projection
        .Select(x => new ProjectedClass
        {
            Category = x.Category,
            Supplier = x.Supplier
        })
         // Call Distinct to remove duplicate projected results
        .Distinct()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
         // Define how many items to return
        .Take(pageSize)
        .ToList();
    
    totalResults = stats.TotalResults;        // Number of total matching documents (includes duplicates)
    skippedResults += stats.SkippedResults;   // Number of duplicate results that were skipped
    totalUniqueResults += pagedResults.Count; // Number of unique results returned in this server call
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total matching results reported in the stats is 47 (totalResults),
// but the total unique objects returned while paging the results is only 29 (totalUniqueResults)
// due to the 'Distinct' usage which removes duplicates.

// This is solved by adding the skipped results count to Skip().
    List<ProjectedClass> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = await asyncSession
        .Query<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
        .Statistics(out QueryStatistics stats)
        .Where(x => x.UnitsInStock > 10)
        .OfType<Product>()
         // Define a projection
        .Select(x => new ProjectedClass
        {
            Category = x.Category,
            Supplier = x.Supplier
        })
         // Call Distinct to remove duplicate projected results
        .Distinct()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
        .Take(pageSize)
        .ToListAsync();
    
    totalResults = stats.TotalResults;        // Number of total matching documents (includes duplicates)
    skippedResults += stats.SkippedResults;   // Number of duplicate results that were skipped
    totalUniqueResults += pagedResults.Count; // Number of unique results returned in this server call
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total matching results reported in the stats is 47 (totalResults),
// but the total unique objects returned while paging the results is only 29 (totalUniqueResults)
// due to the 'Distinct' usage which removes duplicates.

// This is solved by adding the skipped results count to Skip().
    List<ProjectedClass> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 10;
                
do
{
    pagedResults = session.Advanced
        .DocumentQuery<Products_ByUnitsInStock.IndexEntry, Products_ByUnitsInStock>()
        .Statistics(out QueryStatistics stats)
        .WhereGreaterThan(x => x.UnitsInStock, 10)
        .OfType<Product>()
         // Define a projection
        .SelectFields<ProjectedClass>()
         // Call Distinct to remove duplicate projected results
        .Distinct()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
        .Take(pageSize)
        .ToList();
    
    totalResults = stats.TotalResults;        // Number of total matching documents (includes duplicates)
    skippedResults += stats.SkippedResults;   // Number of duplicate results that were skipped
    totalUniqueResults += pagedResults.Count; // Number of unique results returned in this server call
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total matching results reported in the stats is 47 (totalResults),
// but the total unique objects returned while paging the results is only 29 (totalUniqueResults)
// due to the 'Distinct' usage which removes duplicates.

// This is solved by adding the skipped results count to Skip().
    public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
    public class IndexEntry
    {
        public int UnitsInStock { get; set; }
    }
    
    public Products_ByUnitsInStock()
    {
        Map = products => from product in products
            select new
            {
                UnitsInStock = product.UnitsInStock
            };
    }
}
    public class ProjectedClass
{
    public string Category { get; set; }
    public string Supplier { get; set; }
}
    from index "Products/ByUnitsInStock"
where UnitsInStock > 10
select distinct Category, Supplier
limit 0, 10  // First loop will skip 0, take 10, etc.

    Querying a Fanout index:

    List<Order> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 50;
                
do
{
    pagedResults = session
        .Query<Orders_ByProductName.IndexEntry, Orders_ByProductName>()
        .Statistics(out QueryStatistics stats)
        .OfType<Order>()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
        .Take(pageSize)
        .ToList();
    
    totalResults = stats.TotalResults;
    skippedResults += stats.SkippedResults;
    totalUniqueResults += pagedResults.Count;
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total results reported in the stats is 2155 (totalResults),
// which represent the multiple index-entries generated as defined by the fanout index.

// By adding the skipped results count to the Skip() method,
// we get the correct total unique results which is 830 Order documents.
    List<Order> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 50;
                
do
{
    pagedResults = await asyncSession
        .Query<Orders_ByProductName.IndexEntry, Orders_ByProductName>()
        .Statistics(out QueryStatistics stats)
        .OfType<Order>()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
        .Take(pageSize)
        .ToListAsync();
    
    totalResults = stats.TotalResults;
    skippedResults += stats.SkippedResults;
    totalUniqueResults += pagedResults.Count;
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total results reported in the stats is 2155 (totalResults),
// which represent the multiple index-entries generated as defined by the fanout index.

// By adding the skipped results count to the Skip() method,
// we get the correct total unique results which is 830 Order documents.
    List<Order> pagedResults;

int totalResults = 0;
int totalUniqueResults = 0;
int skippedResults = 0;

int pageNumber = 0;
int pageSize = 50;
                
do
{
    pagedResults = session.Advanced
        .DocumentQuery<Orders_ByProductName.IndexEntry, Orders_ByProductName>()
        .Statistics(out QueryStatistics stats)
        .OfType<Order>()
         // Add the number of skipped results to the "start location"  
        .Skip((pageNumber * pageSize) + skippedResults)
        .Take(pageSize)
        .ToList();
    
    totalResults = stats.TotalResults;
    skippedResults += stats.SkippedResults;
    totalUniqueResults += pagedResults.Count;
    
    pageNumber++;
}
while (pagedResults.Count > 0); // Fetch next results

// When executing the query on the Northwind sample data:
// ======================================================

// The total results reported in the stats is 2155 (totalResults),
// which represent the multiple index-entries generated as defined by the fanout index.

// By adding the skipped results count to the Skip() method,
// we get the correct total unique results which is 830 Order documents.
    // A fanout index - creating MULTIPLE index-entries per document:
// ==============================================================

public class Orders_ByProductName : AbstractIndexCreationTask<Order>
{
    public class IndexEntry
    {
        public string ProductName { get; set; }
    }
    
    public Orders_ByProductName()
    {
        Map = orders => 
            from order in orders
            from line in order.Lines
            select new IndexEntry
            {
                ProductName = line.ProductName
            };
    }
}
    from index "Orders/ByProductName"
limit 0, 50  // First loop will skip 0, take 50, etc.

    related articlesRelated Articles

    Querying

    Indexes

    Home / Docs / Indexes / Querying / Paging