I want to understand how the pagination works along with RowLimit while fetching items from an SPList using SPQuery. I had a list which has 71 items in it. I set the RowLimit to 5 and I can see in my profiler that it did 15 calls (to VXMlListQuery) to fetch the items. I suppose this is expected because ceil(71/5) = 15.
So, when is the RowLimit applied i.e. is the same query getting executed 15 times and the GetItems returns the required items as per the position from the result set? Or the whole SQL query itself gets executed each time with the RowLimit values specified in the input.
If former, using RowLimit is saving the SQL network traffic which is flowing out but not the performance. Is that correct?