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, optimizing hardware usage, or simply because no user can handle huge amounts of data at once.

If not specified, page size on client side is set to 128.

If not specified, maximum page size on server side is set to 1024 and can be altered using Raven/MaxPageSize setting (more information here).

Example I - Safe by Default

All of the bellow queries will return up to 128 results due to the client default page size value.

• Query
• DocumentQuery
• Commands
• Index

IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Where(x => x.UnitsInStock > 10)
.ToList();

IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.ToList();

QueryResult result = store
.DatabaseCommands
.Query(
	"Products/ByUnitsInStock",
	new IndexQuery
	{
		Query = "UnitsInStock_Range:{Ix10 TO NULL}"
	});

public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select new
					  {
						  UnitsInStock = product.UnitsInStock
					  };
}
}

All of the bellow queries will return up to 1024 results due to the server default max page size value.

• Query
• DocumentQuery
• Commands
• Index

IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Where(x => x.UnitsInStock > 10)
.Take(9999)	// server will decrease this value to 1024
.ToList();

IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.Take(9999)	// server will decrease this value to 1024
.ToList();

QueryResult result = store
.DatabaseCommands
.Query(
	"Products/ByUnitsInStock",
	new IndexQuery
	{
		Query = "UnitsInStock_Range:{Ix10 TO NULL}",
		PageSize = 9999
	});

public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select 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:

• Query
• DocumentQuery
• Commands
• Index

IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Where(x => x.UnitsInStock > 10)
.Skip(20)	// skip 2 pages worth of products
.Take(10)	// take up to 10 products
.ToList();	// execute query

IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.Skip(20)	// skip 2 pages worth of products
.Take(10)	// take up to 10 products
.ToList();	// execute query

QueryResult result = store
.DatabaseCommands
.Query(
	"Products/ByUnitsInStock",
	new IndexQuery
	{
		Query = "UnitsInStock_Range:{Ix10 TO NULL}",
		Start = 20,	// skip 2 pages worth of products
		PageSize = 10	// take up to 10 products
	});

public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select new
					  {
						  UnitsInStock = product.UnitsInStock
					  };
}
}

Finding the Total Results Count When Paging

While paging, you often need to know the exact number of results returned from the query. The Client API supports this explicitly:

• Query
• DocumentQuery
• Commands
• Index

RavenQueryStatistics stats;
IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Statistics(out stats)				// fill query statistics
.Where(x => x.UnitsInStock > 10)
.Skip(20)
.Take(10)
.ToList();

int totalResults = stats.TotalResults;

RavenQueryStatistics stats;
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.Statistics(out stats)					// fill query statistics
.WhereGreaterThan(x => x.UnitsInStock, 10)
.Skip(20)
.Take(10)
.ToList();

int totalResults = stats.TotalResults;

QueryResult result = store
.DatabaseCommands
.Query(
	"Products/ByUnitsInStock",
	new IndexQuery
	{
		Query = "UnitsInStock_Range:{Ix10 TO NULL}",
		Start = 20,
		PageSize = 10
	});

int totalResults = result.TotalResults;

public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select 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 that produce multiple index entries per document (a fanout index). Then TotalResults will contain the total count of matching documents found, but will not take into account results that were skipped as a result of the Distinct operator.

Whenever the 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 the 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:

• Query
• DocumentQuery
• Commands
• Index

RavenQueryStatistics stats;
IList<Product> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
results = session
	.Query<Product, Products_ByUnitsInStock>()
	.Statistics(out stats)
	.Skip((pageNumber * pageSize) + skippedResults)
	.Take(pageSize)
	.Where(x => x.UnitsInStock > 10)
	.Distinct()
	.ToList();

skippedResults += stats.SkippedResults;
pageNumber++;
}
while (results.Count > 0);

RavenQueryStatistics stats;
IList<Product> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
results = session
	.Advanced
	.DocumentQuery<Product, Products_ByUnitsInStock>()
	.Statistics(out stats)
	.Skip((pageNumber * pageSize) + skippedResults)
	.Take(pageSize)
	.WhereGreaterThan(x => x.UnitsInStock, 10)
	.Distinct()
	.ToList();

skippedResults += stats.SkippedResults;
pageNumber++;
}
while (results.Count > 0);

QueryResult result;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
result = store
	.DatabaseCommands
	.Query(
		"Products/ByUnitsInStock",
		new IndexQuery
		{
			Query = "UnitsInStock_Range:{Ix10 TO NULL}",
			Start = (pageNumber * pageSize) + skippedResults,
			PageSize = pageSize,
			IsDistinct = true
		});

skippedResults += result.SkippedResults;
pageNumber++;
}
while (result.Results.Count > 0);

