martinknappe wrote:

> Now, coming back to my problem:
>
> In a normal situation, the user of my application would repeatedly
> send the same sql request to the server, only with changed parameters;
> the request could be something like the following:
>
>
> select * from dicentries d
> inner join get_next_20_dicentries_by_asterm('a', 25) g
> on d.id = g.id
>
>
> get_next_20_dicentries_by_asterm('a', 25) would simply return the
> field ID from table DICENTRIES for the first 20 records "where asterm
>> = 'a' and ID >= 25" and "order(ed) by asterm ascending, id ascending)"
> suppose the last one of these 20 records has ASTERM = 'ZOO', ID = 130,
> and the user is scrolling forward, not backward, then the next query
> would simply be:
>
>
> select * from dicentries d
> inner join get_next_20_dicentries_by_asterm('ZOO', 130) g
> on d.id = g.id
>
>
> and so on and so forth...
>
> now, every time i request 20 new records, the whole table DICENTRIES
> is sorted "by asterm ascending, id ascending" before returning just
> the first 20 of this big and sorted result set..is there no way to
> realize for the server that a received query is the exact same query
> as the one before but with changed parameters so that the result set
> from the query before could be reused??
>

Hi,

If the database cannot do this task any faster, try changing the task it
has to do. If your data does not change often, or you can get away with
a daily version of your dictionary, you might try creating an
alternative table with the data ordered correctly, "by asterm ascending,
id ascending"; the old clustered index scenario that Firebird does not
seem to support.

insert into lookup_table
select * from data_table
order by asterm ascending, id ascending

In theory then, the database can use the parameters: ('a', 25) to go to
the first record required and just pull up the next 20. Definitely "BDE"
thinking, but it can work and I am sure someone on the list will respond
with some experience in Firebird, even just to tell us it does not work.

regards
ian