Hi Geoff!

There isn't much of a difference between your two statements. Without a
PLAN helping you is a bit like shooting in the dark, but targets have
been visible on this list before, so maybe we will hit something.

If it is a change in the PLAN that was your problem, then Adam have
explained your problem already.

What I do notice, is that the two statements you do show are remarkably
similar and that I would expect them to produce identical plans (I am a
bit uncertain whether newer versions of Firebird may use an index for IS
NOT NULL, but 1.5.3 didn't when I tried a statement that I would expect
to benefit from an index in such a situation).

My wild guess is that something else was causing your slowness. This may
be another long-running query or a SWEEP (something that may indicates
poor handling of transactions). Did you try your original query both
before and after your modified query to ascertain that it wasn't
anything like this?

Set

g.ingram wrote:

> I have two tables which in six months of use have grown to the following
> sizes:
> EXAM - 96,258 records
> EXAM_ITEM - 23,228 records
>
> This query worked fine until today when it started bogging down (over a
> minute to execute):
>
> select distinct
> EXAM_ITEM.EXAM_ID
> from
> EXAM_ITEM
> join
> EXAM on EXAM.EXAM_ID =3D EXAM_ITEM.EXAM_ID
> where
> (EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE =3D 'F')
> and
> (EXAM.REPORT_APPROVED_DATE is not NULL)
> and
> (
> (EXAM.PRI_INSURANCE_CODE <> '' and EXAM_ITEM.PRI_LAST_BILLED is NULL)
> or
> (EXAM.SEC_INSURANCE_CODE <> '' and EXAM_ITEM.SEC_LAST_BILLED is NULL)
> or
> (EXAM.TER_INSURANCE_CODE <> '' and EXAM_ITEM.TER_LAST_BILLED is NULL)
> )
>
> I changed it to the following and performance was restored (less than 1
> second execution):
>
> select distinct
> EXAM_ITEM.EXAM_ID
> from
> EXAM_ITEM
> join
> EXAM on EXAM.EXAM_ID =3D EXAM_ITEM.EXAM_ID
> where
> (EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE =3D 'F')
> and
> (
> (EXAM.REPORT_APPROVED_DATE is not NULL and EXAM.PRI_INSURANCE_CODE <>
> '' and EXAM_ITEM.PRI_LAST_BILLED is NULL)
> or
> (EXAM.REPORT_APPROVED_DATE is not NULL and EXAM.SEC_INSURANCE_CODE <>
> '' and EXAM_ITEM.SEC_LAST_BILLED is NULL)
> or
> (EXAM.REPORT_APPROVED_DATE is not NULL and EXAM.TER_INSURANCE_CODE <>
> '' and EXAM_ITEM.TER_LAST_BILLED is NULL)
> )
>
> Can anyone explain why the second query is faster? Are there any docs
> on Firebird internals (other than the source code [#-o] ) that would
> explain? Or maybe a general text on query optimization? I am concerned
> that I will have to change the query again in six months time.
>
> TIA