BLOB performance evaluation questions (long)

I'm evaluating HSQL BLOB performance and wanted to get some expert feedback
on how things have been looking in my test harness.

My hardware environment is Windows 7 x64 Ultimate, 24 GB RAM, 2 TB 7200 RPM
SATA HD, SATA 3.0 on a motherboard w/an Intel XEON E5630 @ 2.53GHz.  

Using HSQL 2.2.9 with Java 1.7.0U3 x64 and deploying the test harness into a
Glassfish 3.1.2.2 application server. 

My table schema looks like so:

CREATE TABLE FOO (ID BIGINT NOT NULL, SUBID INTEGER NOT NULL, PRODNAME
VARCHAR(32) NOT NULL, B1 BLOB NOT NULL, B2 BLOG NOT NULL, B3 BLOB NOT NULL,
PRIMARY KEY(ID))

CREATE UNIQUE INDEX I_INSTCOL_PRODNAM ON FOO (SUBID, PRODNAME)
CREATE INDEX I_INSTCOL ON FOO (SUBID)
CREATE SEQUENCE SEQUENCEFOO START WITH 1

I'm loading about 15 GB of binary data into the BLOB columns with a total
63636 rows. 

On the Glassfish side I've got a JTA connection pool set up to HSQL running
in file mode with the following parameters:

jdbc:hsqldb:file:E/hsql/test/testdb;sql.syntax_ora=true;hsqldb.log_data=false;hsqldb.default_table_type=cached;hsqldb.nio_max_size=2048;shutdown=true

I turned off logging because I wanted the fastest inserts during my test
runs as I'm focused now on retrieval times. 

In my particular use case I will have to fetch/iterate over the entire
table. 

I found that unwrapping the Connection from the injected EntityManager and
using it to create a JDBC prepared statement which I iterated over gave
better performance then JPA. 

In this case for each Blob in the fetched resultSet row I call
getBinaryStream() and then use Apache Commons IO IOUtils.toByteArray(is,
blog.length()) on the input stream to fetch the data.

Reading in the blob data from the resultset avgs 7.3ms per row and about 7.3
minutes to load all 63k rows worth of BLOB data. 

I can't store the entire DB in memory, hence the cache mode. In any event it
looks like BLOB data is not read into memory regardless.  Is it possible to
get faster performance then what I've detailed?  Are there any HSQL
configuration tweaks that could squeeze out 20%, 30% or better performance
gains? Is there a better approach for fetching the data then my JDBC
approach? 

FWIW, this approach is being compared vs. reading the data directly from
flat files into byte[] which is taking approx. 4.5 minutes to load all 15GB
worth of binary data. Obviously there's overhead in HSQL vs flat files but
ideally I would like to get things as close as possible to that performance.

Thoughts would be appreciated. 

-Noah



--
View this message in context: http://hsqldb.10974.n7.nabble.com/BLOB-performance-evaluation-questions-long-tp3909.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
This SF.net email is sponsored by Windows:

Build for Windows Store.

http://p.sf.net/sfu/windows-dev2dev