Knut Anders Hatlen wrote:
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.
ganest <email@example.com> 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
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 row selected
ELAPSED TIME = 133 milliseconds
This is with head of the Derby 10.5 branch, OpenSolaris 2009.06 snv_111a
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).
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
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.