Why is peeking into BLOBs via getBytes(long pos, int length) so slow on an H2 database?

Question

I have an application that needs to peek into blobs get out small numbers of bytes (via getBytes(long pos, int length)). The blobs are ~30MB. When I ask for bytes near the beginning of the blob, the performance is reasonable. When I ask for bytes near the end of the blob, the performance is much worse. Looking at the source code (JdbcBlob.java) it appears that the blob is read sequentially instead of randomly (via an input stream).

Does anybody know of any workarounds? I'm a huge H2 fan and this issue isn't a deal breaker but I think it could be improved.

Thanks

Answer 1

It's slow, because H2 uses an InputStream and doesn't do random access (so you have already answered the question yourself). The reason why random access it is not supported is: so far nobody requested this feature :-)

I don't think there is a simple workaround. H2 needs to be changed to support random access. For BLOB data, this should be relatively easy; for CLOB data it will be harder (because data is stored in UTF-8 form, and it's not so easy to seek to the right point).

I have now added a feature request in the roadmap, but if you really need this feature in the near future, then I'm afraid you will have to implement it yourself. H2 is open source, and patches are always welcome :-)

Answer 2

I just upgraded to version 1.3.166 and was happy to find that this issue has been corrected. Peeking near the end of the blob in my case took about 500ms.

It now takes 4ms.