paginationdynamics-crmfetchxmlquery-expressions

When to specify PagingInfo.PagingCookie and PagingInfo.PageNumber


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="&lt;cookie page=&quot;1&quot;&gt;&lt;new_parentrecordid last=&quot;{F8DAB1AA-3A0F-E411-8189-005056B20097}&quot; first=&quot;{F8DAB1AA-3A0F-E411-8189-005056B20097}&quot; /&gt;&lt;/cookie&gt;" 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.


Solution

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

    ss1

    The ID's in the paging cookie stay the same:

    ss1

    With page #:

    ss3

    The ID's change:

    ss4

    It is also interesting to note that if you advance the page # without changing the page or ID's in the paging cookie,

    ss5

    The system uses the paging cookie's last ID and expands the SQL "top" parameter to get the number of pages you requested:

    ss6

    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:

    ss7