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 = EXAM_ITEM.EXAM_ID
where
(EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE = '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 = EXAM_ITEM.EXAM_ID
where
(EXAM_ITEM.ZERO_BALANCE is NULL or EXAM_ITEM.ZERO_BALANCE = '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




[Non-text portions of this message have been removed]