I run into this all the time. I find it is much more effecient to
use a join like this:

select
di2.ID,di2.NDC,di2.FRMTD_NDC,di2.UPC_HRI,di2.FRMTD_MDDB_UPC_HRI,di2.GP
I,di2.STATUS,di2.NAME,di2.LABELNAME
from mddb_drugitem d1
join MDDB_DRUGITEM di2 on di2.NDC = di1.ndc and
d1.upc_hri is not null and
d1.ndc is not null
group by
d1.ndc
having
count(*) > 1


Although whether this will work properly with the "having count(*) >
1" condition I don't know.


--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...>
wrote:

> The times listed are what it takes to run each query separately.

If I

> run it as one query, I kill it after several minutes.
> The plan is natural for all querys. The only thing I can think of

is

> that FB is evaluating the IN statement for every row.
>
> -- 0.781s
> select
>
>

di2.ID,di2.NDC,di2.FRMTD_NDC,di2.UPC_HRI,di2.FRMTD_MDDB_UPC_HRI,di2.GP
I,

> di2.STATUS,di2.NAME,di2.LABELNAME
> from
> MDDB_DRUGITEM di2
> where
> di2.NDC in
> (
> -- 0.561s
> select
> d1.ndc
> from
> mddb_drugitem d1
> where
> d1.upc_hri is not null and d1.ndc is not null
> group by
> d1.ndc
> having
> count(*) > 1
> )