Inside FetchXML pt 8 – page, count & top

By default you’ll get up to 5,000 records that match your query. The top-level <fetch> element supports a few options to help you control this:

top will limit the number of records further, e.g.

<fetch top="10">
  <entity name="account">
    <all-attributes />
  </entity>
</fetch>

When you use top to limit the number of records, you don’t have the option to move on to subsequent pages. You can’t use this to increase the number of records beyond 5,000 – you can only use a number between 1 and 5,000.

In contrast, count and page allow you to move through multiple pages of results and control the size of each page:

<fetch count="100" page="3">
  <entity name="account">
    <all-attributes />
    <order attribute="createdon" />
  </entity>
</fetch>

This example will get records 201 – 300. If you are working you way through sequential pages you should also set the paging-cookie attribute with the value of the paging cookie from the previous page.

So when should you use top and when should you use count and page?

If you are only ever interested in the first n records (e.g. you just want the first record that matches your query), use top as you have no need to retrieve multiple result pages.

On the other hand, if you do need to retrieve all the results (or at least, more than “just” the first 5,000), use page to indicate which number page you want. In addition you can optionally use count to control the size of each page – you might set this after some trial-and-error testing of the performance of your query.

SQL Equivalents

In SQL terms, top is equivalent to the SELECT TOP (n) clause. count and page are similar to the OFFSET ... FETCH ... clause.

Leave a comment

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.