1

I have an Oracle table that contains a number of clob fields. The clob fields themselves are plain text CSV files that are about 4kb-8kb in size. There are about 200 records in my table. I have a Sql query written that I run in JdbcTemplate that runs pretty quickly in JdbcTemplate (about 2 seconds).

Unfortunately, when I try to extract the clob field into a string for all 200 records, the execution time goes from 2 seconds to over 20 seconds, which is far too slow.

I am using the following line to convert the clob to a string.

String clobValue = clob.getSubString(1, (int) clob.length());

It seems to be this conversion that is killing performance for me. Are there any alternatives open to me on the Oracle or Java side to speed this up?

I have tried some other suggestions from here, but performance doesn't improve - Most efficient solution for reading CLOB to String, and String to CLOB in Java?

Surely there is a different, or more efficient way to do this?

user2254180
  • 844
  • 13
  • 30
  • That's pretty fishy, 1 second per record is too much in any case. Did you try to profile both the java side and the db queries issued to see where the time is lost? – Curiosa Globunznik Nov 21 '19 at 17:55
  • The query to get the records takes 2 seconds. Each conversion of a clob to a string takes .1 seconds. That gets us to 20 seconds in total. I suppose I could multi-thread it... – user2254180 Nov 21 '19 at 18:37
  • I have one query that gets all 200 records at once. The issue I have isn't the query or getting the results in time, it's extracting the clob's on Java once I have them. – user2254180 Nov 21 '19 at 18:42
  • I assume you have to loop over the result, some `while (rs.next())`, that could cause further fetches under the hood that evade observation as of now. `executeQuery` only fetches the first bunch of records, depending on fetch size. Seems, default oracle/jdbc fetch size is [10 rows](https://medium.com/@FranckPachot/oracle-postgres-jdbc-fetch-size-3012d494712), you have 200 rows, so there will be another 19 fetches. Just trying to take the problem apart and find the actual cause. Converting the internal byte[] of clob to a String won't be the culprit, I'd assume. – Curiosa Globunznik Nov 21 '19 at 18:49
  • Also seems, you arrived at your number by averaging the run time after executeQuery per record, did you? Hm, and did you use the [native `oracle.sql.*` classes](https://web.stanford.edu/dept/itss/docs/oracle/10gR2/java.102/b14355/oralob.htm#i1058044) for access or did you go the generic way? – Curiosa Globunznik Nov 21 '19 at 18:59

0 Answers0