I have an idea how I could make my select procedure more selective but
I don't know whether it's technically possible with firebird; if any
of you knows, please tell me. The idea is to rewrite this procedure
but also this would make it necessary to obtain the collation key of a
given varchar object inside the procedure (pseudo-code):

variables: i = 0 (integer), key (varchar(80)), set1 (table), set2 (table)

1) the procedure would accept asterm, id as parameters
2) it would then get the collation key of asterm via a function call
like for example (you please tell me whether any such thing exists):
key = GetCollateKey(asterm); key being a one-byte-character string
3) it would then "select id from dicentries where
GetCollateKey(asterm) = key" into a result set (set1)
4) next, it would "select id from dicentries d inner join set1 s on
s.id = d.id" into result set (set2)
5) then it would do something like this:
"for select id from set2 order by id do
begin
suspend;
i = i + 1;
if i = 20 then goto 8;
end
6) key = key + 1
7) goto 3
8) end of procedure

please, if any of you can help me (even if it is to tell me this is
impossible), please do answer!

thanx,

martin

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> 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
>