At 01:03 PM 6/09/2007, you wrote:

>Hi all,
>
> I just finished my work to migrate from IB to FB. Everything
> works fine except that the query that usually runs quickly, now
> still runs very-very slowly.
>
> This is the query
> select t.id_db, t.stp_frm, t.brk_typ, t.crt_n, t.id_frm, t.cncl,
> t.nml, t.old_nml, t.old_int_amnt, t.nml_intr_brk, t.crrny,
> t.trs_dt, t.int_amnt, t.trsfr_nml_ac, t.trsfr_nml_bnk,
> t.fnl_mt_dt, t.trsfr_nml_nm, t.is_new
>from t_db t
>where t.crrny = 'Rp'
>and t.IS_PBLSH = 'T'
>and t.stp_frm in ('S','B')
>and (t.cncl is null or t.cncl <> 'T' or (t.cncl='T' and t.is_new =
>'T' and t.stp_frm='S'))
>and t.id_db not in (select a.id_frm from t_db a
> where a.id_frm = t.id_db
> and a.id_db in (select id_db from t_cst_trsfr2
> where trsfr_typ in('S', 'B')))
>and t.id_db not in (select b.id_db from t_cst_trsfr2 b
> where b.trsfr_typ in('S', 'B'))
>
> The last two lines statement ->'and t.id_db not in (select
> b.id_db from t_cst_trsfr2 b where b.trsfr_typ in('S', 'B'))',
> caused this delay. Without this 2 lines, query runs quickly.

The reason for this slowness is that Fb 2 corrects a logical error
that is in InterBase NOT IN() processing and was in Firebird <=
1.5.x. Now, NOT IN() gives correct results (unlike before) but it
does it slowly. From the point of view of efficiency, you should try
to avoid NOT queries if there is another logical path to the result
set you want, but particularly NOT IN(), since its logic is actually
a lot more complex than it seems.

If the NOT search is unavoidable then this close equivalent, which
will use an index, is probably close enough if you were satisfied
with the results you got in IB:

and not exists (
select 1 from t_cst_trsfr2 b
where b.id_db = t.id_db
and b.trsfr_typ in('S', 'B'))

Helen