Subject Re: [firebird-support] Re: how do i speed this up?
Author ian
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 =
> 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 =
> 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 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??


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.