db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jonathan Eric Miller" <jemil...@uchicago.edu>
Subject Re: Thread on Derby on The Server Side
Date Tue, 09 Aug 2005 18:27:04 GMT
>From what I remember, I found that if you're doing batch updates, using JDBC 
batch updates improves performance greatly with Derby. Using 
PreparedStatements alone was much slower than other DBMSs such as MySQL or 


----- Original Message ----- 
From: "David Van Couvering" <David.Vancouvering@Sun.COM>
To: "Derby Discussion" <derby-user@db.apache.org>
Sent: Tuesday, August 09, 2005 11:24 AM
Subject: Re: Thread on Derby on The Server Side

> Thanks, Sunitha, for looking into this!  It would be *great* if you
> posted this to the server side in response to the "Derby is slow"
> judgement out there.   If for whatever reason you are uncomfortable
> doing this, let me know, and I'll be happy to do it with your OK.
> These seem to be the two most common mistakes: assuming a database is
> transactional when it's not really (doesn't sync to disk) and not
> preparing statements. We ran into this at Sun as well with a group doing
> performance tests of Derby.
> We should have a "cheat sheet" for things people should look at when
> doing internal benchmarking of Derby and make sure it's front and center
> on our web site...
> David
> Sunitha Kambhampati wrote:
>> 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

View raw message