Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Is Optimized this query ? |
Thu, Jul 2 2009 3:45 AM | Permanent Link |
"Mauro Botta" | Hi
Is full optimized this query ? select mag.*, tabProd.DESCRIZIONE as campo1,fornitor.codice,fornitor.descriz1 as campo2,tabgru.DESCRIZ as campo3 from mag LEFT OUTER JOIN tabProd on mag.CODTABPROD = tabProd.CODICE LEFT OUTER JOIN fornitor on mag.codforn = fornitor.codice LEFT OUTER JOIN tabgru on mag.COD_MERCE = tabgru.COD_MERCE where fornitor.codice = 2 order by campo1,campo2,campo3 i have all indexs / fields Case Insensitive. PLAN : ================================================================================ SQL Query (Executed by ElevateDB 2.02 Build 10) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "mag"."CODARTI" AS "CODARTI", "mag"."CODAFOR" AS "CODAFOR", "mag"."CODORIGINALE" AS "CODORIGINALE", "mag"."DESCRIZ1" AS "DESCRIZ1", "mag"."DESCRIZ2" AS "DESCRIZ2", "mag"."DESCRIZIONE_LINGUA1" AS "DESCRIZIONE_LINGUA1", "mag"."DESCRIZIONE_LINGUA2" AS "DESCRIZIONE_LINGUA2", "mag"."DESCRIZIONE_LINGUA3" AS "DESCRIZIONE_LINGUA3", "mag"."CODFORN" AS "CODFORN", "mag"."COD_LINEA" AS "COD_LINEA" "mag"."ANNO" AS "ANNO", "mag"."CODTESSUTO" AS "CODTESSUTO", "mag"."CIDTAGMIS" AS "CIDTAGMIS", "mag"."CODMARCA" AS "CODMARCA", "mag"."CODCATEG" AS "CODCATEG", "mag"."UNMISURA" AS "UNMISURA", "mag"."COD_MERCE" AS "COD_MERCE", "mag"."CODTABPROD" AS "CODTABPROD", "mag"."SIGLA_LISTINO" AS "SIGLA_LISTINO", "mag"."COD_FAM_SCONTO" AS "COD_FAM_SCONTO", "mag"."TIPOCODBARRE" AS "TIPOCODBARRE", "mag"."GIAC_TOT" AS "GIAC_TOT", "mag"."QTA_ASSEGNATO_TOT" AS "QTA_ASSEGNATO_TOT", "mag"."GIAC_TOT_REMOTA" AS "GIAC_TOT_REMOTA", "mag"."ORDINATO_TOT_REMOTO" AS "ORDINATO_TOT_REMOTO", "mag"."IMPEGNATO_TOT_REMOTO" AS "IMPEGNATO_TOT_REMOTO", "mag"."IMPE_TOT" AS "IMPE_TOT", "mag"."ORDI_TOT" AS "ORDI_TOT", "mag"."DISP_TOT" AS "DISP_TOT" "mag"."COD_IVA" AS "COD_IVA", "mag"."GIAC_MIN" AS "GIAC_MIN", "mag"."GIACENZA_CONSIGLIATA" AS "GIACENZA_CONSIGLIATA", "mag"."PRECPREZAC" AS "PRECPREZAC", "mag"."SCONTO_ACQ_1" AS "SCONTO_ACQ_1", "mag"."SCONTO_ACQ_2" AS "SCONTO_ACQ_2", "mag"."SCONTO_ACQ_3" AS "SCONTO_ACQ_3", "mag"."PERC_PRZ_TRASP_ACQ" AS "PERC_PRZ_TRASP_ACQ", "mag"."VAL_PRZ_TRASP_ACQ" AS "VAL_PRZ_TRASP_ACQ", "mag"."PREZZO_LISTINO" AS "PREZZO_LISTINO", "mag"."SCONTO_LISTINO_1" AS "SCONTO_LISTINO_1", "mag"."SCONTO_LISTINO_2" AS "SCONTO_LISTINO_2", "mag"."SCONTO_LISTINO_3" AS "SCONTO_LISTINO_3", "mag"."ULTPREZAC" AS "ULTPREZAC", "mag"."ULTDATAAC" AS "ULTDATAAC", "mag"."PERC_TRASPORTO" AS "PERC_TRASPORTO", "mag"."NUMSERIE" AS "NUMSERIE", "mag"."CODVALUTA" AS "CODVALUTA", "mag"."PREZNET1" AS "PREZNET1", "mag"."PREZNET2" AS "PREZNET2", "mag"."PREZNET3" AS "PREZNET3", "mag"."PREZNET4" AS "PREZNET4", "mag"."PREZNET5" AS "PREZNET5", "mag"."PREZIVA1" AS "PREZIVA1", "mag"."PREZIVA2" AS "PREZIVA2", "mag"."PREZIVA3" AS "PREZIVA3", "mag"."PREZIVA4" AS "PREZIVA4", "mag"."PREZIVA5" AS "PREZIVA5", "mag"."RICARICO1" AS "RICARICO1", "mag"."RICARICO2" AS "RICARICO2", "mag"."RICARICO3" AS "RICARICO3", "mag"."RICARICO4" AS "RICARICO4", "mag"."RICARICO5" AS "RICARICO5", "mag"."SCOLIST1" AS "SCOLIST1", "mag"."SCOLIST2" AS "SCOLIST2", "mag"."SCOLIST3" AS "SCOLIST3", "mag"."SCOLIST4" AS "SCOLIST4" "mag"."SCOLIST5" AS "SCOLIST5", "mag"."ARRPREZ1" AS "ARRPREZ1", "mag"."ARRPREZ2" AS "ARRPREZ2", "mag"."ARRPREZ3" AS "ARRPREZ3", "mag"."ARRPREZ4" AS "ARRPREZ4" "mag"."ARRPREZ5" AS "ARRPREZ5", "mag"."DATA_ULT_PRZ" AS "DATA_ULT_PRZ", "mag"."OBSOLETO" AS "OBSOLETO", "mag"."FLAG_ARTICOLO_BLOCCATO" AS "FLAG_ARTICOLO_BLOCCATO", "mag"."DATA_ARTICOLO_BLOCCATO" AS "DATA_ARTICOLO_BLOCCATO", "mag"."MODALITA_UPD_PREZZO_ACQ" AS "MODALITA_UPD_PREZZO_ACQ", "mag"."MODALITA_UPD_PREZZO_ACQ_GIRARE_FORNITORE" AS "MODALITA_UPD_PREZZO_ACQ_GIRARE_FORNITORE", "mag"."STAMLIST" AS "STAMLIST", "mag"."MOVIARTI" AS "MOVIARTI", "mag"."CODBARRE" AS "CODBARRE", "mag"."FLAG_PROM" AS "FLAG_PROM", "mag"."FLAGTIPOPROM" AS "FLAGTIPOPROM", "mag"."SCOPROM" AS "SCOPROM", "mag"."SCOPROM_2" AS "SCOPROM_2", "mag"."SCOPROM_3" AS "SCOPROM_3", "mag"."SCOPROM_4" AS "SCOPROM_4", "mag"."QTAPROM" AS "QTAPROM", "mag"."QTAPROM_FINO_A_1" AS "QTAPROM_FINO_A_1", "mag"."QTAPROM_FINO_A_2" AS "QTAPROM_FINO_A_2", "mag"."QTAPROM_FINO_A_3" AS "QTAPROM_FINO_A_3", "mag"."QTAPROM_FINO_A_4" AS "QTAPROM_FINO_A_4", "mag"."PREPRNET" AS "PREPRNET", "mag"."PREPRNET_2" AS "PREPRNET_2", "mag"."PREPRNET_3" AS "PREPRNET_3", "mag"."PREPRNET_4" AS "PREPRNET_4", "mag"."PREPRIVA" AS "PREPRIVA", "mag"."PREPRIVA_2" AS "PREPRIVA_2", "mag"."PREPRIVA_3" AS "PREPRIVA_3", "mag"."PREPRIVA_4" AS "PREPRIVA_4", "mag"."DATA_INIZ_PROM" AS "DATA_INIZ_PROM", "mag"."DATA_FINE_PROM" AS "DATA_FINE_PROM", "mag"."QTA_RIORDINO_MIN_FORNITORE" AS "QTA_RIORDINO_MIN_FORNITORE", "mag"."DESC_ETI" AS "DESC_ETI", "mag"."FLAG_MANODOPERA" AS "FLAG_MANODOPERA", "mag"."FLAG_PRODOTTO_FINITO" AS "FLAG_PRODOTTO_FINITO", "mag"."UTENTECAR" AS "UTENTECAR", "mag"."UTENTENUM" AS "UTENTENUM", "mag"."FOTO" AS "FOTO", "mag"."PESO" AS "PESO", "mag"."CODREP" AS "CODREP", "mag"."CODSET" AS "CODSET", "mag"."CODFAM" AS "CODFAM", "mag"."POSIZMAGA" AS "POSIZMAGA", "mag"."QTACONFEZIONE" AS "QTACONFEZIONE", "mag"."CODSCHEDAPROD" AS "CODSCHEDAPROD", "mag"."DESCRIZIONE_TEMP1" AS "DESCRIZIONE_TEMP1", "mag"."DESCRIZIONE_TEMP2" AS "DESCRIZIONE_TEMP2", "mag"."COD_FREQUENZA" AS "COD_FREQUENZA", "mag"."COD_CAUS_VEND" AS "COD_CAUS_VEND", "mag"."COD_CAUS_VEND_2" AS "COD_CAUS_VEND_2", "mag"."CODRAGGRUPPAMENTO" AS "CODRAGGRUPPAMENTO", "mag"."DISTINTA_AGGIORNAMENTO_AUTOMATICO" AS "DISTINTA_AGGIORNAMENTO_AUTOMATICO", "mag"."DISTINTA_PRZ_FIGLI_BLOCCATI" AS "DISTINTA_PRZ_FIGLI_BLOCCATI", "mag"."DISTINTA_RICARICO" AS "DISTINTA_RICARICO", "mag"."DISTINTA_TOTALE_LISTINO" AS "DISTINTA_TOTALE_LISTINO", "mag"."DISTINTA_TOTALE_ACQ" AS "DISTINTA_TOTALE_ACQ", "mag"."DISTINTA_TOTALE" AS "DISTINTA_TOTALE", "mag"."DISTINTA_PREZZO_SU_PADRE" AS "DISTINTA_PREZZO_SU_PADRE", "mag"."DISTINTA_QTA_PADRE" AS "DISTINTA_QTA_PADRE", "mag"."DISTINTA_PREZZO_SU_FIGLI" AS "DISTINTA_PREZZO_SU_FIGLI", "mag"."DISTINTA_QTA_FIGLI" AS "DISTINTA_QTA_FIGLI", "mag"."DISTINTA_MOVIMENTA_PADRE" AS "DISTINTA_MOVIMENTA_PADRE", "mag"."DISTINTA_MOVIMENTA_FIGLI" AS "DISTINTA_MOVIMENTA_FIGLI", "mag"."FLAG_NON_ESPORTARE" AS "FLAG_NON_ESPORTARE", "mag"."FLAG_NON_CONTEGGIARE" AS "FLAG_NON_CONTEGGIARE", "mag"."FLAG_NON_RINNOVABILE" AS "FLAG_NON_RINNOVABILE", "mag"."CONFEZIONE_MODALITA_PREZZO" AS "CONFEZIONE_MODALITA_PREZZO", "mag"."CONFEZIONE_UNITA_MISURA" AS "CONFEZIONE_UNITA_MISURA", "mag"."LEGAME_LISTINO_MODALITA" AS "LEGAME_LISTINO_MODALITA", "mag"."LEGAME_LISTINO_COD_FORN" AS "LEGAME_LISTINO_COD_FORN", "mag"."ORARIO_INIZIO_1" AS "ORARIO_INIZIO_1", "mag"."ORARIO_FINE_1" AS "ORARIO_FINE_1", "mag"."ORARIO_INIZIO_2" AS "ORARIO_INIZIO_2", "mag"."ORARIO_FINE_2" AS "ORARIO_FINE_2", "mag"."CODTIPRISORSA" AS "CODTIPRISORSA", "mag"."DATAINS" AS "DATAINS", "mag"."ORA_INSERIMENTO" AS "ORA_INSERIMENTO", "mag"."OPERATORE_INSERIMENTO" AS "OPERATORE_INSERIMENTO", "mag"."DATAULTVAR" AS "DATAULTVAR", "mag"."ORA_MODIFICA" AS "ORA_MODIFICA", "mag"."OPERATORE_MODIFICA" AS "OPERATORE_MODIFICA", "mag"."EC_VISIBILITA_IN_NEGOZIO" AS "EC_VISIBILITA_IN_NEGOZIO" "mag"."EC_IN_VENDITA" AS "EC_IN_VENDITA", "mag"."EC_MOSTRA_PREZZO" AS "EC_MOSTRA_PREZZO", "mag"."EC_ARTICOLO_IN_HOMEPAGE" AS "EC_ARTICOLO_IN_HOMEPAGE", "mag"."EC_ARTICOLO_SEMPRE_ORDINABILE" AS "EC_ARTICOLO_SEMPRE_ORDINABILE", "mag"."EC_DATA_INIZIO_DISPONIBILITA" AS "EC_DATA_INIZIO_DISPONIBILITA", "mag"."EC_GIORNI_RIASSORTIMENTO" AS "EC_GIORNI_RIASSORTIMENTO", "mag"."EC_DESCRIZIONE_BREVE" AS "EC_DESCRIZIONE_BREVE", "mag"."EC_DESCRIZIONE_BREVE_LINGUA_1" AS "EC_DESCRIZIONE_BREVE_LINGUA_1", "mag"."EC_DESCRIZIONE_METATAG" AS "EC_DESCRIZIONE_METATAG", "mag"."EC_DESCRIZIONE_SCHEDA_TECNICA" AS "EC_DESCRIZIONE_SCHEDA_TECNICA", "mag"."EC_DESCRIZIONE_SCHEDA_TECNICA_LINGUA_1" AS "EC_DESCRIZIONE_SCHEDA_TECNICA_LINGUA_1", "mag"."EC_TITOLO_PAGINA" AS "EC_TITOLO_PAGINA", "mag"."EC_TITOLO_PAGINA_LINGUA_1" AS "EC_TITOLO_PAGINA_LINGUA_1", "mag"."EC_PAROLA_CHIAVE" AS "EC_PAROLA_CHIAVE" "mag"."EC_DESCRIZIONE_ESTESA" AS "EC_DESCRIZIONE_ESTESA", "mag"."EC_DESCRIZIONE_ESTESA_LINGUA_1" AS "EC_DESCRIZIONE_ESTESA_LINGUA_1", "mag"."CARATTERISTICHE" AS "CARATTERISTICHE", "mag"."NOTALUNGA" AS "NOTALUNGA", "mag"."NOTE" AS "NOTE", "tabProd"."DESCRIZIONE" AS "campo1", "fornitor"."codice" AS "codice", "fornitor"."descriz1" AS "campo2", "tabgru"."DESCRIZ" AS "campo3" FROM "mag" LEFT OUTER JOIN "tabProd" ON "mag"."CODTABPROD" = "tabProd"."CODICE", LEFT OUTER JOIN "tabgru" ON "mag"."COD_MERCE" = "tabgru"."COD_MERCE", LEFT OUTER JOIN "fornitor" ON "mag"."codforn" = "fornitor"."codice" WHERE "fornitor"."codice" = 2 ORDER BY "tabProd"."DESCRIZIONE", "fornitor"."descriz1", "tabgru"."DESCRIZ" Source Tables ------------- mag: 12663 rows tabProd: 1112 rows fornitor: 285 rows tabgru: 75 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the result set rows as they were generated: "fornitor"."codice" = 2 Joins ----- The driver table was the mag table The mag table was joined to the tabProd table with the left outer join expression: "mag"."CODTABPROD" = "tabProd"."CODICE" The mag table was joined to the tabgru table with the left outer join expression: "mag"."COD_MERCE" = "tabgru"."COD_MERCE" The mag table was joined to the fornitor table with the left outer join expression: "mag"."codforn" = "fornitor"."codice" The optimizer attempted to re-order the joins to a more optimal order The joins were already in the most optimal order The following join condition was applied to the tabProd table: "tabProd"."CODICE" = "mag"."CODTABPROD" [Index scan (tabprod.CODICE)] The following join condition was applied to the tabgru table: "tabgru"."COD_MERCE" = "mag"."COD_MERCE" [Index scan (tabgru.COD_MERCE)] The following join condition was applied to the fornitor table: "fornitor"."codice" = "mag"."codforn" [Index scan (fornitor.CODICE)] ================================================================================ 1200 row(s) returned in 1,03 secs ================================================================================ |
Thu, Jul 2 2009 4:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
It looks pretty optimised. Its using index scans for all the joins. I'm not sure about <<The following filter condition was applied to the result set rows as they were generated: "fornitor"."codice" = 2>> That may mean that there isn't an appropriate index to use or that ElevateDB decided that it was more efficient to scan each row as generated. I'm not 100% sure of the exact meaning of the new format of query plans yet. Roy Lambert [Team Elevate] |
Fri, Jul 3 2009 1:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< It looks pretty optimised. Its using index scans for all the joins. I'm not sure about >> That notation means that the filter condition was applied *after* the joins due to the fact that the fornitor table was the target of a left-outer join. In such a case EDB has to examine the row after the joins are executed in order to take care of a situation where the left outer join generated a NULL for the codice column. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jul 4 2009 3:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Yuknow I thought exactly that Have a nice 4th - are you going to try and get to the top of the Statue of Liberty now she's reopened? Roy Lambert |
Mon, Jul 6 2009 12:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Have a nice 4th - are you going to try and get to the top of the Statue of Liberty now she's reopened? >> I don't do heights normally, but I might have to make an exception for that. NYC is just a quick flight or semi-quick train ride from here, which is great for short little trips. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |