Sounds like a challenge! ;-)
I don't develop derby but I thought it might be entertaining to see if
I could improve the performance of derby with the h2 benchmarks.
I only focused on the embedded databases.
Results from website
h2: 16312
derby: 131704
The first thing I did was run the benchmarks with the latest version
of h2, and derby. My results were a bit different than the posted
results.
Results summary
h2: 17844
derby: 85531
I looked at some of the code to see what was going on and I noticed
that for a lot of the benchmarks that autocommit mode was on. I made a
slight modification so that openConnection automatically turns
autocommit mode off. I also made it so that closeConnection does a
commit.
Autocommit = false summary
h2: 17063
derby: 66077
The next thing I noticed was the h2 wasn't using durable operations.
There is a special mode in derby to allow a weaker durability setting
(it still writes more frequently than h2). I just added this before
any of the benchmarks where ran:
System.setProperty("derby.system.durability", "test");
Durability off summary
h2: 18796
derby: 45499
Results for all the embedded databases (more recent versions of all
databases with my minor changes to the benchmarks):
h2: 17437
hypersonic: 37469
derby: 44704
If I knew more about derby I'm sure I could get it running faster.
Brish
Thanks for your mail!
> Sounds like a challenge! ;-)
Sure!
> I don't develop derby but I thought it might be entertaining to see if
> I could improve the performance of derby with the h2 benchmarks.
That's good! So far nobody from Derby took the time to see why Derby is slow.
> I only focused on the embedded databases.
I think that's the main usage of H2 and Derby.
> The first thing I did was run the benchmarks with the latest version
> of h2, and derby. My results were a bit different than the posted
> results.
Yes, I also found out the newest version of Derby is faster. The new
results will be posted in the next release.
> Results summary: h2: 17844, derby: 85531
On my machine, I got: H2: 14687, Derby: 87111; see also:
http://h2database.googlecode.com/svn/trunk/h2/src/docsrc/html/performance.html
> a lot of the benchmarks that autocommit mode was on.
Yes, that's what I expect most people use.
> I made a slight modification so that openConnection automatically turns
> autocommit mode off. I also made it so that closeConnection does a
> commit.
> Autocommit = false summary; h2: 17063; derby: 66077
While I don't think that this is a valid use case for a database, I
will add this to the Derby documentation:
"A few problems have been identified: Leaving autocommit on is a
problem for Derby. If it is switched off during the whole test, the
results are about 20% better for Derby."
> The next thing I noticed was the h2 wasn't using durable operations.
Yes, Derby does not use durable operations as well. Derby uses
RandomAccessFile(.., "rws" or "rwd"), this is not durable. It does not
call FileDescriptor.sync() or FileChannel.force() for each commit.
See also: http://www.h2database.com/html/advanced.html#durability_problems
> There is a special mode in derby to allow a weaker durability setting
> (it still writes more frequently than h2)
> System.setProperty("derby.system.durability", "test");
I'm not sure if it really writes more frequently, how did you test
that? I know about this mode. As far as I have read the docs, it says
'don't use except for testing, otherwise you got a corrupted database
very quickly'. So this seems too dangerous. However if Derby would
implement 'soft commits' like MySQL, PostgreSQL and H2, I would use
that.
> If I knew more about derby I'm sure I could get it running faster.
I'm sure it can be made faster, but in my view it is up to the Derby
develpers to change the default settings to make it faster.
In any case, I think it would be good if Derby would write an open
source benchmark where they are faster than another database, if this
is possible. They do have a benchmark where they compare embedded
Derby against client/server MySQL (as far as I know), and made
presentations at ApacheCon and JavaOne. I asked for the source code
but they said it is confidential.
Thomas
... snip ...
> > I don't develop derby but I thought it might be entertaining to see if
> > I could improve the performance of derby with the h2 benchmarks.
>
> That's good! So far nobody from Derby took the time to see why Derby is slow.
But they sped it up with the current release?! It appears they are
looking at performance.
... snip ...
> > I made a slight modification so that openConnection automatically turns
> > autocommit mode off. I also made it so that closeConnection does a
> > commit.
> > Autocommit = false summary; h2: 17063; derby: 66077
>
> While I don't think that this is a valid use case for a database, I
> will add this to the Derby documentation:
Having autocommit on isn't the normal case.
> > The next thing I noticed was the h2 wasn't using durable operations.
>
> Yes, Derby does not use durable operations as well. Derby uses
> RandomAccessFile(.., "rws" or "rwd"), this is not durable. It does not
> call FileDescriptor.sync() or FileChannel.force() for each commit.
>
> See also:http://www.h2database.com/html/advanced.html#durability_problems
It does use force. See:
> > There is a special mode in derby to allow a weaker durability setting
> > (it still writes more frequently than h2)
> > System.setProperty("derby.system.durability", "test");
>
> I'm not sure if it really writes more frequently, how did you test
> that? I know about this mode. As far as I have read the docs, it says
> 'don't use except for testing, otherwise you got a corrupted database
> very quickly'. So this seems too dangerous. However if Derby would
> implement 'soft commits' like MySQL, PostgreSQL and H2, I would use
> that.
Derby does IO for commits. H2 delays IO for commits for one second.
This gives h2 really impressive looking numbers because it can do many
commits with a single write but at the cost of durability.
According to the documentation this is what the setting does:
derby.system.durability
Function
This property changes the default durability of Derby to improve
performance at the expense of consistency and durability of the
database. The only valid supported case insensitive value is test. If
this property is set to any value other than test, this property
setting is ignored. When derby.system.durability is set to test, the
store system will not force I/O synchronization calls for:
* the log file at each commit.
* the log file before a data page is forced to disk.
* page allocation when a file is grown.
* for data writes during checkpoints.
Derby still does IO operations but it won't force syncs so there are
chances that it can't recover.
Derby in this mode is still more reliable than h2.
How can I consider the H2 benchmark valid if you disable durability on
every database that supports it except Derby?
> > If I knew more about derby I'm sure I could get it running faster.
>
> I'm sure it can be made faster, but in my view it is up to the Derby
> develpers to change the default settings to make it faster.
They are. See the latest version performance improvements.
> In any case, I think it would be good if Derby would write an open
> source benchmark where they are faster than another database, if this
> is possible. They do have a benchmark where they compare embedded
> Derby against client/server MySQL (as far as I know), and made
> presentations at ApacheCon and JavaOne. I asked for the source code
> but they said it is confidential.
Why not just have a standard open source benchmark for databases
period?
Like this one:
http://polepos.sourceforge.net/
I noticed that as the database size grows the difference in
performance between h2, and derby shrinks. This probably has something
to do with all the data for the benchmarks fits into the h2 cache but
not in the derby cache. As the size is increased both h2, and derby
are forced to read the data from the disk so they are on more even
ground.
After looking at how both databases work I think it all boils down to
one thing. Disk IO is more expensive than ram access.
H2 sacrifices durability for performance. The closest thing Derby
supports for non-durability is not calling sync all the time. I
checked the latest code and it appears there is some work on how
information is stored. They might be working on being able to turn
some of the durability options for performance. It will be interesting
to see how well H2 does against Derby when both of them delay writes.
fyi I tried to increase the data sizes for the benchmark so that both
Derby, and H2 were actually reading from the disk and sometimes the
benchmark didn't work. Other times h2 crashed. You may want to look
into this.
Brish
I continued to try to update the h2 benchmark so it doesn't cripple
the other database's results. All the previous changes I mentioned
above are still applied.
I reduced the cache size on h2 to 4 megs so that it has the same size
as derby so that it's a fair test (it was 16 megs giving it 4x the
cache that derby has).
I decided to focus on fixing one benchmark first (BenchSimple).
I added batch inserts/updates/deletes so that network databases aren't
crippled anymore. I also added batch selects.
I encountered huge performance problems with h2. When I did the query
(random) test with batch selects the performance was really REALLY
bad. This change has no impact on derby embedded, and significantly
sped up derby server, postgres.
// In H2 there is a serious performance problem executing this
query.
// I'm not sure what is wrong with h2 but it should be fixed
because
// this is a common way of speeding up some types of data
loading.
db.start(this, "Query (random) - added fetch batching");
prep = db.prepare("SELECT * FROM TEST WHERE ID in
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
int currentParameter = 1;
for (int i = 0; i < records; i++) {
prep.setInt(currentParameter++, random.nextInt(records));
if (currentParameter > 10)
{
currentParameter = 1;
db.queryReadResult(prep);
}
}
prep.close();
db.end();
Embedded results for just this test:
H2: 103891
Derby: 1703
Server results for just this test:
H2: 111921
Derby: 7407
postgres: 4875
H2 has huge performance problems with batching if connecting to a
network server. On the network test H2 was easily beaten by Derby, and
postgres after batching was added. Batching sped up all the other
databases I tested.
Embedded results for BenchSimple:
h2: 109172
derby: 10547
Server results for BenchSimple:
h2: 237859
derby: 23016
postgres: 17046
I noticed that you didn't apply my previous changes to the benchmark
before updating your benchmark results on your main site. Does this
mean you are not serious about having a fair benchmark?!
Brish
I disabled batch insert/updates/deletes, and batch fetching for the
embedded tests because H2 performance was so bad with it.
I removed the crippling hsqldb config you added.
Your config for hsqldb:
jdbc:hsqldb:data/
test;hsqldb.default_table_type=cached;sql.enforce_size=true;sa, sa
h2: 4188
derby: 11125
hsqldb: 8578
Removed your custom config for hsqldb:
jdbc:hsqldb:data/test;sa, sa
h2: 4234
derby: 10654
hsqldb: 1875
Brish
prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
+ "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
+ "OL_D_ID=? AND OL_O_ID<? AND "
+ "OL_O_ID>=? AND S_W_ID=? AND "
+ "S_I_ID=OL_I_ID AND S_QUANTITY<?");
prep.setInt(1, warehouseId);
prep.setInt(2, dId);
prep.setInt(3, oId);
prep.setInt(4, oId - 20);
prep.setInt(5, warehouseId);
prep.setInt(6, threshold);
// TODO this is where HSQLDB is very slow
rs = db.query(prep);
rs.next();
rs.getInt(1); // stockCount
rs.close();
?!
Brish
Sorry for the delay.
> > nobody from Derby took the time to see why Derby is slow.
> But they sped it up with the current release?! It appears they are
> looking at performance.
Sorry, I should have written: why Derby is slow in the H2 benchmark.
> Having autocommit on isn't the normal case.
It depends on the use case of course. Of course you could argue people
who are interested in performance will disable it. Autocommit is
enabled by default after opening a connection.
> It does use force. See:
Yes, I saw that, and also thought it is used by default. However, then
I have added a System.out.println(...) just before the call and run my
test. There was nothing on system out. The method is not actually
called by the engine in the normal case.
Maybe I am wrong and they have changed it (but that would be strange
as it slows things down a lot), if you have time could you test it
with the current release of Derby?
> Derby does IO for commits. H2 delays IO for commits for one second.
> This gives h2 really impressive looking numbers because it can do many
> commits with a single write but at the cost of durability.
Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
configuration is changed so they also delay the commit by one second.
If Derby supports a delayed commit I would do that as well.
> derby.system.durability = test
> Derby in this mode is still more reliable than h2.
Why do you think so?
> How can I consider the H2 benchmark valid if you disable durability on
> every database that supports it except Derby?
Because Derby says it's dangerous:
WARNING: Write cache reduces probability of successful recovery after
power failure
If it's not dangerous any longer, I will use it.
> > develpers to change the default settings to make it faster.
> They are. See the latest version performance improvements.
That's good! Of course performance is not the only 'selling point' for
a database.
> Why not just have a standard open source benchmark for databases
> period?
> Like this one:
> http://polepos.sourceforge.net/
See http://www.h2database.com/html/performance.html for the results
(however I didn't run this test for a longer time, probably I should
remove the numbers). The algorithms are all quite simple. It was
developed / sponsored by db4o. The default settings are problematic
(for example, databases are not always closed).
> I noticed that as the database size grows the difference in
> performance between h2, and derby shrinks.
That's possible. H2 is not yet optimized for larger databases.
> This probably has something
> to do with all the data for the benchmarks fits into the h2 cache but
> not in the derby cache.
I'm not sure. For BenchC Derby does use much less memory. I will need
to change the cache settings. The last time I did that, it didn't make
much difference, but potentially it does of course.
> It will be interesting
> to see how well H2 does against Derby when both of them delay writes.
Sure!
> fyi I tried to increase the data sizes for the benchmark so that both
> Derby, and H2 were actually reading from the disk and sometimes the
> benchmark didn't work. Other times h2 crashed. You may want to look
> into this.
What were the settings? Of course I need to find out why H2 crashed!
What kind of exception did you get? Lock timeout?
Thanks for you feedback!
Thomas
On 10/10/07, Thomas Mueller <thomas.to...@gmail.com> wrote:
>
> Hi,
>
> Sorry for the delay.
>
> > > nobody from Derby took the time to see why Derby is slow.
> > But they sped it up with the current release?! It appears they are
> > looking at performance.
>
> Sorry, I should have written: why Derby is slow in the H2 benchmark.
>
> > Having autocommit on isn't the normal case.
>
> It depends on the use case of course. Of course you could argue people
> who are interested in performance will disable it. Autocommit is
> enabled by default after opening a connection.
>
We use both Derby and H2 with autocomitt off exclusively; our use case
is a bit non standard but still.
> > It does use force. See:
>
> Yes, I saw that, and also thought it is used by default. However, then
> I have added a System.out.println(...) just before the call and run my
> test. There was nothing on system out. The method is not actually
> called by the engine in the normal case.
>
> Maybe I am wrong and they have changed it (but that would be strange
> as it slows things down a lot), if you have time could you test it
> with the current release of Derby?
>
> > Derby does IO for commits. H2 delays IO for commits for one second.
> > This gives h2 really impressive looking numbers because it can do many
> > commits with a single write but at the cost of durability.
>
> Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
> configuration is changed so they also delay the commit by one second.
> If Derby supports a delayed commit I would do that as well.
>
> > derby.system.durability = test
> > Derby in this mode is still more reliable than h2.
>
> Why do you think so?
>
> > How can I consider the H2 benchmark valid if you disable durability on
> > every database that supports it except Derby?
>
> Because Derby says it's dangerous:
> WARNING: Write cache reduces probability of successful recovery after
> power failure
> If it's not dangerous any longer, I will use it.
>
This tracks with what I understand about how Derby works. It seems to
me that with H2 you might lose some data fromt he last set of
operations, but the database would be recoverable to a known point;
with Derby, if you turn off durability, you run the risk of a
non-recoverable database, which is an order of magnitude more
disastrous.
> > > develpers to change the default settings to make it faster.
> > They are. See the latest version performance improvements.
>
> That's good! Of course performance is not the only 'selling point' for
> a database.
>
> > Why not just have a standard open source benchmark for databases
> > period?
> > Like this one:
> > http://polepos.sourceforge.net/
>
> See http://www.h2database.com/html/performance.html for the results
> (however I didn't run this test for a longer time, probably I should
> remove the numbers). The algorithms are all quite simple. It was
> developed / sponsored by db4o. The default settings are problematic
> (for example, databases are not always closed).
>
> > I noticed that as the database size grows the difference in
> > performance between h2, and derby shrinks.
>
> That's possible. H2 is not yet optimized for larger databases.
>
> > This probably has something
> > to do with all the data for the benchmarks fits into the h2 cache but
> > not in the derby cache.
>
In my situation, I expose H2 to somewhat large databases (500-800
tables, a total of 10-30 million rows). In this instance I use a cache
of at least 64M, sometimes 128M. Even though H2 is handling
significantly more data than fits in the cache, its performance
remains massively above Derby's, both query and ingest. No matter how
high I set the cache in Derby I can't quite get it to play catchup.
> I'm not sure. For BenchC Derby does use much less memory. I will need
> to change the cache settings. The last time I did that, it didn't make
> much difference, but potentially it does of course.
>
> > It will be interesting
> > to see how well H2 does against Derby when both of them delay writes.
>
> [... deletia ...]
I've no doubt Derby will improve.
Chris
> I reduced the cache size on h2 to 4 megs so that it has the same size
> as derby so that it's a fair test (it was 16 megs giving it 4x the
> cache that derby has).
What settings did you use?
> with batch selects the performance was really REALLY
> bad.
> prep = db.prepare("SELECT * FROM TEST WHERE ID in
> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
The problem is that H2 doesn't always use an index in this case.
> Does this
> mean you are not serious about having a fair benchmark?!
I am serious about a fair benchmark, but I agree the IN(...) needs to
be fixed in H2. However I don't believe that using IN(...) like that
is the most important use case. Usually IN is used to limit the result
in some way, for example: WHERE BUG_ID=? AND BUG_STATE IN(OPEN,
REOPENED, ASSIGNED). You are using it as the primary key lookup, WHERE
BUG_ID IN(?, ?, ...). The TPC tests don't use IN(...) at all as far as
I know, at least not in this way.
Derby does a better job than H2 at using indexes when possible. There
are still quite many case where H2 doesn't use an index, or only uses
indexes in some cases. For example, H2 doesn't use an index when using
GROUP BY: SELECT TYPE, COUNT(*) FROM TEST GROUP BY TYPE will not use
an index on TYPE. It will take some time until all such cases are
using an index. However, H2 is already much better at optimizing
queries than HSQLDB.
Thomas
Cheers
Andrea
> I removed the crippling hsqldb config you added.
>
> Your config for hsqldb:
> jdbc:hsqldb:data/
> test;hsqldb.default_table_type=cached;sql.enforce_size=true;sa, sa
If you do that HSQLDB uses in-memory tables. H2 supports this mode as
well, using
jdbc:h2:test;DEFAULT_TABLE_TYPE=MEMORY
There is still a difference between H2 memory tables and HSQLDB memory
tables (for persistent databases): H2 supports larger in-memory tables
as the table data is not always kept in-memory (I know this is
confusing); only the index data is kept in-memory.
H2 doesn't support ignoring the VARCHAR size.
Thomas
Is it not? See the TPC-C test at http://www.tpc.org/tpcc/spec/tpcc_current.pdf
Thomas
> at least for some applications a filter
> based exclusively on in(...) is an important use case.
Sure. I understand it is important. For H2, there is a workaround:
SELECT * FROM TABLE(X AS INT = ?) I, TEST T WHERE I.X = T.ID
You can then set the parameter ? like this:
PreparedStatement.setObject(1, new Long[]{new Long(1), ...});
Therefore, you can use it with PreparedStatements. It will use an
index on TEST.ID. Also, you can use multiple columns. See also
org.h2.tools.MultiDimension (about this, and about multi-dimensional
range queries).
The current idea is to automatically convert IN(...) queries to such
queries. But it will take some more time.
Thomas
Who are the people who aren't interested in performance?!
> > Derby does IO for commits. H2 delays IO for commits for one second.
> > This gives h2 really impressive looking numbers because it can do many
> > commits with a single write but at the cost of durability.
>
> Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
> configuration is changed so they also delay the commit by one second.
> If Derby supports a delayed commit I would do that as well.
fyi There is new feature in the beta of posgresql 8.3 so that delayed
writes can't result in unrecoverable errors.
MySQL isn't reliable in any mode (it's getting better with a special
ansi mode but it's still not reliable).
It would be interesting to see the performance numbers for all the
databases when durability is turned on.
fyi For most the data I work with reliability is more important than
speed.
> > derby.system.durability = test
> > Derby in this mode is still more reliable than h2.
>
> Why do you think so?
I tried to get derby to corrupt the database by forcing the java vm to
shutdown in the middle of transactions. I even did forced shutdowns
during recovery and it still had no problems (it turns out even
recovery in derby uses a WAL so failed recoveries can be recovered
from!!!).
When I tried optimize the benchmarks I got a few crashes in h2 that
caused data problems. Maybe I'm just good at finding bugs! :-)
> > How can I consider the H2 benchmark valid if you disable durability on
> > every database that supports it except Derby?
>
> Because Derby says it's dangerous:
> WARNING: Write cache reduces probability of successful recovery after
> power failure
> If it's not dangerous any longer, I will use it.
If having issues recovering means it is excluded from being in the
benchmark then you can't include MySQL (it isn't reliable in any
mode), you can't do delayed writes for postgres (it can corrupt data),
you can't include hypersonic, and you can't include h2 in the
benchmark.
So either fix the postgres configuration, and remove all the other
databases except derby ... or turn off durability for derby so that
it's a fair test.
> > fyi I tried to increase the data sizes for the benchmark so that both
> > Derby, and H2 were actually reading from the disk and sometimes the
> > benchmark didn't work. Other times h2 crashed. You may want to look
> > into this.
>
> What were the settings? Of course I need to find out why H2 crashed!
> What kind of exception did you get? Lock timeout?
There is a number in a properties file that controls the size of the
data to benchmark. It was 400 by default. I changed it to 10000. The
benchmark failed.
Brish
H2 delays writes to the log so if the java vm crashes it can result in
unrecoverable errors because there is no log to recover from.
In derby in "test" mode the write is still done but it doesn't call
sync. This means even if the vm crashes the OS can still write the
data. The test mode will only fail if the OS crashes, or there is a
power failure.
What this means is the derby in the test mode is more reliable than h2
in any mode.
> In my situation, I expose H2 to somewhat large databases (500-800
> tables, a total of 10-30 million rows). In this instance I use a cache
> of at least 64M, sometimes 128M. Even though H2 is handling
> significantly more data than fits in the cache, its performance
> remains massively above Derby's, both query and ingest. No matter how
> high I set the cache in Derby I can't quite get it to play catchup.
I found the following:
- for simple operations h2 is faster
- for reports derby is faster
- h2 is really slow on a few operations (batching with a network
server, large transactions, in(...) processing with a wide range of
values etc)
Brish
The cache size in h2 was set to 4 megs.
> > Does this
> > mean you are not serious about having a fair benchmark?!
>
> I am serious about a fair benchmark, but I agree the IN(...) needs to
> be fixed in H2. However I don't believe that using IN(...) like that
> is the most important use case. Usually IN is used to limit the result
> in some way, for example: WHERE BUG_ID=? AND BUG_STATE IN(OPEN,
> REOPENED, ASSIGNED). You are using it as the primary key lookup, WHERE
> BUG_ID IN(?, ?, ...). The TPC tests don't use IN(...) at all as far as
> I know, at least not in this way.
I use the in (...) thing all the time because it speeds up network
database queries a lot for graphs of objects. Also, Hibernate does
this as well.
fyi The in (...) significantly sped up h2 for the sequential test when
h2 was ran in server mode. It only had a negative effect on h2 for the
random test (all the other database were sped up in the random test
using it).
> Derby does a better job than H2 at using indexes when possible. There
> are still quite many case where H2 doesn't use an index, or only uses
> indexes in some cases. For example, H2 doesn't use an index when using
> GROUP BY: SELECT TYPE, COUNT(*) FROM TEST GROUP BY TYPE will not use
> an index on TYPE. It will take some time until all such cases are
> using an index. However, H2 is already much better at optimizing
> queries than HSQLDB.
I checked out the current code for hsqldb because the website said it
has a new query optimizer. hsqldb beats h2 on the h2 benchmark using
the current version.
Brish
You didn't answer the question. Do you think it's a valid test?
The query is just odd.
Brish
fyi The explain said it was using the index for the in(...) in my
test.
Brish
> fyi There is new feature in the beta of posgresql 8.3 so that delayed
> writes can't result in unrecoverable errors.
Yes, that's what H2 uses as well.
> MySQL isn't reliable in any mode (it's getting better with a special
> ansi mode but it's still not reliable).
That's what Derby claims. Do you have a link for the MySQL problem?
> It would be interesting to see the performance numbers for all the
> databases when durability is turned on.
The performance would be very bad for all of them: about 20
transactions per second. Except for OS X where fsync doesn't actually
do a lot (that's what I have read somewhere). And it wouldn't be
durable, see http://www.h2database.com/html/advanced.html#durability_problems
> fyi For most the data I work with reliability is more important than
> speed.
Yes. But reliability doesn't mean force commits; delayed commits as in
H2 or PostgreSQL (8.3 only? I didn't know that) are reliable as well.
They are just not durable (see the link above)
> When I tried optimize the benchmarks I got a few crashes in h2 that
> caused data problems.
Could you post the exception, and the steps required to reproduce it?
> MySQL (it isn't reliable in any mode),
> postgres (it can corrupt data),
It would be great if you could tell me from where you have this information.
Thomas
It would be great if you could describe how you came to this
conclusion, or if you could provide a test case.
> In derby in "test" mode the write is still done but it doesn't call
> sync.
Again, Derby never calls sync. Even when not using "test". Please test
it if you don't believe it.
> What this means is the derby in the test mode is more reliable than h2
> in any mode.
It would be great if you could describe how you came to this conclusion.
> I found the following:
> - for simple operations h2 is faster
> - for reports derby is faster
> - h2 is really slow on a few operations (batching with a network
> server, large transactions, in(...) processing with a wide range of
> values etc)
Yes, H2 is slower for some operations. For reports that use very
complex queries, and when using IN(...) H2 doesn't always optimize as
good as Derby yet (H2 doesn't always use an index where it could).
Thomas
Sorry for the delay.
> I checked out the current code for hsqldb because the website said it
> has a new query optimizer. hsqldb beats h2 on the h2 benchmark using
> the current version.
That's cool! As soon as it is released I will run the benchmark again.
> > > Do you really think this is a valid test:
> > prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
> > + "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
> > + "OL_D_ID=? AND OL_O_ID<? AND "
> > + "OL_O_ID>=? AND S_W_ID=? AND "
> > + "S_I_ID=OL_I_ID AND S_QUANTITY<?");
> > Is it not? See the TPC-C test athttp://www.tpc.org/tpcc/spec/tpcc_current.pdf
> You didn't answer the question. Do you think it's a valid test?
Yes, I think it is a valid test.
> The query is just odd.
I suggest your read the documentation at
http://www.tpc.org/tpcc/spec/tpcc_current.pdf to understand the query.
It tests are a bit complex, but I think the people who designed it
tried to make it meaningful. I saw many queries in existing
applications that are very strange.
Thomas