Optimize query join with 25,000 records
Hello,
I wanted to submit a question optimization, if possible, about a query to JOIN multiple tables.
Optimization because the affected tables have between 20,000 and 30,000 records.
Currently it's work and everything is run, only that the average execution time of the query is 20 minutes.
Immediately place the query so I go to the point:
SELECT
prodottidatigen.codprod,prodottidatigen.descrizion e,prodottidatigen.prz,prodottidatigen.iva,,prodott idatigen.um,
jgs.Trattanti,jgs.qtatrattate,jgs.euro,
prodottidatiean.ean,
prodottidatigiacenze.giac_fisica,
prodottifornitore.codprodfornit,prodottifornitore. codfornit,prodottifornitore.nomefornit,
prodottipackage.pzct,
prodottidatiprezzi01.datadecor01,prodottidatiprezz i01.przAcq01,
prodottidatiprezzi10.datadecor10,prodottidatiprezz i10.przAcq10
prodottidatiprezzizero.datadecorzero,prodottidatip rezzizero.przAcqzero,
prodottiOper.NomeOperatore
FROM prodottidatigen
LEFT JOIN jgs USING (codprod)
LEFT JOIN prodottidatiean USING (codprod)
LEFT JOIN prodottidatigiacenze USING (codprod)
LEFT JOIN prodottipackage USING (codprod)
LEFT JOIN prodottifornitore USING (codprod)
LEFT JOIN prodottidatiprezzi01 USING (codprod)
LEFT JOIN prodottidatiprezzi10 USING (codprod)
LEFT JOIN prodottidatiprezzizero USING (codprod)
LEFT OUTER JOIN prodottiOper
ON prodottifornitore.operat = prodottiOper.codOper
So, there are 10 tables each containing various data/different fields of sale items, which are about 25,000
All tables have in common the ProductCode, which is used as a merge field.
Only the last table does not use the ProductCode as a merge field but uses a different one.
I perform this query and can retrieve all the data I need, relate properly.
Everything working, but the running time is just a bit 'long,
I guess due to the fact that the records involved are so great.
The data obtained from this query will be use to insert a table "final" consultation through:
INSERT INTO tabFinal (codProd, ..., ...,)
SELECT ==> join query shown above
If there is the possibility to optimize the execution time of the query,
maybe using another method instead of join are well available to test it.
Thanks to anyone who can give me a hint