Hi,

I have following problem - under FB 2 some our queries are slower then
under FB 1.5. When I analyzed queries, I found out they have slightly
different plans, but more important - in section where are correlated
subqueries together with easy conditions against main table, in FB 1.5
easy conditions are probably distributed into JOIN section while in FB2
under some circumstances not.

My query is quite more complex, but for illustratin - conditions
(A.Confirmed = 'A' ) AND (A.Closed = 'N' ) reduced 147 000 records from
Table1 A into something arround 2000 records.
=================================================================================================================
SELECT A.ID FROM
Table1 A
JOIN Table2 B ON B.ID=A.Firm_ID
WHERE

A.Firm_ID IN (SELECT ID FROM Table2 WHERE ID='xxxxxxxxxx' OR
Firm_ID='xxxxxxxxxx')
AND (A.DocQueue_ID IN ('3000000101','K400000101') )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )

Now in performance analyse I see 147 000 fetches from Table2 - suppose
because of correlated subquery is run always for each row of table1
=================================================================================================================
SELECT A.ID FROM
Table1 A
JOIN Table2 B ON B.ID=A.Firm_ID
WHERE
(A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND A.Firm_ID IN (SELECT ID FROM Table2 WHERE ID='xxxxxxxxxx' OR
Firm_ID='xxxxxxxxxx')
AND (A.DocQueue_ID IN ('3000000101','K400000101') )

Now when I just moved conditions closely to begin of WHERE section in
performance analyse I see 2000 fetches from Table2 - suppose because of
conditions
(A.Confirmed = 'A' ) AND (A.Closed = 'N' ) are propagated into JOIN
section, so correlated subquery is run only for rest of rows after JOIN
is complete.
Same result as above (better performance) I will reach when I will
remove condition (A.DocQueue_ID IN ('3000000101','K400000101') - has
something to do with IN ??
or when I will use LEFT JOIN instead JOIN.
As I told, FB 1.5 didn't have this problem and probably propagated
conditions(if I am guessing right about this behavior) into JOIN section
independetly on how
WHERE section was constructed.
But In my original more complex query even move conditions to begin of
WHERE will not help - still there is more fetches from corelated
subqueries one could expect.
Only thing that helped me there was use of LEFT JOIN.
=================================================================================================================

So can somebody advice me something around this problem ?

thanks,
regards tomas