see on GitHub

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 just because no user can handle huge amounts of data at once anyway.

Safe By Default

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

Safe By Default

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.

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.

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 3rd page. To do this we need to issue following query:

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 sometimes need to know the exact number of results returned from the query. The Client API supports this explicitly:

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, server will skip over some results internally, and by that invalidate the TotalResults value e.g. when executing a Distinct query or index produces 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 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. In other words, for each page the starting point should be .Skip((currentPage * pageSize) + SkippedResults).

For example, let's page through all the results:

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
						  };
	}
}

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

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