db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George Anestis <gan...@ced.tuc.gr>
Subject Re: Performance Tuning Problem ?
Date Sat, 23 May 2009 12:56:50 GMT
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

Mime
View raw message