Sorry for a bit late reply, Roland.

The point is not to avoid NATURAL, but to avoid looking at records of no
interest. Hence, unless "C.DATUM <= '2008/12/31'" eliminates the
majority of records, the index IDX_SALDOKONTO_DATUM slows down the query
rather than speed it up.

The way I think about your query (using my simplified mind, not a proper
representation of what the optimizer does), is that for each record in
A, first all records in D with the appropriate FAKTURY_JEDIN has to be
obtained, then the PAROVACIE_CISLO of the first of them are selected.
Only then C are involved using the indexes for PAROVACIE_CISLO and DATUM
and the SUM calculated (or maybe D is calculated once for every
potential record in C?). Hence, I'm not chocked that this is slow,
although I am surprised that you notice any slowness when your tables
only contain 5000 and 10000 records.

I would suggest that you try using the WITH statement as outlined below.
It may well contain syntax errors since I'm not yet using an appropriate
Firebird version, am fighting a flu currently and should have gone to
bed long ago (a bad combination when trying to help others).

WITH TEMP_C(PAROVACIE_CISLO, SUM_ROLAND) as
(SELECT PAROVACIE_CISLO, SUM(MA_DAT-DAL) FROM SALDOKONTO
WHERE DATUM+0 <= '2008/12/31'
GROUP BY 1)

SELECT A.JEDIN,A.ROK,EXTRACT(YEAR FROM A.DATUM_VYSTAVENIA),A.DRUH_DOKLADU,
A.CISLO,A.DATUM_VYSTAVENIA,A.DATUM_UCTOVANIA,
A.DATUM_SPLATNOSTI,B.NAZOV_UPLNY,A.VARIABILNY_SYMBOL,A.UCTOVNY_UCET,
A.PRIJEM-A.VYDAJ,B.JEDIN, C.SUM_ROLAND
FROM FAKTURY A
LEFT JOIN TEMP_C C ON EXISTS(SELECT * FROM SALDOKONTO D
WHERE D.PAROVACIE_CISLO = C.PAROVACIE_CISLO
AND D.FAKTURY_JEDIN=A.JEDIN)
LEFT JOIN ADRESAR B ON B.JEDIN=A.ADRESAR
WHERE A.DATUM_VYSTAVENIA<='2008/12/31' AND A.TYP_DOKLADU=4;

Using EXISTS as the only criteria in a LEFT JOIN may seem strange (and I
don't remember whether I've ever done it before), but I think it may be
equivalent to your original statement (the one thing I'm uncertain about
is what Firebird does to your FIRST 1 part - I've ignored that bit).

Please report back to this list whether it gets you the correct result
and whether it helps or not, I hope it does,
Set

Roland Turcan wrote:

> Hello FireBird Support!
>
> I have this SQL statement in store procedure:
>
> SELECT A.JEDIN,A.ROK,EXTRACT(YEAR FROM A.DATUM_VYSTAVENIA),A.DRUH_DOKLADU,
> A.CISLO,A.DATUM_VYSTAVENIA,A.DATUM_UCTOVANIA,
> A.DATUM_SPLATNOSTI,B.NAZOV_UPLNY,A.VARIABILNY_SYMBOL,A.UCTOVNY_UCET,
> A.PRIJEM-A.VYDAJ,B.JEDIN,
> (SELECT SUM(C.MA_DAT-C.DAL) FROM SALDOKONTO C WHERE
> C.DATUM <= '2008/12/31' AND
> C.PAROVACIE_CISLO = (SELECT FIRST 1 D.PAROVACIE_CISLO
> FROM SALDOKONTO D
> WHERE D.FAKTURY_JEDIN=A.JEDIN))
> FROM FAKTURY A
> LEFT JOIN ADRESAR B ON B.JEDIN=A.ADRESAR
> WHERE A.DATUM_VYSTAVENIA<='2008/12/31' AND A.TYP_DOKLADU=4;
>
> which has execution plan:
>
> PLAN (D INDEX (FK_SALDOKONTO_FAKTURY))
> PLAN (C INDEX (IDX_SALDOKONTO_PAROVACIE_CISLO, IDX_SALDOKONTO_DATUM))
> PLAN JOIN (A INDEX (IDX_FAKTURY_DATUM_VYSTAVENIA), B INDEX (RDB$PRIMARY3))
>
> where is no more natural selection, but the subselect gets it slow and
> I have no more idea how to get it faster.
>
> Any idea?
>
> Many thanks in advance.
>
> I'm using Firebird 2.1.