This sql is very slow! (19,89 seconds returning only one regisro

THANKS!

================================================================================
SQL statement (Executed with 4.23 Build 2)
================================================================================

SELECT M.ID AS CODIGO, M.DESCRICAO AS MATERIAL,L.QTDE,LT.CONTROLE AS CONSUMO
FROM LISTA_PRE_DEFINIDA L

LEFT OUTER JOIN TIMES_TECNICOS TT ON
(L.FKTIME = TT.ID)

LEFT OUTER JOIN MATERIAIS M ON
(L.FKMATERIAL = M.ID)

LEFT OUTER JOIN LIMITES_TIMES LT ON
(L.FKMATERIAL = LT.FKMATERIAL AND TT.ID = LT.FKTIME)

WHERE (TT.ID = 203)

ORDER BY CODIGO,MATERIAL

Tables Involved
---------------

LISTA_PRE_DEFINIDA (L) table opened shared, has 215 rows
TIMES_TECNICOS (TT) table opened shared, has 86 rows
MATERIAIS (M) table opened shared, has 154 rows
LIMITES_TIMES (LT) table opened shared, has 13244 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

CODIGO ASC
MATERIAL ASC

WHERE Clause Execution
----------------------

The expression:

TT.ID = 203

will be applied to each candidate row in the result set as the result set is
generated due to the TIMES_TECNICOS table (TT) being the target of an OUTER join

Join Ordering
-------------

The driver table is the LISTA_PRE_DEFINIDA table (L)

The LISTA_PRE_DEFINIDA table (L) is joined to the TIMES_TECNICOS table (TT)
with the LEFT OUTER JOIN expression:

L.FKTIME = TT.ID

The LISTA_PRE_DEFINIDA table (L) is joined to the MATERIAIS table (M) with the
LEFT OUTER JOIN expression:

L.FKMATERIAL = M.ID

The TIMES_TECNICOS table (TT) is joined to the LIMITES_TIMES table (LT) with
the LEFT OUTER JOIN expression:

L.FKMATERIAL = LT.FKMATERIAL AND TT.ID = LT.FKTIME

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

L.FKTIME = TT.ID

is OPTIMIZED

The expression:

L.FKMATERIAL = LT.FKMATERIAL AND TT.ID = LT.FKTIME

is UN-OPTIMIZED

The expression:

L.FKMATERIAL = M.ID

is OPTIMIZED

================================================================================
>>>>> 1 rows affected in 19,89 seconds
================================================================================

Roger


First stage is to make sure all the tests are as optimised as possible. The query plan tells you

The expression:

L.FKMATERIAL = LT.FKMATERIAL AND TT.ID = LT.FKTIME

is UN-OPTIMIZED

so check which of those fields doesn't have an index.

Roy Lambert

Roger Oliveira <pikurim@hotmail.com> wrote in
news:84468571-54DD-4AE8-BF23-DBCD1C7CAFD6@news.elevatesoft.com:

>
>  This sql is very slow! (19,89 seconds returning only one regisro
>
>  THANKS!
>
>========================================================================
>========
> SQL statement (Executed with 4.23 Build 2)
>========================================================================
>========
>
> SELECT M.ID AS CODIGO, M.DESCRICAO AS MATERIAL,L.QTDE,LT.CONTROLE AS
> CONSUMO FROM LISTA_PRE_DEFINIDA L
>
> LEFT OUTER JOIN TIMES_TECNICOS TT ON
> (L.FKTIME = TT.ID)
>
> LEFT OUTER JOIN MATERIAIS M ON
> (L.FKMATERIAL = M.ID)
>
> LEFT OUTER JOIN LIMITES_TIMES LT ON
> (L.FKMATERIAL = LT.FKMATERIAL AND TT.ID = LT.FKTIME)
>
> WHERE (TT.ID = 203)
>
> ORDER BY CODIGO,MATERIAL

What about:

SELECT M.ID AS CODIGO, M.DESCRICAO AS MATERIAL,L.QTDE,LT.CONTROLE AS
CONSUMO
FROM TIMES_TECNICOS TT

JOIN LISTA_PRE_DEFINIDA L ON TT.ID = L.FKTIME

LEFT OUTER JOIN MATERIAIS M ON
(L.FKMATERIAL = M.ID)

LEFT OUTER JOIN LIMITES_TIMES LT ON
(TT.ID = LT.FKTIME AND L.FKMATERIAL = LT.FKMATERIAL)

WHERE (TT.ID = 203)

ORDER BY CODIGO,MATERIAL

I've removed the LEFT OUTER JOIN with TIMES_TECNICOS since you only want
one definite value from it, and I've made it the "Driver table".

You will also need the missing index Roy mentioned on LT.FKMATERIAL or
LT.FKTIME as an UNOPTIMISED join on the largest table is probably the
most important thing.

--
Chris

Chris,

<< I've removed the LEFT OUTER JOIN with TIMES_TECNICOS since you only want
one definite value from it, and I've made it the "Driver table".

You will also need the missing index Roy mentioned on LT.FKMATERIAL or
LT.FKTIME as an UNOPTIMISED join on the largest table is probably the most
important thing. >>

Nice job Roy and Chris.  Any more of this and I'll have to take a vacation
and just pay you guys.

--
Tim Young
Elevate Software
www.elevatesoft.com

THANKS!!! I'VE CREATE A INDEX FKMATERIAL AND FKTIME AND THIS WORKS!

ROGER.