Me again,
I am doing some volume testing and I ran across the following
issue. The query I have pasted below runs against two tables. Bern1
having 3730 records, Bern2 having 2987. The query is a bit of a
dummy query but the idea was to mock some of our old test queries
for some time comparisons.

This query executes fine in about .2-.5 seconds using the embeded
version. However, when I alter the identified "AND" condition
to "OR", the query takes anywhere from 12-17 seconds. I ported the
syntax and ran it in SQL Server 2005 and my query times were the
same for both (or very close).

Could this perhaps be an issue with the way FireBird interprets
the "OR'S" in the where clause?

Is there any possible suggestions to how this can be optimized to
avoid such results? In our production environments, worst case
scenario could have a query running against a pair of 30 million
record tables, so 12-17 seconds with such a small set of data raises
concerns.

Has there been anyone in the past do some major volume testing whith
those kinds of numbers before (30 mil). If so, care to share some
results?

Query:

select
count(*)
from
BERN1,
BERN2
where
(BERN1.GENDER = BERN2.GENDER)
AND
(
(
BERN1.DOB_YEAR = BERN2.DOB_YEAR
AND
(
BERN1.DOB_MO = BERN2.DOB_MO OR
BERN1.DOB_DAY = BERN2.DOB_DAY OR
BERN1.DOB_MO = BERN2.DOB_DAY
)
)
AND /* THIS IS THE PROBLEM LINE *****************/
(
(BERN1.DOB_DAY - BERN1.DOB_DAY) < 2 AND
(BERN2.DOB_DAY - BERN1.DOB_DAY) < 2 AND
BERN1.DOB_MO = BERN2.DOB_MO AND
BERN1.DOB_YEAR = BERN2.DOB_YEAR
)
);

Thanks

Darin Amos