Hi again Martin.

It sounds wise to stick to Firebird 1.5 when you've come a long way and
want to finish shortly.

As far as I can see, there is one big problem - and that's a problem
that may be difficult to solve without redesigning what you're doing:
SELECT against a table without a limiting WHERE clause is bad, and it
gets more and more problematic as the tables grow. I don't think FIRST
is supposed to be a replacement for WHERE, it is supposed to be an
addition. I think this may be a general client/server issue, but my
experience is limited to Firebird and I cannot tell for certain.

It should be no problem if there are only a few rows, but with millions,
people get bored by waiting.

I don't understand how the optimizer works in cases like yours (SELECT
FIRST ... ORDER BY ... with a where clause that isn't selective at all).
My first thought is that it is treating Firebird like a desktop database
and that it cannot work decently (Firebird is brilliant for
client/server use, lousy if treated like Paradox). However, the
optimizer expert on this list is Arno Brinkman and unlike me, he can
probably tell you why it will not work or how to make it work.

The only advice I can give, is to add a fairly restrictive WHERE clause
and then simply relax a bit at a time if it gets too restrictive (don't
remove the FIRST 20, just add to the WHERE clause). Admittedly, it is a
workaround that doesn't sound like a nice design, but as deadlines draw
nearer, it will look more appealing...

Sorry,
Set

martinknappe wrote:

> I do believe you in that it's *theoretically* easier to do all these
> things with fb2 but there are 2 reasons I'm wary about fb2:
>
> 1) I have given fb2 a try already and the first thing that ocurred was
> a problem with events not being posted correctly, so I signed up to
> firebird-devel and posted my problem and it came out to be a bug in
> the server..it's probably fixed by now, but it made me wary...
>
> 2) I think there's a big difference between writing applications USING
> fb and being really into this whole developing community thing..maybe
> fb2 intl architecture is easier, again, *theoretically*, but with
> fb1.5 I have david schnepper's tutorial on how to write my own
> collation which i simply have to follow and it works..with fb2 - it
> seems - all i have is the source code and no idea about how to change
> which files and how to compile them if i want to write my own
> collation..if you're not so much of an experienced programmer you look
> at the source code and say "Ooookayy"
>
> I'm writing this application for my end-of-studies project (as I've
> said earlier in this forum) and I've been working on it for the whole
> year already and I really do need and want to finish by the end of the
> year and there are so many little things that need to be done that I
> really don't think I can risk trying fb2 at this point in time..I've
> done too much going back and forth already, I can't afford to waste
> any more time..
>
> 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??
>
> thanx,
>
> martin