Yes, I'm looking for duplicates in data sent by a third party. This is
a one time analysis, so I just took the results from the IN query and
cut/paste them into the main query.

The reason for the original question was why FB would take so long to
execute the combined statement, if the individual statements were
subsecond.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Friday, September 09, 2005 6:54 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Subselect causes slow query

--- 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
> )

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






------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links