At 11:36 PM 29/07/2008, you wrote:

>hmm, no I should have been clearer, it's actually a sub query. the whole
>thing is:
>SELECT ID
>, BATCHCOUNT
>, (SELECT COUNT(*) FROM ERSINVOICE WHERE ERSINVOICE.FKBATCH IN
> (SELECT ERSVOUCHERBATCH.ID FROM ERSVOUCHERBATCH WHERE
>ERSVOUCHERBATCH.FKEXPORTLOG=ERSBATCHESLOG.ID))
>, BTYPE
>FROM ERSBATCHESLOG

It's a subquery expression with an embedded existence subquery.

>so the identifiers are correct,

No. And Fb 2 needs them to be.

>but it might be a double EXISTS clause.

It's not.

>BTW this runs very fast in 1.5 but it's a dog in 2.1

It probably gives wrong results in 1.5 as well.

SELECT
ebl.ID,
ebl.BATCHCOUNT,
(SELECT COUNT(inv.*) FROM ERSINVOICE inv
WHERE inv.FKBATCH IN
(SELECT evb.BATCH.ID FROM ERSVOUCHERBATCH evb
WHERE evb.FKEXPORTLOG=ebl.ID)) as InvCount,
ebl.BTYPE
FROM ERSBATCHESLOG ebl

But I'm pretty sure you'll get that count a lot faster by replacing the embedded existence query with an inner join, thus removing the second level of correlation:

SELECT
ebl.ID,
ebl.BATCHCOUNT,
(SELECT COUNT(inv.*) FROM ERSINVOICE inv
join ERSVOUCHERBATCH evb
on evb.BATCH.ID = inv.FKBATCH
WHERE evb.FKEXPORTLOG=ebl.ID) as InvCount,
ebl.BTYPE
FROM ERSBATCHESLOG ebl

./h