11.10.2017 19:43, Luigi Siciliano wrote:

> Hallo,
>
> I have this query
>
> SELECT
> ID,
> DENOMINAZIONE,
> SCADENZA,
> PARTITA,
> NUMERO_DOCUMENTO,
> DATA_DOCUMENTO,
> IMPORTO,
> IMPORTO - PAGATO AS RESIDUO,
> PAGATO,
> PAGAMENTO,
> SALDARE
> from
> (
> SELECT
> C.ID,
> C.DENOMINAZIONE,
> PNS.SCADENZA,
> PNS.PARTITA,
> PNT.NUMERO_DOCUMENTO,
> PNT.DATA_DOCUMENTO,
> PNS.IMPORTO,
> COALESCE((SELECT
> SUM(PNC2.AVERE - PNC2.DARE)
> from
> PN_CORPO PNC2
> WHERE
> PNC2.PARTITA = PNS.PARTITA
> AND PNC2.SCADENZA = PNS.SCADENZA
> AND PNC2.SCADENZA IS NOT NULL
> ), 0) AS PAGATO,
> 0.00 AS PAGAMENTO,
> '0' AS SALDARE
> from
> PN_SCADENZE PNS,
> PN_CORPO PNC,
> CLIENTI C,
> PN_TESTA PNT
> where
> PNS.PN_TESTA_ID = PNC.PN_TESTA_ID
> AND C.ID = PNC.CLIENTE_ID
> AND PNT.ID = PNS.PN_TESTA_ID
> )
>
> PLAN (PNC2 NATURAL)
> PLAN JOIN (PNC NATURAL, PNT INDEX (PN_TESTA_PK), C INDEX (PK_CLIENTI),
> PNS INDEX (PN_SCADENZE_FK))
>
> 4684158 fetches, 0 marks, 0 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 3011 index, 2313903 seq.
> Delta memory: 70560 bytes.
> Total execution time: 1.608s
>
> How I can speed up it?
> The time 1.608s if referred at only 300 rows fetched as defaults of
> flamerobin. In production I have 1157 rows affected (they grows rapidly)
> in near 6.20s.

Hi Luigi!

I never write SELECT ... FROM SELECT ... COALESCE(( SELECT..., so I
decided to rewrite your query so that it was easier for me to understand
(and then I changed from implicit to explicit joins, you always ought to
use FROM <table> JOIN <table> ON <join criteria> and not FROM <table>,
<table> WHERE <join criteria>, queries with JOIN are far easier to read
and some errors are much easier to see ):

WITH TMP( PARTITA, SCADENZA, PAGATO ) AS
( SELECT PARTITA, SCADENZA, SUM( AVERE - DARE )
FROM PN_CORPO
GROUP BY 1, 2 )

SELECT
C.ID,
C.DENOMINAZIONE,
PNS.SCADENZA,
PNS.PARTITA,
PNT.NUMERO_DOCUMENTO,
PNT.DATA_DOCUMENTO,
PNS.IMPORTO,
PNS.IMPORTO - COALESCE( TMP.PAGATO, 0 ) AS RESIDUO,
COALESCE( TMP.PAGATO, 0 ) AS PAGATO,
0.00 AS PAGAMENTO,
'0' AS SALDARE
FROM PN_SCADENZE PNS
JOIN PN_CORPO PNC ON PNS.PN_TESTA_ID = PNC.PN_TESTA_ID
JOIN CLIENTI C ON PNC.CLIENTE_ID = C.ID
JOIN PN_TESTA PNT ON PNS.PN_TESTA_ID = PNT.ID
LEFT JOIN TMP ON PNS.PARTITA = TMP.PARTITA
AND PNS.SCADENZA = TMP.SCADENZA

I think this should give the same result as your query, whether or not
it is any quicker, I simply do not know (but I would love to hear if it
made any difference).

As for how long time a query takes, it is rather irrelevant how many
rows it returns, it is more important how many rows it has to process to
reach the result (the amount of rows returned are more important for
transferring through a slow network). Looking at your plan, I would say
that

PLAN (PNC2 NATURAL)

seems to be your problem (PNC on the other hand, seems OK). I don't know
the internals of the optimizer, but my way of thinking about it, is that
Firebird has to go through all rows of PNC2 for every row it intends to
return. If the query returns 300 rows and the PNC2 table contains 1000
records, that means 300.000 rows.

Hence, if PARITA and/or SCADENZA are selective, I would recommend that
you create an index for either or both of these fields. That way, I
would assume your original query to become a lot quicker.

HTH,
Set