Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

I assume you're talking about client-side cursors...if they weren't so darned slow, then I guess it would be viable. If a server side cursor is used, then a connection to the database is held, which would effect scalability (too many simultaneous connections, etc, effects performance on the database server). The security you refer to indicates a pessimistic locking strategy, because you require guarantees.

If it is a client cursor, which involves disconnecting from the database, then the entire contents of the resultset are transfered to the client for manipulation, which causes a lot of unneccessary traffic, especially if a paging strategy was good enough. This is also an optimistic strategy, because someone could change a record in the database while you were scrolling through the records in your resultset on the client.

(1) I'm not sure what you mean by "rows across pages". Can you please explain this.
(2) M$ already handles externally linked tables, which I am also currently using. Provided the linked table also has a unique row identifier then I can sort by that and so perform paging (perhaps having a unique row identifier would also need to be a requirement of a proposed standard)
(3) Could you please explain a scenario where the db would do an online reorg on-the-fly. Changing ids doesn't seem normal to me? Anyway, I would have thought that was something that you would schedule, etc. We use Guid's to identify each row, and so sorting by that ID would always guarantee a particular order, even if the contents of particular pages changes.
(4) The paging solution is really part of an optimistic strategy. If someone deletes a record that exists on the page, the strategy would be designed to handle it. For example, we generate all our IDs on the client, and we know whether an object is new, or an updated object (retrieved from the database). We also have a timestamp, so that we can tell if someone else has modified the record, and have a strategy for handling this. If we hold an object that was retrieved from the database, and when an attempt is made to write that object back to the disk, the object is found to no longer exist, then the strategy would also handle this (ie, why are they updating an object that someone else deleted? Should this user be given the option to re-insert the object in this case?).

As for exposing the physical layout of the data, well, I guess to do paging, there would always be a need to order the data to ensure it was returned in a consistent fashion. I personally don't see a problem with this, as my strategy handles this well enough for our circumstances.

I am not convinced that anyone who implements a paging strategy would need the so called security that a scrollable cursor might provide. Most people implementing a paging strategy know that this is an optimistic stratgey, but they can live with it.

And I stand by my suggestion, that this somehow become part of the standard, as there appears to be enough of us that want this to warrant it (as the MySql implementation demonstrates). Should we really be suggesting that it not be part of a standard, and let M$ implement their own custom solution? That seems to be half the problem - if you don't make it standard then those who only implement standards will be playing catchup again, with products that cater for what the developer wants...

Serge Rielau <srielau_at_ca.ibm.com> wrote in message news:<3D982CD3.1050400_at_ca.ibm.com>...
> The reason that nothing along these lines is in the standard is that
> this page() function you propose exposes the physical layout of data on
> the disk to the application.
> SQL is all about not doing that.
> From a more pragmatic angle dealing with pagenumbers and rownumbers can
> cause quite some confusion:
> 1) What if your favorite DBMS (starts to) support rows crossing pages
> 2) What if your row is stores outside of the DBMS (external table,
> nickname, ....)
> 3) What if your DBMS wants to support online reorg... Yoru rowids will
> chanhe in mid flight
> 4) What if someone deletes a record in the page you're on..
>
> The way to walk through resultsets securly are scrollable cursors.
>
> Cheers
> Serge
Received on Mon Sep 30 2002 - 21:28:17 CDT