Helen Borrie wrote:

> At 04:36 PM 12/05/2009, you wrote:
> >I understand that 2.1 has improved the optimiser a lot, but in this
> >particular case it fails to realise that running those filters before
> >subquery can be faster, and it seems that it only chooses to do so when
> >there are indexes available on those filters.
>
> The misunderstanding here seems to be that your misconceptions about the
> logic of correlated subqueries and NOTed searches. In this case, my
> guess is that you can improve this query logic by using outer joins and
> using the WHERE clause to include only members that have null outer
> values - with the proviso that you provide the missing indexes, of course.

Fwiw I have always thought that Firebird's optimizer is not very good at
optimizing correlated subqueries compared to SQL Server, which I've used
a lot for ad hoc query work. With SQL Server I've always got the query
results within reasonable time (often less than a minute), almost no
matter what, but with FB I've often accidently written such queries that
could run for hours or days.

Maybe it's better in 2.1 - I haven't yet had time to experiment enough
to be able to say anything about that.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64