Hi,

Knut Anders Hatlen wrote:
ganest <ganest@ced.tuc.gr> writes:

  
I am executing using ij tool (java -Dderby.language.maxMemoryPerTable=4096
-Xms256m -Xmx256m -jar $DERBY_HOME/lib/derbyrun.jar ij) 
the following query: (I read about derby.language.maxMemoryPerTable in this
mailing list)

select count(*) from  big inner join bigref on big.id=bigref.bigid and
big.name like '0ff%';

The result is: 258 and it takes more than 20 seconds to be executed. Using
mysql with the same
configuration the result is produced in milliseconds.
    

For the record, I ran the code you provided on my machine, using only
the default settings for Derby and the JVM, and I see that the query
takes less than 150 ms:

ij> elapsedtime on;
ij> select count(*) from  big inner join bigref on big.id=bigref.bigid and big.name like '0ff%'; 
1          
-----------
255        

1 row selected
ELAPSED TIME = 133 milliseconds

This is with head of the Derby 10.5 branch, OpenSolaris 2009.06 snv_111a
X86, and

java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)

The runtime statistics on my machine are almost identical to the ones
you provided (some small differences in page count and row count due to
the randomness of the code that populates the tables).

  
Obviously the cache was used that is why you take such time. When I run exactly the same query twice I get the same time too.
If you run the same query with a different value in the WHERE expression ( i.e. big.name like '5aa%', etc), It will take 20 -30 seconds

As I said the problem remains. I hoped that it was a tuning issue, but it seems that is an intrinsic problem
related with the join implementation. I like Derby and I am really disappointed, especially when I compare it with mysql.

In any case, thanks again for your answers.

George