Hmm, so all tables except FH may or may not have a matching row. OK,
that seems fine since almost all are using their primary key in the
plan (though I have minimal experience with this many left joins
within one query). But, SUBPROJ uses NATURAL. How many records are
there in this table? Maybe you should create an index for SU_NR and/or
SU_SUB? I also try to always get NATURAL as high up in the plan as
possible, but since you're using PKs all the way, I doubt it matters
in this case.

Set

--- In firebird-support@yahoogroups.com, "Nico Callewaert" wrote:
>
> Hi Pavel,
>
> > That's one more sign that what you see is a server-side caching.
> > If you could post your query, definition script for related
> > metadata and the query execution plan, then probably people in
> > this list could help you with the query improvement (if the
> > improvement is possible at all).
>
> Thanks again for your reply !
> For sure this is something to do with caching, because once the
> query is ONE time executed, it doesn't matter in the application or
> IBExpert, everything is running fast. There are a few triggers and
> lots of stored procedures referencing this table (CSFDPX).
> Here is the query and execution plan :
>
(deleted lots of fields in the select clause since they're 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 = FH.FH_WEDERVERKOPER
> WHERE (FH.FH_CODE = 'B')
>
> Plan
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (FH INDEX (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))