Subject Re: [Firebird-Architect] Re: Bi-directional indexes
Author Alexandre Benson Smith
Roman Rokytskyy wrote:

>Even if it is possible, it is too non-standard and at least for Java
>people that will be no so easy to grok, why this thing is needed for a
>decent speed.
Hi Roman,

Just to avoid making the result set at every request for the next
records, teh REsult Set will be generated at the first query and at
next's execuctions just reuse the already filtered/ordered result set.
just fetching new records as requested.

In the today environment it will be the equivalent of it:

Create TempCustomers (Id integer, Name varchar(40));

insert into TempCustomers select Id, Name from Customers order by Name
Desc order by Name;

and then

for the browse part of the records issue this:
select first 20 skip 0 * from TempCustomers
select first 20 skip 20 * from TempCustomers
select first 20 skip 40 * from TempCustomers

The TempCustomers will have the already filtered and ordered records, I
know without an explicity order by clause no order are guaranteed, but
if the engine could keep a result set to be fetched in parts the order
will be garanteed.

Don't know how close it will be to a Cursor, just used Cursors inside
MSSQL procedures and don't know how it's according to SQL spec. I used
the MSSQL Cursors as the For Select Into type of statement in FB.

Maybe I am just saying what it's already accomplished by cursors,
something like this:

declare cursor X for Select ID, Name from Customers order by Name Desc;
Fetch next 20 X to :MyCursor
Fetch next 20 X to :MyCursor
release cursor X

The idea is to reuse the computed result set for the next partial
fetches by the client.

see you !


Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005