Subject Improving performance of BLOB read operations in JDBC
Author jeeggers
I found the read-performance on BLOB columns to be dismal going
through JDBC, so I made some mods in the driver on my local machine.
I'd like to get some feedback from those that know the driver better.
For my test, I was reading two rows, one had a 1.6 MB Blob, the other
has a 6.1 MB blob (approx). Server is 1.5 and running on localhost
with respect to client, Win 2K. VM is 1.4.0

When I run the SELECT outside of a transaction, the
statement.executeQuery(sql) for the 2 rows takes 32 seconds the first
time, 6 seconds after that. I can't explain why it sometimes takes 5
times as long for the same query against the same data, maybe it's
what's in the server cache.

If I run the SELECT inside a transaction, executeQuery is instant,
but getBytes() on the BLOB column is slow and swings wildly:

Run # 1:
7 secs for 1.5 MB (first row) and 15 secs for 6.1 MB (second row)

Run # 2:
4 secs for 1.5 MB (first row) and 3 secs for 6.1 MB (second row)

Run # 3:
14 secs for 1.5 MB (first row), 6 seconds for 6.1 MB (second row)

Run # 4:
10 and 22 seconds

Run # 5:
8 and 6 seconds

It seems to me that the retrieval times are all over the place, why
is that ?

I cranked up the buffer sizes in FBManagedConnection and FBBlobField,
and while performance is somewhat better, it still swings widely.

I also monitored CPU utilization, and the FBServer process was using
hardly any CPU while the java process seemed to be maxed out... Any
ideas ?