--- In firebird-support@yahoogroups.com, "Antti Nivala"
<antti.nivala@...> wrote:

>
> 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.

Does it happen on FB 1.5? If not, take it to the FB2 devel list.

What ODS is your database? Possibly FB2 could handle it better, but
not if you are using a database created with FB1.5 or earlier.

Maybe it would be quicker to not use an index for the query if it is
5% of your table?

SELECT FIRST 1 *
FROM DV_PV
WHERE
PROPERTYVALUE+0 = 2

I think Paul suggested the order by because generally it does not make
sense to return only a specificly positioned record (in this case the
first 1) unless your set is ordered.

It think though you are just using it in place of an 'exists', you
don't really care about the values, just whether there is a record
that matches your query.

Maybe the fetches count includes the number of pages that need to be
read to build the index?

You may get better performance on the delete using a stored procedure
to read through the table, then delete them record by record.

Adam