--- In firebird-support@yahoogroups.com, "Ed Dressel" <Dressel@...> wrote:

>
> > Not like that! :-)
>
> That's why I asked :-)
>
> > Tip: unless you have to save white space, it is a REALLY good idea
> > to lay out your multi-table statements in a way that makes the
> > linkages obvious. Even if you don't care, you *might* retire some
> > day and bequeath your code to someone else. :-)
>
> totally agree, but I've never seen an join that is easy to read, I
> generally stay with joins in the where clause.
>
> I got it to work, kinda. Here's my SQL:
>
> select Count(*)
> from clientinfo C
> left join PersonInfo PE
> on C.Client_ID = PE.Client_ID
> full join PaycheckInfo Pay on PE.Person_ID = Pay.Person_ID
> where PE.person_type_id = 0
> and Pay.sequence = 1
>
> It returns 2,681 records--and takes 110 seconds to run. If I change

it to:

>
> select Count(*)
> from clientinfo C
> left join PersonInfo PE
> on C.Client_ID = PE.Client_ID
> where PE.person_type_id = 0
>
> it returns 2,744 records and only takes 31 ms to run.
>
> Questions:
>
> (1) why doesn't it return the same number of records? That is what I
> need. (I need a field value from the PaycheckInfo table for every
> record in ClientInfo, but the record in PersonInfo and in PaycheckInfo
> may not exist)

They are two different queries. In the first query, you are
eliminating records where Pay.Sequence <> 1 or where Pay.Sequence is null.

You do realise that if you put conditions on the right side of a left
join in the where clause, you turn the left join into an inner join.
Records in ClientInfo which do not have a corresponding PersonInfo can
not be included anymore, because PE.person_type_id = 0 would resolve
to false (it is null for such records and 0 is distinct from null)

My guess is you mean something like this:

select Count(*)
from clientinfo C
left join PersonInfo PE on (C.Client_ID = PE.Client_ID)
full join PaycheckInfo Pay on (PE.Person_ID = Pay.Person_ID and
PE.person_type_id = 0 and Pay.sequence = 1)

Brackets are optional but I find them helpful to understand which
conditions form part of the join rule and which form part of the set
rule. The conditions about person_type_id and sequence do not make
sense if there is no PersonInfo record, which is what the above query
captures.

>
> (2) The plan for the first one is
>
> Plan
> PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))
>
> Adapted Plan
> PLAN JOIN (PAY NATURAL,JOIN (C NATURAL,PE INDEX (PERSONINFO_CT1)))
>
> What causes it to take so long? Paycheck.Client_Id is a FK,
> (Person_ID; Sequence) is indexed. I added an index to just Sequence
> but that did not speed anything up. What should I look at?
>
> Thanks
>

Make the above change to the query and see if the PLAN changes.

Adam