Hi

If you SELECT FIRST 1 from a table I think you should ORDER BY 'field' to
know what criteria is for that first record !



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Antti Nivala
Sent: Friday, July 14, 2006 7:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Why "SELECT FIRST 1 ..." and "EXISTS ( SELECT *
FROM ... )" are so slow in a big database?



I have a big table (about 20.000.000 records). Running a simple "SELECT
FIRST 1" query or a NOT EXISTS check against this table takes a surprisingly
long time. I am using FB 2.0 RC 3.

Example:

SELECT FIRST 1 *
FROM DV_PV
WHERE
PROPERTYVALUE = 2

FB chooses the following plan: "PLAN (DV_PV INDEX (IX_DV_PV_PV))"

IX_DV_PV_PV has poor selectivity because there are many duplicate
PROPERTYVALUE values (about 1.000.000 records where PROPERTYVALUE = 2). But
I don't understand why poor selectivity would make the "FIRST 1" type of a
query slow. I also tried adding the PK to the end of the poor index but that
did not make any difference to the query speed.

The returned stats are:

Current memory = 33386968
Delta memory = 9152540
Max memory = 33393780
Elapsed time= 0.48 sec
Buffers = 65535
Reads = 0
Writes 0
Fetches = 1309

QUESTIONS:

1. Why does it take so long? I think FB should only need to visit the index
to find the first matching record.

2. What does "Fetches = 1309" mean? Does it mean that FB has fetched 1309
record from the DV_PV table?

3. What does "Current memory = 33386968" mean? I don't seem to get this
figure to rise even though Buffers = 65535 and page size is 8192. In Task
Manager I can see that FB is taking about 512 MB, which is the intention,
but I don't understand the memory amount reported by SET STATS.

My main intention is to do a DELETE from another table if a corresponding
record in the DV_PV table does not exist. The "DELETE FROM PV WHERE
PV.PropertyValue = 2 AND NOT EXISTS( SELECT * FROM DV_PV WHERE PropertyValue
= 2 )" query is just as slow as the above SELECT FIRST 1 query and is making
the delete take much too long.

Antti





[Non-text portions of this message have been removed]