public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
	Map = products => from product in products
					  select new
					  {
						  UnitsInStock = product.UnitsInStock
					  };
}
}

• Query
• DocumentQuery
• Commands
• Index

RavenQueryStatistics stats;
IList<Order> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
results = session
	.Query<Order, Orders_ByOrderLines_ProductName>()
	.Statistics(out stats)
	.Skip((pageNumber * pageSize) + skippedResults)
	.Take(pageSize)
	.ToList();

skippedResults += stats.SkippedResults;
pageNumber++;
}
while (results.Count > 0);

RavenQueryStatistics stats;
IList<Order> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
results = session
	.Advanced
	.DocumentQuery<Order, Orders_ByOrderLines_ProductName>()
	.Statistics(out stats)
	.Skip((pageNumber * pageSize) + skippedResults)
	.Take(pageSize)
	.ToList();

skippedResults += stats.SkippedResults;
pageNumber++;
}
while (results.Count > 0);

QueryResult result;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;

do
{
result = store
	.DatabaseCommands
	.Query(
		"Orders/ByOrderLines/ProductName",
		new IndexQuery
		{
			Start = (pageNumber * pageSize) + skippedResults,
			PageSize = pageSize
		});

skippedResults += result.SkippedResults;
pageNumber++;
}
while (result.Results.Count > 0);

public class Orders_ByOrderLines_ProductName : AbstractIndexCreationTask<Order>
{
public Orders_ByOrderLines_ProductName()
{
	Map = orders => from order in orders
					from line in order.Lines
					select new
					{
						Product = line.ProductName
					};

	MaxIndexOutputsPerDocument = 1024;
}
}

The situation would be different if a Distinct query and a projection applied to stored fields only. Then, to get correct results 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):

• Query
• DocumentQuery
• Commands
• Index

IList<string> results;
int pageNumber = 0;
const int pageSize = 10;

do
{
results = session
	.Query<Orders_ByStoredProductName.Result, Orders_ByStoredProductName>()
	.Select(x => x.Product)
	.Skip((pageNumber * pageSize))
	.Take(pageSize)
	.Distinct()
	.ToList();

pageNumber++;
}
while (results.Count > 0);

IList<Orders_ByStoredProductName.Result> results;
int pageNumber = 0;
const int pageSize = 10;

do
{
results = session
	.Advanced
	.DocumentQuery<Order, Orders_ByStoredProductName>()
	.SelectFields<Orders_ByStoredProductName.Result>("Product")
	.Skip((pageNumber * pageSize))
	.Take(pageSize)
	.Distinct()
	.ToList();

pageNumber++;
}
while (results.Count > 0);

QueryResult result;
int pageNumber = 0;
const int pageSize = 10;

do
{
result = store
	.DatabaseCommands
	.Query(
		"Orders/ByStoredProductName",
		new IndexQuery
		{
			Start = (pageNumber*pageSize),
			PageSize = pageSize,
			IsDistinct = true,
			FieldsToFetch = new[]
			{
				"Product"
			}
		});

pageNumber++;
} while (result.Results.Count > 0);

public class Orders_ByStoredProductName : AbstractIndexCreationTask<Order>
{
public class Result
{
	public string Product { get; set; } 
}

public Orders_ByStoredProductName()
{
	Map = orders => from order in orders
					from line in order.Lines
					select new Result
					{
						Product = line.ProductName
					};

	MaxIndexOutputsPerDocument = 1024;

	Store("Product", FieldStorage.Yes);
}
}

Increasing StartsWith Performance

All StartsWith operations (e.g. LoadStartingWith and Stream from advanced session operations or StartsWith and Stream from low-level commands) contain a RavenPagingInformation parameter that can be used to increase the performance of a StartsWith operation when next page is requested.

To do this, we need to pass the same instance of RavenPagingInformation to the identical operation. The client will use information contained in this object to increase the performance (only if next page is requested).

var pagingInformation = new RavenPagingInformation();
IList<Product> results = session
.Advanced
.LoadStartingWith<Product>(
	"products/",				// all documents starting with 'products/'
	"1*|2*",				// rest of the key must begin with "1" or "2" e.g. products/10, products/25
	0 * 25,					// skip 0 records (page 1)
	25,					// take up to 25
	pagingInformation: pagingInformation);	// fill `RavenPagingInformation` with operation data

results = session
.Advanced
.LoadStartingWith<Product>(
	"products/",				// all documents starting with 'products/'
	"1*|2*",				// rest of the key must begin with "1" or "2" e.g. products/10, products/25
	1 * 25,					// skip 25 records (page 2)
	25,					// take up to 25
	pagingInformation: pagingInformation);	// since this is a next page to 'page 1' and we are passing 'RavenPagingInformation' that was filled during 'page 1' retrieval, rapid pagination will take place