--- 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.GPI,
> 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
> )

The IN predicate means that the entire subselect must be run. In this
case, you should be able to change it to an EXISTS, which will return
as soon as it has found a single record. If the subselect has a large
set, this could speed it up significantly.

If my brain was switched on, I could probably reverse engineer your
table structure and the aim of your query and try and come up with
something better, but sadly it is too early in the morning (here). If
you want to post the basic fields. Actually the subselect is using the
same table, so I am guessing you are after duplicates or something
like that, but let us know if these tips didn't solve your problem and
we can take a deeper look.

Adam