> How I can speed up it?
> 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
<SL> Create an index on PN_CORPO( PARITA, 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
<SL> Use modern SQL syntax for JOINs:
From PN_SCADENZE PNS,
JOIN PN_CORPO PNC ON PNC.PN_TESTA_ID = PNS.PN_TESTA_ID
JOIN CLIENTI C ON C.ID = PNC.CLIENTE_ID
JOIN PN_TESTA PNT = PNT.ID = PNS.PN_TESTA_ID
> )
>
> From Statistics of Flamerobin:
>
> Prepare time: 0.014s
> PLAN (PNC2
> NATURAL) PLAN JOIN (PNC NATURAL, PNT INDEX (PN_TESTA_PK), C INDEX
> (PK_CLIENTI), PNS INDEX (PN_SCADENZE_FK))
>
> Executing...
> Done.
> 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
> Script execution finished
>
> 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.
