Svein Erling Tysvaer wrote:

> Well, I only know that I don't get any such error with a similar query
> and Firebird 1.5.3.

If I use the query alone, there is no problem. It's just the
use as a subquery...

BTW, I remembered that I had already built a view which includes
only payments (vonlypay), and another view which includes only
the latest payment of each customer (vlastpay). Using that latter
view in the "big" query with

sum(
case
when (b.transtype = 'R') /* reminder */
and
(b.transdate > (select transdate from vlastpay v
where company=2 and v.custno = b.custno))
then 1
else 0
end
) as current_reminders

gives the following extraordinary result (look at the "execute time"
and the "fetches from cache"):

Plan:
PLAN SORT ((V VONLYPAY BILLING INDEX (RDB$FOREIGN16,RDB$FOREIGN16,RDB$FOREIGN16,RDB$FOREIGN16,RDB$FOREIGN16,RDB$FOREIGN16,RDB$FOREIGN16)))
PLAN SORT (JOIN (B INDEX (RDB$FOREIGN15),T INDEX (RDB$PRIMARY1)))
Adapted plan:
PLAN SORT ((V VONLYPAY BILLING INDEX (INTEG_54,INTEG_54,INTEG_54,INTEG_54,INTEG_54,INTEG_54,INTEG_54))) PLAN SORT (JOIN (B INDEX (INTEG_47),T INDEX (INTEG_2)))
------ Performance info ------
Prepare time = 31ms
Execute time = 6h 28m 51s 703ms
Avg fetch time = 686.226,56 ms
Current memory = 2.107.072
Max memory = 2.477.564
Memory buffers = 2.048
Reads from disk to cache = 39.943.556
Writes from cache to disk = 325
Fetches from cache = -311.326.039

Funny, eh? The results aren't very correct either :-)

> Yes, I see, sorry. It is not all customers that are shown, but I forgot
> that the same custno may exist in several companies. Change to
>
> and EXISTS(select * from billing b3
> where b3.custno = b.custno
> and b3.company = b.company
> and b3.cleared+0 = 0)

Yes, now it's the correct result. FYI:

Plan:
PLAN (B3 INDEX (ICOMPANYCUSTNOTRANSTYPE))
PLAN SORT (JOIN (B INDEX (RDB$FOREIGN15),T INDEX (RDB$PRIMARY1)))
Adapted plan:
PLAN (B3 INDEX (ICOMPANYCUSTNOTRANSTYPE)) PLAN SORT (JOIN (B INDEX (INTEG_47),T INDEX (INTEG_2)))
------ Performance info ------
Prepare time = 32ms
Execute time = 31s 78ms
Avg fetch time = 914,06 ms
Current memory = 1.481.092
Max memory = 2.477.564
Memory buffers = 2.048
Reads from disk to cache = 1.415
Writes from cache to disk = 0
Fetches from cache = 14.467.120

> The reason for the slowness may also be that the ICLEARED index is not
> very selective and that my subselect didn't use any other indexes for
> that table. Hopefully, this is due to me not specifying the company
> above and that you have an index on (company, custno) or similar (I
> assumed - probably incorrectly - that you had an index on custno by
> itself). Also, due to the extreme slowness I added +0, if your ICLEARED
> index does contain (cleared, company, custno) or similar, you may want
> to remove this +0.

Yes, that's the index "ICOMPANYCUSTNOTRANSTYPE". The above result was
with "+0"; if I leave that out, it takes about twice as long (and an
additional index, ICLEARED, is now used, too):

Plan:
PLAN (B3 INDEX (ICOMPANYCUSTNOTRANSTYPE,ICLEARED))
PLAN SORT (JOIN (B INDEX (RDB$FOREIGN15),T INDEX (RDB$PRIMARY1)))
Adapted plan:
PLAN (B3 INDEX (ICOMPANYCUSTNOTRANSTYPE,ICLEARED)) PLAN SORT (JOIN (B INDEX (INTEG_47),T INDEX (INTEG_2)))
------ Performance info ------
Prepare time = 15ms
Execute time = 1m 5s 578ms
Avg fetch time = 1.928,76 ms
Current memory = 1.591.380
Max memory = 2.477.564
Memory buffers = 2.048
Reads from disk to cache = 21
Writes from cache to disk = 0
Fetches from cache = 1.655.508

> These may well be factors, but the difference should be a lot less (I
> don't know which will be the fastest, but 1.125 seconds sounds good, so
> maybe HAVING beats EXISTS).

Seems so. Meanwhile, I got the feeling that subSELECTs should be
avoided whenever possible (for timing reasons), at least when used
within aggregate functions (SUM) - I guess they're just executed
way too often for good timing results...

Matthias