Thanks for the link. That is the reason that I say that this should be done server side - diffrent SQL serveres - diffrent behaviours, diffrent optimizations.

Regards

Tony

I'll provide a working example soon as I've finalized it.

EDIT: What I am saying is that whilst you are getting i.e. 10 records back, the SQL engine has to actually consider rows * page records in order to provide you with these 10 records.

Unless you say “give me 10 records back fullfilling my order by criteria, starting from a row which fullfills the criteria for the first row in the page that I have found and stored to help optimize this query”.

Also when I say "table" I mean a recordset returned to the application-server, which it can use to query the database, if the filter and (multicolumn) order hasn't changed.

To continue with this

$limit = $_REQUEST['rows']; // the limit of rows

$start = $limit*$page - $limit; // the page from previous post

$SQL = "SELECT ... LIMIT ".$start." , ".$limit; 😉  - not a milion here

Hope that MSSQL have such feature

Regards

I'm talking about fetching only the n records from page m, from the database-server, as quickly from page 1 as from page 10 million.

I'll explain better what I mean later.

EDIT: I had a look at it, and although MySQL has a LIMIT-clause, there is nothing which indicates to me that it doesn't scan the rows.

As described here: http://www.mysqlperformanceblo.....imization/

"Also note - number of records scanned to provide result set will be very dynamic based on particular constant and other factors.
For example for our dating example if we use only (last_online) index and look for people from USA we likely will find 10 people pretty quickly, if the country is small or simply there are few members from the country, ie Slovenia - same kind of search might need to scan 1000s times more rows to provide result set."

My work-around is to create a table holding the order-fields-for-first-row-in-each-page, and use that together with indexes.

Indexes do not alone provide an optimal solution in the case of pagination.

I do not think that it is so complicated. Look at demo server.php (PHP variant)

Here I do

$page = $_REQUEST['page']; // get the requested page

$limit = $_REQUEST['rows']; // get how many rows we want to have into the grid

then get the total records

$result = mysql_query("HERE THE SQL");

$row = mysql_fetch_array($result,MYSQL_ASSOC); // fetch data

$count = $row['count']; // at end the count

 if( $count >0 ) {
         $total_pages = ceil($count/$limit); // how many total pages we should
 } else {
        $total_pages = 0;
}

And the simple logic

if ($page > $total_pages) $page=$total_pages; // HERE WE ARE

Regards

Tony

To do actual  pagination on the database-level, as opposed to table-scan, you have to do some acrobatics.

For SQL Server:

The “PAGE” returned from the server, needs to be adjusted to:

total = npages
, page = (npages >= arguments.page ? arguments.page : npages)

Also the query on the server needs to be adjusted, to either pick rows in the range of the page and rows requested, or the number of rows requested.

I'll have to think abit on how to make the rows queried for, actually line up with the pages for the requested number of rows per page, whilst keeping the query as optimal as possible.

Thanks for your help.

Will see what I can do, but I think that this logic should be implemented at server.

Regards

Tony

Example:

With records = 143.

nPage = 1, nPerPage = 50, shows rows 1-50 (correct)

nPage = 3, nPerPage = 50, shows rows 101-143 (correct)

nPage = 3, nPerPage = 100, shows no rows (technically correct, usably incorrect)

What should have happened, IMHO:

1. When the data fetched shows that there are no rows for the requested nPage and nPerPage

, AND

2. records shows that there are rows that can be fit into a previous page

, the component should requery with either:

A) The lowest page number that ALL the records can fit into

, OR

B) The highest (next) page that can hold at least one row

You're doing a stellar job with this component, btw, and I'll nag my employer for a contribution to your continous efforts, and try and contribute with a Coldfusion custom-tag.