> ive seen this same kind of application done with the bde where you
> were also able to switch indices as you please and it was way
> faster..bde is pretty old so i cant really believe that firebird
> shouldn't be able to do it equally fast
> admittedly, the bde database didnt contain several different tables
> referring to each other like this one does, but as you can see, this
> very query requires no searching in any other table so there should be
> a quicker way to find the first entry that meets the condition
> i dont have *too* much of an idea of sql i must admit so i
> suppose/hope there's still some better way to formulate my query

Martin,

Your original query seems to me to me an overcomplicated way of
running this query:

select
first 1
id
from dicentries
where (asterm >= '' and id > 0)
order by asterm ascending, id ascending

I took a quick glance at your script, but could not find a joint index
on asterm, id. Perhaps the index on asterm alone is not selective
enough, and using an index to determine whether ID>0 when it is
probably coming from a generator (ie all records have to meet that
criteria anyway) seems to me to be redundant.

So if that assumption is true, the query is even simpler.

select
first 1
id
from dicentries
where (asterm >= '')
order by asterm ascending, id ascending

If you have an index on dicentries (asterm, id), it should perform better.

You should also be aware that Firebird is very much not like BDE
(thankfully), and does not automatically ship the entire result to the
client. Most components only pull back what is visible on the screen
at a time, so you may be reinventing a wheel here. But it should also
be noted that there are some things that BDE will always be faster at,
well at least until you try and get more than one person to use it
simultaneously. It is acceptable in some cases and as designed for a
desktop database interface, but certainly doesn't scale well to client
server.

Adam