db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: slow subqueries
Date Sat, 11 Nov 2006 20:07:18 GMT


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



Mime
View raw message