Antti Nivala wrote:

> I'm sorry but I don't understand what you mean. Are you saying that adding an ORDER BY clause would make the query run faster? I don't understand how, and I did not get it any faster by adding "ORDER BY PV ". It still takes 400 ms to return just one record. Can you give me more details if the ORDER BY clause can really help speed up the query.

Paul is saying that FIRST 1 would normally need to know an order to work
and produce a reliable result. You ARE looking for all records, and then
applying the 'filter' of just returning the first one - FIRST does not
reduce processing, but will give a faster result if you later call SKIP
x on a second query since the data is normally cached. So people want
different things from the same function ;)

> My point is that I want to make a QUICK test for existence of any records where PV = 2.
> When I use "NOT EXISTS ( SELECT * FROM DV_PV WHERE PV = 2 )" I don't understand why it takes much longer for values with duplicates and almost nothing for values with a small number of duplicates. It appears to me that the millions of duplicate values for the "PV = 2" key in the index make the EXISTS check so slow, but I do not understand why. I assumed FB would just visit the index and see that there are values for "PV = 2" and that's it. Why would it read all the duplicates from the index before concluding that at least one value exists?

Again you are asking for all records. And '*' asks for all fields. Have
you tried just looking for one field?

> The same slowness appears in the "SELECT FIRST 1 * FROM DV_PV WHERE PV = 2" example.

> How can I make a quick test for existence in this case?

Personally I store a count in a separate table that has details of each
value of things like 'PV_TYPE'. And a trigger updates that table when a
record is added or deleted. That way I know how many 'PV = 2' records
exist without having to 'COUNT' - but this may not be practical for you.
If the 'PV_TYPE' table does not have an entry then I either flag an
error, or request further information to create one.

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php