There should be a foreign key between PV_TYPE.PV and DV_PV.PV, and if
the key is set to No Action on delete, the delete won't occur. You'll
have to catch the exception, however.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Antti Nivala
Sent: Saturday, July 15, 2006 5:50 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Why "SELECT FIRST 1 ..." and "EXISTS (
SELECT * FROM ... )" are so slow in a big database?

Dmitry Yemanov wrote:

> Records are fetched from a table based on the bitmap filled by the
> index scan. I.e. no record can be fetched until the index scan
> completes for all existing matches. For a non-selective index, the
> scan may take quite a long time.

Thank you. This fully explains the problem I am experiencing. Is there a
workaround, i.e. is there a way to make a pure index-based existence
check based on the "PV = 2" condition when there are lots of (e.g.
1.000.000) records for which this is true? The table has 26.000.000
records so a natural scan would be much worse in many cases.

In practice, I am looking for a way to delete a record from table
PV_TYPE if that record is not referenced by any record in the DV_PV
table. For example, when deleting a record from PV_TYPE, I currently do
DELETE FROM PV_TYPE WHERE
PV = <x> AND
NOT EXISTS ( SELECT 1 FROM DV_PV WHERE PV = <x> )

This takes 0 ms if there are not many matching records in DV_PV, but 400
ms if there are 1.000.000 matching records in DV_PV. My database has
just been backed up and restored. The point is, as Dmitry said, that the
EXISTS check reads lots of data from the index in that case. Is there a
way to avoid that, i.e. utilize the index for a pure existence check? I
would want FB to stop reading the index as soon as it sees that at least
one record matches.

The workaround of having a manual count field is one possibility, but I
am still looking for a more efficient and elegant solution. Is there
one?

Antti




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links