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
