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