Though reading lots of available resources online, I couldn't find a straight explanation that clarifies:
In what case both the values of PageingInfo.PageNumber
and PageingInfo.PagingCookie
are required to be assigned?
For example: Consider we have 10,000 unique records, and we'd like to retrieve all of them, but 200 records at a time. Do we have to iterate over PageNumber
(50 iterations), or is it enough to use only the PagingCookie
?
Now, I'd like to share what I've found on online resources:
Firstly, many online resources links to the official MSDN examples (For FetchXML: 1, 2, for QueryExpression: 1, 2), but there isn't a direct answer for this issue. They all iterate over the PageNumber
, but as I understand (and it may be wrong), a Page always has 5000 records (Or is it?).
Secondly, I've found this demonstration of using PagingCookie
, fetching records 6-10 out of 40 records:
<fetch mapping="logical" count="5" page="2" paging-cookie="<cookie page="1"><new_parentrecordid last="{F8DAB1AA-3A0F-E411-8189-005056B20097}" first="{F8DAB1AA-3A0F-E411-8189-005056B20097}" /></cookie>" version="1.0">
<entity name="new_parentrecord">
<attribute name="new_name" />
<link-entity name="new_childrecord" from="new_parentaid" to="new_parentrecordid">
<attribute name="new_childrecordid" />
<attribute name="new_name" />
</link-entity>
</entity>
</fetch>
Then, it's explained that the above is translated to the following SQL query:
select top 6 "new_parentrecord0".new_name as "new_name"
, "new_parentrecord0".new_parentrecordId as "new_parentrecordid"
, "new_childrecord1".new_childrecordId as "new_childrecord1.new_childrecordid"
, "new_childrecord1".new_name as "new_childrecord1.new_name"
from
new_parentrecord as "new_parentrecord0"
join new_childrecord as "new_childrecord1" on ("new_parentrecord0".new_parentrecordId = "new_childrecord1".new_ParentAId)
where
((("new_parentrecord0".new_parentrecordId > '01DBB1AA-3A0F-E411-8189-005056B20097')))
order by
"new_parentrecord0".new_parentrecordId asc
Thus, as I see it in this example, there is no need to use the page number, since only the paging cookie is being used in resulted the SQL query.
Would be great to have a nice clarification for this issue.
My experience and additional research I just did indicates that yes, we do need to iterate over all the pages. While the page number never makes it into the SQL query, it controls which ID SQL will use in its filter.
Without page # in the query:
The ID's in the paging cookie stay the same:
With page #:
The ID's change:
It is also interesting to note that if you advance the page # without changing the page or ID's in the paging cookie,
The system uses the paging cookie's last ID and expands the SQL "top" parameter to get the number of pages you requested:
And it must do some processing on the SQL recordset because it returns the right number of records (in this case 20), with the ID's advanced: