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 
MSSQL.

Jon

----- 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
>>>>
>>
> 


Mime
View raw message