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

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

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?

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

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

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