![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Tue, Oct 23 2007 11:35 AM | Permanent Link |
jean bernard | Hi
error with this: SELECT CLIENTS.NO_CLIENT, CLIENTS.NOM, FACTURES.ANNEE, SUM(LIGNFACT.Q_COM) AS TOTAL, VARIET.TIPE, CLIENTS.COMMISSION, CLIENTS.CODE_POSTAL FROM CLIENTS INNER JOIN FACTURES ON (CLIENTS.NO_CLIENT = FACTURES.NO_CLIENT) INNER JOIN LIGNFACT ON (FACTURES.NO_LIGNE = LIGNFACT.NOCOM) INNER JOIN VARIET ON (LIGNFACT.CODEVAR = VARIET.CODEVAR), ANNEE WHERE (FACTURES.ANNEE >= ANNEE.ANNEE - 4) AND (CLIENTS.REPRESENTANT =6)/* and (factures.tipe='FACTURE') */ GROUP BY 1,2,3,5,6,7 ================================================================================ SQL Query (Executed by ElevateDB 1.05 Build 2) 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 "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" = 6) AND ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) GROUP BY 1, 2, 3, 5, 6, 7 Source Tables ------------- CLIENTS: 2891 rows FACTURES: 16818 rows LIGNFACT: 330358 rows VARIET: 579 rows ANNEE: 1 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CLIENTS table: ("CLIENTS"."REPRESENTANT" = 6 [Index scan: 758 keys, 12288 bytes estimated cost]) The following filter condition was applied to the result set rows as they were generated: ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) Joins ----- The driver table was the CLIENTS table The CLIENTS table was joined to the FACTURES table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The FACTURES table was joined to the LIGNFACT table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The LIGNFACT table was joined to the VARIET table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The optimizer attempted to re-order the joins to a more optimal order The optimizer successfully re-ordered the joins into this more optimal order: The driver table was the VARIET table The VARIET table was joined to the LIGNFACT table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The LIGNFACT table was joined to the FACTURES table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The FACTURES table was joined to the CLIENTS table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The following join condition was applied to the LIGNFACT table: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR" [Index scan]) The following join condition was applied to the FACTURES table: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM" [Index scan]) The following join condition was applied to the CLIENTS table: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT" [Index scan]) ================================================================================ 1 row(s) returned in 30.703 secs ================================================================================ in firebird the same request: 4931 records fetched in 0.92 s |
Tue, Oct 23 2007 2:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Jean,
<< GROUP BY 1,2,3,5,6,7 >> You can't use this syntax in EDB since it isn't recommended in SQL 2003 anymore. You need to use the actual column names and/or expressions instead: SELECT ALL "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" = 6) AND ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) GROUP BY "NO_CLIENT", "NOM", "ANNEE", "TIPE", "COMMISSION", "CODE_POSTAL" -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 24 2007 2:33 PM | Permanent Link |
jean bernard | Tim Young [Elevate Software] a écrit :
thank's new sql: SELECT CLIENTS.NO_CLIENT, CLIENTS.NOM, FACTURES.ANNEE, SUM(LIGNFACT.Q_COM) AS TOTAL, VARIET.TIPE, CLIENTS.COMMISSION, CLIENTS.CODE_POSTAL FROM CLIENTS INNER JOIN FACTURES ON (CLIENTS.NO_CLIENT = FACTURES.NO_CLIENT) INNER JOIN LIGNFACT ON (FACTURES.NO_LIGNE = LIGNFACT.NOCOM) INNER JOIN VARIET ON (LIGNFACT.CODEVAR = VARIET.CODEVAR), ANNEE WHERE (FACTURES.ANNEE >= ANNEE.ANNEE - 4) AND (CLIENTS.REPRESENTANT =6) and (factures.tipe='FACTURE') GROUP BY CLIENTS.NO_CLIENT,CLIENTS.NOM,FACTURES.ANNEE,VARIET.TIPE,CLIENTS.COMMISSION,CLIENTS.CODE_POSTAL NOJOINOPTIMIZE ================================================================================ SQL Query (Executed by ElevateDB 1.05 Build 2) 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 "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" = 6) AND ("factures"."tipe" = 'FACTURE') AND ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) GROUP BY "CLIENTS"."NO_CLIENT", "CLIENTS"."NOM", "FACTURES"."ANNEE", "VARIET"."TIPE", "CLIENTS"."COMMISSION", "CLIENTS"."CODE_POSTAL" NOJOINOPTIMIZE Source Tables ------------- CLIENTS: 2891 rows FACTURES: 16818 rows LIGNFACT: 330358 rows VARIET: 579 rows ANNEE: 1 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CLIENTS table: ("CLIENTS"."REPRESENTANT" = 6 [Index scan: 758 keys, 12288 bytes estimated cost]) The following filter condition was applied to the FACTURES table: ("factures"."tipe" = 'FACTURE' [Index scan: 16505 keys, 262144 bytes estimated cost]) The following filter condition was applied to the result set rows as they were generated: ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) Joins ----- The driver table was the CLIENTS table The CLIENTS table was joined to the FACTURES table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The FACTURES table was joined to the LIGNFACT table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The LIGNFACT table was joined to the VARIET table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The NOJOINOPTIMIZE clause was used and the optimizer left the joins in their declared order The following join condition was applied to the FACTURES table: ("FACTURES"."NO_CLIENT" = "CLIENTS"."NO_CLIENT" [Index scan]) The following join condition was applied to the LIGNFACT table: ("LIGNFACT"."NOCOM" = "FACTURES"."NO_LIGNE" [Index scan]) The following join condition was applied to the VARIET table: ("VARIET"."CODEVAR" = "LIGNFACT"."CODEVAR" [Index scan]) ================================================================================ 5245 row(s) returned in 5.516 secs ================================================================================ is there a way to optimize this? |
Wed, Oct 24 2007 2:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Jean,
<< is there a way to optimize this? >> Take out this condition: ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) And then tell me how many rows are generated in the query result set. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Nov 7 2007 6:27 PM | Permanent Link |
jean bernard | Tim Young [Elevate Software] a écrit :
> Jean, > > << is there a way to optimize this? >> > > Take out this condition: > > ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) > > And then tell me how many rows are generated in the query result set. > Tim ================================================================================ SQL Query (Executed by ElevateDB 1.05 Build 2) 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 "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" = 6) AND ("factures"."tipe" = 'FACTURE') GROUP BY "CLIENTS"."NO_CLIENT", "CLIENTS"."NOM", "FACTURES"."ANNEE", "VARIET"."TIPE", "CLIENTS"."COMMISSION", "CLIENTS"."CODE_POSTAL" Source Tables ------------- CLIENTS: 2891 rows FACTURES: 16818 rows LIGNFACT: 330358 rows VARIET: 579 rows ANNEE: 1 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CLIENTS table: ("CLIENTS"."REPRESENTANT" = 6 [Index scan: 758 keys, 12288 bytes estimated cost]) The following filter condition was applied to the FACTURES table: ("factures"."tipe" = 'FACTURE' [Index scan: 16505 keys, 262144 bytes estimated cost]) Joins ----- The driver table was the CLIENTS table The CLIENTS table was joined to the FACTURES table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The FACTURES table was joined to the LIGNFACT table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The LIGNFACT table was joined to the VARIET table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The optimizer attempted to re-order the joins to a more optimal order The optimizer successfully re-ordered the joins into this more optimal order: The driver table was the VARIET table The VARIET table was joined to the LIGNFACT table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The LIGNFACT table was joined to the FACTURES table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The FACTURES table was joined to the CLIENTS table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The following join condition was applied to the LIGNFACT table: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR" [Index scan]) The following join condition was applied to the FACTURES table: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM" [Index scan]) The following join condition was applied to the CLIENTS table: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT" [Index scan]) ================================================================================ 10156 row(s) returned in 37.484 secs ================================================================================ ================================================================================ SQL Query (Executed by ElevateDB 1.05 Build 2) 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 "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" = 6) AND ("factures"."tipe" = 'FACTURE') GROUP BY "CLIENTS"."NO_CLIENT", "CLIENTS"."NOM", "FACTURES"."ANNEE", "VARIET"."TIPE", "CLIENTS"."COMMISSION", "CLIENTS"."CODE_POSTAL" Source Tables ------------- CLIENTS: 2891 rows FACTURES: 16818 rows LIGNFACT: 330358 rows VARIET: 579 rows ANNEE: 1 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CLIENTS table: ("CLIENTS"."REPRESENTANT" = 6 [Index scan: 758 keys, 12288 bytes estimated cost]) The following filter condition was applied to the FACTURES table: ("factures"."tipe" = 'FACTURE' [Index scan: 16505 keys, 262144 bytes estimated cost]) Joins ----- The driver table was the CLIENTS table The CLIENTS table was joined to the FACTURES table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The FACTURES table was joined to the LIGNFACT table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The LIGNFACT table was joined to the VARIET table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The optimizer attempted to re-order the joins to a more optimal order The optimizer successfully re-ordered the joins into this more optimal order: The driver table was the VARIET table The VARIET table was joined to the LIGNFACT table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The LIGNFACT table was joined to the FACTURES table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The FACTURES table was joined to the CLIENTS table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The following join condition was applied to the LIGNFACT table: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR" [Index scan]) The following join condition was applied to the FACTURES table: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM" [Index scan]) The following join condition was applied to the CLIENTS table: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT" [Index scan]) ================================================================================ 10156 row(s) returned in 37.484 secs ================================================================================ the same query with NOJOINOPTIMIZE: 7.1 s there is a problem with optimiser? jean |
Fri, Nov 9 2007 1:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Jean,
I didn't notice that you have a table included without a join condition (ANNEE). What happens if you remove it ? It is very well possible that the ANNEE table being included at the end of the list of tables is causing a slowdown since it will require one row scan for every join prior to it. BTW, if you want to just send me the database catalog/tables and the original query, I can run it here and tell you exactly what is taking so long. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |