Hi,
I spent two lonely nights trying to optimise the retrieving of binary
data from bytea column when using ResultSet.getBytes().
The attached patch gives 20-400% speedup depending on the bytea column
size. The speedup comes from switching to the binary transfer mode
instead of the text transfer mode for bytea columns.
The patch also contains the test code which I used to test the
performance (ByteaBenchTest.java). The benchmark results are:
cvs:
column size: transfer speed:
512b speed: 3,8MB/s
2048b speed: 10,0MB/s
4096b speed: 13,1MB/s
16384b speed: 22,0MB/s
65536b speed: 22,4MB/s
1048576b speed: 21,2MB/s
patch:
column size: transfer speed:
512b speed: 4,6MB/s - 21% speedup
2048b speed: 16,8MB/s - 68% speedup
4096b speed: 27,9MB/s - 112% speedup
16384b speed: 79,2MB/s - 260% speedup
65536b speed: 111,1MB/s - 395% speedup (optimal for my L2 cache?)
1048576b speed: 74,8MB/s - 252% speedup
The benchmark was run on Java6rc build 104 with postgresql 8.1.5 running
on localhost with Athlon64 2x2GHz, 64bit mode.
---
What the patch does:
After executing a prepared statement the first time the result set field
information (Field[]) is cached into the prepared query object.
Subsequent executions of the prepared statements use the field
types to decide when to request binary encoding from the server.
Currently only bytea types request binary encoding. The caching seems
safe because the Field objects are immutable.
Still open questions:
- is there any better way to obtain the result set field information in
QueryExecutor?
* could the result set type information be obtained during the
prepare?
- in which conditions should a prepared statement drop the cached fields
* do the result set fields ever change?
* any other conditions?
- if caching of Field information is allowed (at least in some
circumstances), could the driver stop requesting a Describe from the
backend?
-Mikko