db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Re: slow subqueries
Date Mon, 13 Nov 2006 17:45:40 GMT
Army wrote:

> In order to get Derby to recognize the equijoin predicate, the subquery 
> has to appear in the FROM list.  So I created a view for the subquery:

<snip view>

> and then I changed the query to select and join with that view:

<snip>

> Working off the modified query above I used "optimizer overrides", which 
> were added as part of Derby 10.2, to 1) fix the join order so that the 
> subquery becomes the "inner" table, and 2) to force ("guarantee") a hash 
> join using the equijoin predicate, thereby allowing materialization of 
> the inner table (i.e. of the subquery).  The query to do that is as 
> follows:
> 
> select count(*) from --DERBY-PROPERTIES joinOrder=FIXED
>   time, v_hash --DERBY-PROPERTIES joinStrategy=HASH
>   where time.id = v_hash.id;

Just one thing to mention as an afterthought: you do not have to create a 
separate view for this to work.  I did that for the sake of clarity, but you 
could just as easily write:

select count(*) from --DERBY-PROPERTIES joinOrder=FIXED
   time,
   (select distinct
     time.id from time, double_sample, band
    where band.id = double_sample.fk_band_id
      and double_sample.fk_time_id = time.id and
      band.id in (11, 12)
   ) v_hash --DERBY-PROPERTIES joinStrategy=HASH
   where time.id = v_hash.id;

Of course after writing all of that I realized that you said your queries are 
generated (as opposed to hand-written) and that they "are only representative of 
my problem. [...] While the above sample query can be easily modified to run 
well, I'm not sure how to do it in the general case."

So maybe this isn't going to work for you, after all...

Army


Mime
View raw message