Hi,

I have an optimization concern with the 1.5.2.4731 Firebird Release. I have
tested the same query on 1.0.3, 1.5.1.4481, and 1.5.2.4731.

* Variables in the WHERE clauses are all indexed Integers.
* I consider that each of the WHERE Clauses tested below are equivelent
statements.

Here are my results...

"WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>2)"
1.0.3 Execution Time: 175 ms Fetch Time: 187 ms
1.5.1 Execution Time: 109 ms Fetch Time: 144 ms
1.5.2 Execution Time: 39840 ms Fetch Time: 26361 ms ***

"WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>=3)"
1.0.3 Execution Time: 176 ms Fetch Time: 188 ms
1.5.1 Execution Time: 134 ms Fetch Time: 99 ms
1.5.2 Execution Time: 2712 ms Fetch Time: 1854 ms ***

"WHERE STATUS=1 AND (PAYTYPE!=2)"
1.0.3 Execution Time: 174 ms Fetch Time: 190 ms
1.5.1 Execution Time: 141 ms Fetch Time: 95 ms
1.5.2 Execution Time: 96 ms Fetch Time: 81 ms

I have attached the results from InterBase PLANalyzer 1.0.0.47

The whole query is:
SELECT .....
FROM MBPAYMENT D
LEFT JOIN MBPAYMENTDATA P ON D.PAYMENTKEY=P.PAYMENTKEY LEFT JOIN
MBUSERPAYMENT U ON U.PAYMENTKEY=P.PAYMENTKEY LEFT JOIN MBPROVIDERITEM L ON
L.PROVIDERITEMKEY=P.PROVIDERITEMKEY
WHERE ... (as shown above)


The number of reads for each join is very different for 1.5.2.4731. But
they are the same for 1.0.3 and 1.5.1.

Regards,

Chris
TransActive Systems

--
Outgoing mail has been virus checked but TransActive Systems do not warrant
that this email is free of viruses that may have passed through our anti
virus software undetected.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 6/09/2005




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