db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: slow subqueries
Date Mon, 13 Nov 2006 14:04:37 GMT
This is an "ugly" work around to an existing bug.

AFAIK, the problem was when you used an IN clause in the inner select.
This is a known derby bug.

Going to a temp table, you probably won't see any improvement, and you don't
have the ability to put an index on the temp table. Unless you're returning
over 10K rows on the inner query, a simple table scan would be more
efficient. (At least for IDS et al. YMMV for Derby.)

> -----Original Message-----
> From: Jim Newsham [mailto:jnewsham@referentia.com]
> Sent: Saturday, November 11, 2006 2:07 PM
> To: 'Derby Discussion'
> Subject: RE: slow subqueries
> > > This is why a nested loop is not going to work here... 20,000 squared
> > > operations is very expensive, let alone millions squared.  For a query
> > with
> > > this profile, the inner query should only be executed once.
> >
> > Perhaps you can get the behavior you desire by explicitly creating
> > a temporary table, selecting the data from your inner query into
> > the temporary table, then re-writing your main query to join against
> > the temporary table?
> I'd be willing to try that.  Some questions come to mind:
> - Is there a way in sql to select the results of a query into a table?
> - When I'm done with the temp table, can I just drop it as for a normal
> table?
> - I think I read somewhere that I can't add an index to temporary tables.
> Is this true?  I can't even have a primary key?  If so, I think the
> performance will still be intractable for thousands or millions of results
> in the temp table.
> Thanks,
> Jim

View raw message