Hi Set,

This is also frustrating sometimes. There are quite a lot of rows in
SUBPROJ. I have a unique index defined on the fields SU_NR and
SU_SUB. But it seems firebird is refusing to use that index. I
don't like really so many left joins, but the customer wants to see
al the related information in the grid.

Thanks !
Nico

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> 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))
>