--- In firebird-support@yahoogroups.com, Milan Tomeš - Position <tomes@...> wrote:

>
> Just my 2 cents - you have to know the total count only when you're
> asking for last page. If there is next page you can obtain through query
> like SELECT * from table rows ((page_no - 1) * recs_per_page) + 1 to
> (page_no * recs_per_page) + 1 and test number of records returned (if
> number of records returned is higher than recs_per_page there IS a next
> page). That's a trick we're using in our apps. And for last page we're
> using this: If there is a prediction of many records returned by query
> we're not allowing this feature. Only way to allow it is implementation
> of filters required to fill by user.

I like this approach. You are right - the only time I need to know the total number of rows returned from a query is when the user wants to go to the last page. So I really only need to do the total count to support that one option. From looking at the general use case on the grids, I'd say that only about 10-15% of the time would a user use that option - most of the time they set their search filters, and have it refresh from page 1. If they don't see what they are looking for, they will either click NEXT to go to the next page, or change the sort order by clicking on a column header, or change the entire search query.

They'd use LAST page if they sorted incorrectly, but 75% of the time they would probably just re-click on the column header to resort the results the way they wanted. Your approach, in all cases, is totally valid - I don't need a total count unless they want to click the LAST page button.

The only thing that would possibly be an issue here is that the Flexigrid JS component displays the total number of rows that they *can* navigate through. I think my users might have issue if I remove that, but its possible I could replace it with a cached value.

> And one opinion at the end - can't you use something like this:
>
> CREATE TABLE test (
> ROW_NO bigint
> )
> CREATE DESCENDING INDEX IX_TEST_ROWNO ON TEST (ROW_NO);
>
> and maintain ROW_NO as a continuous (it means without spaces) and use
> max() instead of count()? Max() can use that descending index so it will
> be really fast, but there can be a problem to maintain that ROW_NO values.

Yes, but it assumes that the user only ever gets the entire content of a table in their search. My problem is that they get the results of the criteria that they filter on.

I put a screenshot of how this is implemented to make it clearer at:

http://files.techsolusa.com/temp/screenshot_019.jpg

The user can construct their search at the top of the page, can filter those results if they wish, and then navigates at the bottom of the grid. I do store the user's last search query in a session table persistently, so I can return them to where they were last time. I certainly could store the total row count there as well, which wouldn't take into consideration changes in the dataset since they last searched, but maybe a 'timeout' on that number might work (ie. if it has been more than 5 minutes since their last search, clear to the total count and force it to be refreshed if the search criteria has changed).

Myles