RE: [firebird-support] How I can speed up this query
> 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 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 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.
Re: [firebird-support] How I can speed up this query
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 JOIN ON and not FROM , WHERE , 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_CORPOPNC ON PNS.PN_TESTA_ID = PNC.PN_TESTA_ID JOIN CLIENTI C ON PNC.CLIENTE_ID = C.ID JOIN PN_TESTAPNT ON PNS.PN_TESTA_ID = PNT.ID LEFT JOIN TMPON 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 ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How I can speed up this query
Hallo, Il 11/10/2017 20.38, setysvar setys...@gmail.com [firebird-support] ha scritto: > 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). Your query is to slow, flamerobin tells 4.360s. :( > PLAN (PNC2 NATURAL) > > seems to be your problem (PNC on the other hand, seems OK). > > 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. Ok, I create an index for PARTITA and SCADENZA fields for PNC and Your query speed up, Flamerobin now tells 0.060s :) And, my query, now, flamerobin tells 0.045s :)) The secondary index are automatically maintained by server, is it right to do a periodically manually maintenance for it's? Thanks -- Luigi Siciliano -- ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How I can speed up this query
Hallo, Il 13/10/2017 21.33, 'Daniel Miller' dmil...@amfes.com [firebird-support] ha scritto: > It would be interesting to compare the plans for both your original query syntax and Set's suggestion - see where the difference is. Original: PLAN (PNC2 NATURAL) PLAN JOIN (PNC NATURAL, PNT INDEX (PN_TESTA_PK), C INDEX (PK_CLIENTI), PNS INDEX (PN_SCADENZE_FK)) With Set suggestion: PLAN (PNC2 NATURAL) PLAN JOIN (PNC NATURAL, PNT INDEX (PN_TESTA_PK), C INDEX (PK_CLIENTI), PNS INDEX (PN_SCADENZE_FK)) Thanks. -- Luigi Siciliano --
