OK then, Martin.

Try something like

CREATE PROCEDURE get_next_20_dicentries_by_asterm(
astermparam varchar(80); idparam integer) returning idreturn integer
as
declare variable currentasterm varchar(80);
declare variable currentid integer;
declare variable count integer;
begin
count = 0;
select min(asterm) from dicentries
where asterm > :astermparam
or (asterm = :astermparam
and id > :idparam) into :currentasterm;
if (currentasterm > astermparam) then
currentid = 0 //This ascertains that id > 0
else
currentid = idparam;
while (count < 20) do
begin
for select id from dicentries
where (asterm = :currentasterm
and id > :currentid)
order by id
into :idreturn do
begin
count = count+1;
if (count < 20) then
suspend
else
exit;
end
select min(asterm) from dicentries
where asterm > :currentasterm
into :currentasterm;
currentid = 0;
end
end

Now, this stored procedure is written in Thunderbird and will not work
if you try to prepare it (I've written a very limited number of stored
procedures in my life). So your first task will be to remove the syntax
errors.

Once that is done, with two indexes - one on asterm and hopefully a
primary key on id, my guess is that it could well help you out of your
performance problems. That is, unless you have lots of duplicate asterm
values (like 80 percent share five values).

Now, I never understood what you meant by GetCollateKey, but I hope my
suggestion still helps a bit?

Tell us the results, I'm curious. If it still is a problem, please
prepare all statements individually and report back the plan (and tell
us how selective asterm is).

HTH,
Set

martinknappe wrote:

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

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