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
begin:vcard
fn:David Van Couvering
n:Van Couvering;David
org:Sun Microsystems, Inc.;Database Technology Group
email;internet:[EMAIL PROTECTED]
title:Senior Staff Software Engineer
tel;work:510-550-6819
tel;cell:510-684-7281
x-mozilla-html:TRUE
version:2.1
end:vcard
