At 09:26 PM 25/10/2005 -0700, you wrote:

>I have a stored procedure which worked well under IB5.6 but now runs for an
>interminable time under FB1.5. The goal is to join all the lines of a new
>'document' with a previous document so that changes can be calculated,
>including the recognition of document lines that the previous and current
>document do not have in common. I'm using a self-join on a table. So the
>problematic SQL looks like this:
>
>select
> h0.HSD_DOC_ID,
> h0.SEC_ID,
> h0.HLDG_SHARES,
> h0.HLDG_MKT_VALUE,
> h0.HLDG_PAR_VALUE,
> h0.HLDG_SEQNO,
> h1.HSD_DOC_ID,
> h1.SEC_ID,
> h1.HLDG_SHARES,
> h1.HLDG_MKT_VALUE,
> h1.HLDG_PAR_VALUE,
> h1.HLDG_SEQNO
> from
> holdings h0 full join
> holdings h1 on h0.sec_id=h1.sec_id
> where
> (
> h0.hsd_doc_id=:IPREV_DOC_ID or
> h0.hsd_doc_id is null
> )
> and
> (
> h1.hsd_doc_id=:ADOCID
> )
>
>
>It seems like under FB1.5 the whole cross-product of the join is being
>formed and then the 'where' conditions are applied, which is probably the
>correct implementation of the SQL standard, but it worked well under IB5.6
>and is now unusable for performance reasons when FB1.5. Can anyone tell me
>how the SQL processing has changed between IB5.6 and FB1.5 relative to this
>query?

No, I can't; but the optimizer changes did affect performance for some
outer joins in a way that's related (I think) to your analysis. Hopefully
Arno is watching and can explain the cause.

The workaround may be to push one or both of the search criteria up into
the ON clause to force the restriction(s) to occur in a lower-level stream.

>This is in a stored procedure and I'm already starting to think of
>alternatives to get the same result, but any implementation suggestions are
>welcome.

It would always be my choice, regardless of the presence or otherwise of
poor optimisation, to use an explicit cursor rather than mess around with a
re-entrant set, sweeping searches and the potential for duplicates. You can
avoid the re-entrant join by nesting a second cursor inside the outer one
to search for the other doc_id's. Using the explicit cursor allows you to
perform an UPDATE...WHERE CURRENT OF... operation in the loop and saves a
lot of processing energy.

./heLen