CODEWITHSUNDEEP
javaoracle-databasejdbcclob
Tahir Akhtar
Tahir Akhtar

Reputation: 11625

Performance implications of handling CLOBs with Oracle JDBC using SetBigStringTryClob

I am in process of reviewing some old Java/JDBC code for CLOB-handling on oracle database. Existing code uses approach similar to the approach mentioned in this question.

I found an article that states:

Prior to Oracle JDBC 10g, to manipulate the CLOB data in JDBC, Oracle extension class oracle.sql.CLOB was used. But now, Oracle JDBC 10g has a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling of large data using some of the available standard APIs, instead of using the Oracle extension classes.

The article gives following information about the details of this enhancement:

By default, the method preparedStatement.setString() will allow processing of the strings up to 32765 bytes. In order to insert data greater than 32765 bytes, a newly introduced Connection property - SetBigStringTryClob can be set. This forces the preparedStatement.setString() to use another newly introduced method, OraclePreparedStatement.setStringForClob() instead.

However it warns:

... handling very large amounts of data this way may not be a wise; streaming the data is a better alternative.

But above is the only performance-related warning in that article. My question is that if all of the CLOB access in my code is already being done through String objects, should I worry about any other potential performance problems that this change of approach might cause? In other words, my app is not using the benefit offered by streaming as it already always load the CLOBs in String objects so above warning can probably be ignored because we are not aiming at performance improvement at the moment. Are there any other performance-related issues that might arise because of switching to this technique?

Upvotes: 1

Views: 2570

Answers (1)

JOTN
JOTN

Reputation: 6317

How big are you talking about? I regularly handle short audio files and web images in memory without any performance issues. These are all kilobyte scale files. I think if you run megabyte sizes then streaming would be better. Using the streaming to a LOB is actually pretty easy now with the standardized stuff.

Upvotes: 1

Related Questions