lec_sas wrote:

> 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
>

Hi,Saw the same thing here... my testquery looks a lot like yours and I
get very different execution times if I fiddle around AND / OR with the
same logical result. It seems FB (I'm using 1.5.2 because my ISP has
that one running) is changing the evaluation-order, so that "cheaper"
evaluation formulas that would cause the whole formula to evaluate to
False in a early stadium to the right-handside... this causing the
evaluation to take a lot more time.

Example:

SELECT FIELDS FROM TABLE
WHERE CondA
And
(CondB OR CondC OR CondD)
And
CondE
And
CondF

Took abt 5 secs, CondE and CondF are the most expensive

Changed to:

WHERE (CondA
And
(CondB OR CondC OR CondD)
And
CondE
And
CondF)
OR
(1=0)

Now it took abt 0.5 secs...

Note: usually the OR (1=0) is used to avoid using indices. But there are
no indices in my table... Adding the OR seemes to mix up the
evaluation-order. If I do

WHERE (CondB OR CondC OR CondD)
And
CondE
And
CondF
And
CondA)
OR
(1=0)

which logically changes nothing, but the query was fast again... 0.5 secs.
So the question is: where to put the condition that evaluates the whole
formula to false? At the beginning? At the end? Or just try?

Kind regards,
John