Paging 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 toint.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.
Performance Hint
Paging through tampered results
-
The
QueryStatistics
object contains theTotalResults
property,
which represents the total number of matching documents found in the query results. -
The
QueryStatistics
object also contains theSkippedResults
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:
-
When making a Projection query with Distinct.
-
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 theSkippedResults
value when specifying the number of documents to skip for each page using:
(currentPage * pageSize) + SkippedResults
.
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.