Sample Header Ad - 728x90

Can JDBC Oracle file stream blob inserts be speeded up?

1 vote
1 answer
515 views
I have these four different ways by which I am inserting some file data into an Oracle database using JDBC and PreparedStatements. - **Approach A** ps.setBytes(1, fileDataInByteArray); - **Approach B** Blob blob = ps.getConnection().createBlob(); blob.setBytes(1, fileDataInByteArray); ps.setBlob(1, blob); - **Approach C** ps.setBinaryStream(1, fileDataAsInputStream); - **Approach D** ps.setBlob(1, fileDataAsInputStream); In general, Approach C & D seem to be very useful for large fileData. A & B can run out of memory, but obviously C & D are more reliable. However, when the file size is smaller the danger of OOM is not there. I find that A & B take less time for the database insert. I suppose that’s because when the data is a byteArray, it’s much faster. What can I do to speed up the insert when using C&D? I have tried wrapping the input stream with a BufferedInputSteam. I played around with the buffer size. But that did not help. Are we at the mercy of how fast Oracle can read the data? Can that be speeded up?
Asked by Raster R (11 rep)
Jul 6, 2023, 08:45 AM
Last activity: May 30, 2025, 08:04 AM