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

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

Because you are doing it a more efficient way than Firebird does.
Firebird will take around

0.8 seconds + (0.5 seconds times the number of records in
mddb_drugitem).

So unless this table is very small, you would do better to rewrite
the query using either a more efficient subselect (you could most
likely use an exists which is much better), or by joining to itself.
I am pretty sure you can do this a lot quicker but I am not going to
spend time guessing at table structures.

Adam

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