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 23:58:07 GMT
Jim Newsham wrote:
> 
> You previously mentioned a Derby property called
> derby.language.maxMemoryPerTable.  I don't see it mentioned in the current
> documentation, but I found a jira issue which contains some proposed
> documentation (http://issues.apache.org/jira/browse/DERBY-1397).  But it's
> still not entirely clear to me.  Is this property only used when deciding
> whether to perform a hash join or nested loop join?  Or does it also
> influence how much of the hash join is kept in memory, or when that hash
> join is spilled to disk?

Great question.  My first thought was that the property *just* determined 
whether or not to perform a hash join.  But I did some looking around in the 
code and was a bit surprised to see that the answer depends on whether or not 
the inner result set of the hash join is a base table.  If the inner result set 
*is* a base table then the maxMemoryPerTable property does in fact determine 
when we will spill to disk.  For any other hash join, though, the determination 
of when to spill over is based on the JVM memory size at the time the in-memory 
hash table is created.

> Essentially, I'd like to know, if using the above query with optimizer 
> overrides which force a hash join, what is the threshold for spilling a 
> hash to disk, and is this a tunable parameter?

Since your queries involve hash joins with subqueries as the inner table, I 
think this means that spill-over to disk occurs when the the size of the 
in-memory result set reaches or exceeds one hundredth of the *current* memory in 
the JVM.  In the code this is coded as:

   Runtime.getRuntime().totalMemory()/100;

Thus for your case, there does not appear to be a way to tune the threshold for 
spilling the materialized subquery rows to disk. At least, no direct way; you 
can of course change the start and max heap size for the JVM, which indirectly 
affects the spill-over threshold.  But I'm not sure that's what you were looking 
for...

Army


Mime
View raw message