--- In firebird-support@yahoogroups.com, "martinknappe" wrote:
>
> > Hi Martin!
>
> Hi Set :-)
> > you may be able to speed up your query a bit by simplifying and
> > changing to
> >
> > select first 1 id from dicentries
> > where asterm is not null and id+0 > 0
> > /*I think '' is less than any other value, so no need for OR*/
> > order by asterm, id
>
> that speeds it up *slightly* (by like 1 sec *lol)

Well, I didn't expect the speed gain to be too impressive ;o)

> > Though I think that to make it quick, you better split your
> > request into two separate requests:
> >
> > select min(asterm) from dicentries
> > where asterm is not null and id+0 > 0
>
> It seems like min works only on numbers -> this query returns min =
> null!!!

No, you've explicitly said that nulls shouldn't be returned. I'm
pretty certain this works for strings (although '10' is less than '2')
as well as numbers. Though blank or space is smaller than most other
values, so '' would be sorted before 'a' and '0'.

> The problem here is I have a terminology database application and
> now I need to write a client application that requests the entries
> from the database in a sequential manner and outputs them in a
> dictionary-like style into an RTF file. The user needs to have the
> possibility to configure the sorting of the entries in the rtf file
> (i.e. be it by source language, target target language, and more),
> so simply defaulting to the primary key is not an option.
>
> On the other hand, of course, the client application cannot request
> the whole result set all at once because it wouldnt fit into
> memory.. 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

Often (although 'often' doesn't mean 'always'), Firebird needs the
whole result set to be able to sort the dataset so that it can
determine which row is the FIRST (at least if the PLAN says NATURAL,
which I suspect it does in your case). Admittedly, that is required on
the server and it isn't neccessary to transfer all the data to the
client, but still... If it doesn't fit in memory on the server, it
will be put into temporary files on the disk. Does that explain the 7
seconds that is the record so far?

> 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

One of the main differences between desktop and client/server
databases, is that where the desktop database thinks in tables, the
client/server database thinks in datasets. Client/server databases
like Firebird allows for huge tables, but that require you to take
care of your datasets and to make things quick, your dataset must be
small.

SELECT FIRST combined with a 'minimal impact where clause' isn't a
good way to get a small dataset, because the FIRST bit normally is
applied after getting and sorting the entire potential result set, in
your case all with a non-null asterm and a positive id value. I don't
know of other ways to get this quick, than to add a WHERE clause that
significantly reduces your dataset.

If your program output things to a file that will later be used as
kind of a user manual, then 7 seconds isn't long - imagine the time
the user needs to read through all those 200 000 entries. If it is
more something ad-hoc, and you try to let the database sort, then the
user look through things, well, then you have the old BDE way of
thinking that simply doesn't work well with Firebird. Change the way
of thinking, so that the user specifies what he is looking for in one
way or the other. With a small result set (excluding FIRST) and
appropriate indexing, my experience is that Firebird is quick.

HTH,
Set