db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: Thread on Derby on The Server Side
Date Tue, 09 Aug 2005 06:52:08 GMT
I took a quick look at the benchmark at polepos that is mentioned in the 
thread on theserverside , specially the bahrain test.  This test uses 
Statements instead of PreparedStatement for the select queries.  ( see 
BahrainJdbc.java where select queries are like  "select * from bahrain 
where LicenseID=" + i    

One  important performance tip is to use PreparedStatement with dynamic 
markers instead of Statements.   This can show considerable improvements.
If you use Statement with literal values as in the case above, that will 
involve derby having to compile a query plan for each of the statements 
which affects performance.  Using PreparedStatement with dynamic markers 
('?') avoids unnecessary compilation cost , and thus faster.

So for example - if you are executing the query above(in 1st para) with 
different LicenseID values,  using a Statement will involve compilation 
cost for each statement executed. But in case of PreparedStatement,  the 
query would be  ' select * from bahrain where LicenseID=?" and the 
statement is compiled only the first time or when the optimizer thinks 
the plan is stale.

Check out the tuning manual for more details :- 
http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804

---------
Also I think, sometimes default behaviors of databases are different and 
this can lead to different performance out of the box - some key factors 
such as the default isolation level, I/O syncs on a commit or not. When 
I looked at the docs for HSQLDB and Pointbase last, I saw that by 
default they dont force a sync call to the disk on a commit. 

Sunitha.

ps: There are some more differences documented in  Derby-110 comments, 
and also in Dan's blog entry. 
http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397

>
> Daniel Noll wrote:
>
>> David Van Couvering wrote:
>>
>>> Note there are suggestions Derby is slow.  If anyone wants to chime 
>>> in on some of this, that would be great.  We don't want the "word" 
>>> to get out that Derby is slow if it's based on poorly designed 
>>> benchmarks or misconceptions...
>>>
>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
>>
>>
>>
>> Derby does have an inexplicable 8-12 second wait when creating or 
>> opening a database where HSQLDB has no noticeable wait, which doesn't 
>> hurt on a server but on a desktop client it's a bit of a problem.  It 
>> really makes our unit tests grind too, since we use a fresh database 
>> for each test case.
>>
>> But that being said, at runtime (which is normally what matters) we 
>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
>>
>> Maybe someone has some quantitative figures though, instead of these 
>> qualitative experiences.
>>
>> Daniel
>>


Mime
View raw message