Commented by: Sean Leyne (seanleyne)

This is not a engine bug or direct problem, it is simply a poorly written query and/or missing indexes.

Your queries is resulting in 8,000,000^2 rows being read!!!!

The more optimal version of the query would be:

update full_combin_random c set
c.utilise=1
where
EXISTS(
SELECT 1
from full_combin_random a
inner join full_combin b on (
b.utilise=1 and
a.A=b.A and
a.B=b.B and
a.C=b.C and
a.D=b.D and
a.E=b.E and
a.F=b.F
)
WHERE
http://a.ID = http://c.ID
)

The query performance is affected by the use of the IN() instead of EXISTS() and whether there is a compound index A+B+C+D+E+F+utilise on the full_combin table.

The optimization of the query should be discussed in the Firebird Support mailing list (http://groups.yahoo.com/group/firebird-support/) not in this tracker.

Commented by: Sean Leyne (seanleyne)

Given the lack of response to the comments, the case is closed. It can be further reviewed in additional comments/details become available.