Hi Pavel,

I replaced some LEFT OUTER JOINS with JOIN statements (I guess a JOIN
is the same as a INNER JOIN), and that improves performance.

Thank you !
Nico

--- In firebird-support@yahoogroups.com, Pavel Menshchikov
<developer@l...> wrote:

>
> Hello Nico,
>
> NC> I don't like really so many left joins, but the customer wants

to see

> NC> al the related information in the grid.
> Can you revise all left-joined datasets and find out if you could

use

> inner joins for some of them? You may use inner join when there

always

> exists a matching record(s) in a joining dataset for a record in FH
> dataset. If you replace some of outer joins with inner joins, put as
> many inner joins as possible before left outer joins.
>
> >> > Here is the query and execution plan :
> >> >
> >> (deleted lots of fields in the select clause since they're
> NC> irrelevant)
> >> > SELECT
> >> > FH.FH_UNIEK,
> >> > KL.KL_COD,
> >> > PR.PR_NR,
> >> > SU.SU_SUB,
> >> > VE.VE_OMS,
> >> > WE.WE_EMAIL,
> >> > TR.TR_EMAIL
> >> > FROM CSFHPX FH
> >> > LEFT OUTER JOIN KLPX KL ON KL.KL_COD = FH.FH_KLNR
> >> > LEFT OUTER JOIN PROJPX PR ON PR.PR_NR = FH.FH_PROJ
> >> > LEFT OUTER JOIN SUBPROJ SU ON (SU.SU_NR = FH.FH_PROJ)
> >> > AND (SU.SU_SUB = FH.FH_SUBPROJ)
> >> > LEFT OUTER JOIN VERTEW VE ON VE.VE_COD = FH.FH_AGT
> >> > LEFT OUTER JOIN TRANSPORTEUR TR ON TR.TR_CODE =

FH.FH_TRANSPORTEUR

> >> > LEFT OUTER JOIN WEDERVERKOPERS WE ON WE.WE_COD =
> NC> FH.FH_WEDERVERKOPER
> >> > WHERE (FH.FH_CODE = 'B')
> >> >
> >> > Plan
> >> > PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (FH INDEX
> NC> (FH_CODE_INDEX),KL
> >> > INDEX (RDB$PRIMARY36)),PR INDEX (RDB$PRIMARY55)),SU

NATURAL),VE

> >> > INDEX (RDB$PRIMARY76)),TR INDEX (RDB$PRIMARY167)),
> >> > WE INDEX (RDB$PRIMARY292))
>
>
> --
> Best regards,
> Pavel Menshchikov
> http://www.ls-software.com
>