For derby the default is autocommit=true, which means it will do a synchronous log commit write for every statement. My reading of the app and the
call to commit at the end seems to indicate you want to only commit
at the end. Depending on the data distribution (ie. how many times you
call executeUpdate this may be a big issue. When you run the test do
you see a lot of idle time or is 1 cpu at 100% for 12-14 secs? The suggestion for using aggregates seems like a better way to do the app.
What kind of page cache does oracle have when you compare? Are you interested in performance of a query when no page is in cache or when
all pages are in cache? This size table is bigger than the default
derby page cache (1000 pages) so it basically is going to be an I/O test of how fast
stuff can be read from disk each time. If you want to try a cached
test try setting derby page cache bigger - probably 10,000 pages will
fit that table (probably less - didn't do exact math).
You can see example code now. conn.setAutoCommit(false); putted there.
app run with:
java -Xms100m -Xmx1000m -cp dist/derbytest.jar derbytest.FatTest
So memory is enough for app.
Soft- & Hard-ware:
Code2Duo 2Gz, 2Gb RAM, MS Win XP SP2.
java version "1.5.0_15"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_15-b04)
Java HotSpot(TM) Client VM (build 1.5.0_15-b04, mixed mode)
derby version - 10.3.2.1
Oracle XE with default configuration.