Paging Support to Handle Large Query Results
[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]
Queries sometimes return large number of entities. You can optionally retrieve this large result set in smaller chunks called pages. The Take()
operator can be optionally added to the query to specify the page size, the maximum number of entities to return per page. If Take()
operator is not specified in the query, the default page size is 500 entities.
When retrieving large query results in smaller units it is important to be aware whether query results are sorted by a unique or a non-unique property so you can formulate your query appropriately.
Note
By default all query results are ordered by the Unicode binary collation of Entity.Id
. You can change the default order by specifying the orderby
clause in the query.
This topic explores the following paging scenarios:
- Query result sorted by unique property
- Query result sorted by a non-unique property
- Specifying page size using the Take() operator
Paging when the query result is ordered by a unique property
The following code fetches all of the results for an arbitrarily large result set, one page at a time. Note that the query does not specify orderby
and therefore the results are sorted by Id
metadata.
using (SitkaSoapServiceClient proxy = new SitkaSoapServiceClient())
{
Scope myContainerScope = new Scope() {
AuthorityId = authorityId,
ContainerId = containerId};
string lastId = "";
string query = @"from e in entities
where e.Id > ""{0}""
select e";
while (true)
{
int count = 0;
foreach (Entity entity in proxy.Query(
myContainerScope,
String.Format(query, lastId)))
{
// Process entities
lastId = entity.Id;
count++;
}
if (count < 500)
break; // last page if less than 500 results
}
}
In this coding pattern note that:
- The query in the first
while
loop iteration returns the first 500 entities (first page). The loop tracks theId
value of the last entity (lastId
). This value is used in constructing the query for the second iteration. - The query in the second iterations of the
while
loop, skips the first 500 entities (based on thewhere
clause) and returns next set of entities whereId
value is greater than thelastId
value. - The
while
loop continues as long as the query returns 500 entities. Eventually the loop terminates when query returns a page with less than 500 entities signaling no more entities to retrieve.
Paging when the query result is ordered by a non-unique property
In the previous example the query result was sorted by the unique Id values. The query in this example specifies ordering by a non-unique (Author) flexible property. The query retrieves all the Book entities sorted by Author property. An author may write multiple books and therefore it is a non-unique flexible property. A sample result set where entities are sorted by Author is shown in this illustration:
In the sample result the second page has same Author value as the last entity of the previous page. In such cases, the coding pattern similar as in example 1 will not work:
string lastAuthor = "";
string query = @"from e in entities
where e.Author > ""{0}""
orderby e["Author"]
select e";
while (true)
{
int count = 0;
foreach (Entity entity in proxy.Query(
myContainerScope,
String.Format(query, lastAuthor)))
{
// Process entities
lastAuthor = entity.Author;
count++;
}
if (count < 500)
break; // last page if less than 500 results
}
Note that
- The query in the first
while
loop iteration returns first 500 Books. The loop tracks the last author value as "Author X". - The query in the second iteration returns next page skipping all entities where
e.Author > "Author X"
and thus skipping the two "Author X" entities that were not returned in the first page.
To address this issue you first sort the result by Author and then by unique property (Id
metadata is used in the following illustration).
The where
clause in the query must also ensure subsequent pages don't skip any entities. The updated coding pattern is shown below:
string lastAuthor = "";
string lastId = "";
string query = @"from e in entities
where (e[""Author""] = ""{0}"" && e.Id > ""{1}"")
||
e[""Author""] > ""{0}""
orderby e["Author"], e.Id
select e"
while (true)
{
int count = 0;
foreach (Entity entity in proxy.Query(
myContainerScope,
String.Format(query, lastAuthor, lastId)))
{
// Process entities
lastAuthor = entity.Author;
lastId = entity.Id;
count++;
}
if (count < 500)
break; // last page if less than 500 results
}
The condition in the where
clause returns the entity if
- The Author property value is greater than the lastAuthor value.
- The Author property value is same as the lastAuthor; however the
id
value is greater than thelastId
value.
Specifying page size using the Take() operator
You may optionally specify the page size by adding the Take()
operator in the query. If you specify a value larger than 500, the page size of 500 is assumed. That is, the maximum page size allowed is 500. For example the following query expression specifies the page size of 10.
(from e in entities orderby e["Author"], e.Id select e).Take(10)
- The query expression with
Take()
can also be specified as:
Take(from e in entities orderby e["Author"], e.Id select e, 10)
The Take() operator in these examples acts similar to the top operator in SQL select queries.
See Also
Concepts
Querying SQL Data Services
Examples of Using SOAP and REST Interfaces with the SQL Data Services
SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities)