Confused by startRow, endRow and totalRows?

[magnus]

I spent some trying to figure out how these work and I think I've succeeded now. I spent a lot of time trawling through the forums and documentation but found it difficult to find out exactly how they worked. Now that I have, I thought I'd post my findings here for others to benefit. Hopefully isomorphic can correct any mistakes I've made here!

In this scenario, I'm referring to using a Grid and databound listbox.

Firstly the request side. startRow and endRow are both zero based, so if SmartClient wants ten records, starting from the beginning of the rows, it will pass startRow=0 and endRow=9 to the server. SmartCient uses the maximum number of rows that the grid can currently display to the user and the value of the dataPageSize property to determine exactly how many rows it will request.

On the server side, you firstly need to find all of the records that match the request, initially ignoring the startRow and endRow values. You need to take into account any filter and sort values supplied by SmartClient in the request. The total number of rows that match the request then becomes the value of the totalRows variable that you send back to the client. You then use the startRow and endRow request variables to determine which of the total rows you found need to be returned.

For small datasets, SmartClient may request endRow larger than the number of rows available, especially on the first request when it doesn't currently know the value of totalRows. In this case the value of endRow that you return will be less than the value that was requested. You need to check this on every request and adjust the value of endRow if required. I use something like this on the server to do this:

startRow = request.startRow();
endRow = Math.min(request.endRow(), records.size() - 1)
<startRow> + startRow + </startRow>
<endRow> + endRow + </endRow>
<totalRows>" +  records.size() + </totalRows>

So lets say you have 35 rows in your database that will match the filter values supplied, the Grid can display 7 records to the user and dataPageSize is 15.

When SmartClient first fetches data, it will request startRow=0 and endRow=15. The server should return totalRows=35, startRow=0, endRow=15 and the first 16 records in the database. The scroll box "thumb" will be correctly sized on the user interface so that it is 7/35ths the height. As the user moves the scrollbar, SmartClient will retrieve the next set of data that it needs by requesting different values of startRow and endRow. As it keeps requesting further data from the server, it will eventually determine that (based on totalRows) all data has been retrieved and it doesn't need to go back to the server again.

This is a really neat feature and seems to work really well.

[Isomorphic]

Nice summary, thanks for posting that.

Note that there is a complete sample code for implementing paging in Java under Examples -> Java Integration (starting from the SDK home page).

Also, regarding fetching all matching records in advance, note the following from the ResultSet documentation:

Paging and total dataset length

When using data paging, the server communicates the total number of records that match the current search criteria by setting DSResponse.totalRows. The ResultSet will then return this number from getLength(), and ListGrids and other components will show a scrollbar that allows the user to jump to the end of the dataset directly.

However, the ResultSet does not require that the server calculate the true length of the dataset, which can be costly for an extremely large, searchable dataset. Instead, the server may simply advertise a totalRows value that is one page larger than the last row loaded. This results in a UI sometimes called "progressive loading", where the user may load more rows by scrolling past the end of the currently loaded rows, but is not allowed to skip to the end of the dataset.

No client-side settings are required to enable this mode - it is entirely server-driven. However, it is usually coupled with disabling sorting, since server-side sorting would also force the server to traverse the entire dataset.