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.

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 the Id 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 the where clause) and returns next set of entities where Id value is greater than the lastId 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 the lastId 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)