In sqldeveloper , when you select #clob# from #table# the tool shows the first ~80 chars of the clob. I spent part of the last 2 days timing the fastest way to read clobs from the db. I mostly work on remote databases so I notice when network latency start getting high. In looking at java.sql.Clob there's a few way to get the text. I figured I'd test them and see if one was quicker than the other. I wrote a loop that walks all the tables which have clobs in the flow_xxx schema and timed 2 of then methods .getSubString and .getCharacterStream.read().
Here's the crux of my test:
// method one
int read = 80; // only need the first 80 to show
start = System.currentTimeMillis();
s = lob.getSubString(1, read);
long s1 = System.currentTimeMillis() - start;
// method two
start = System.currentTimeMillis();
char[] cbuf = new char[read];
lob.getCharacterStream().read(cbuf);
long s2 = System.currentTimeMillis() - start;
Outcome:
Total Rows Processed : 44850
Lobs ranged in size from null to 17k with the average working out to 114
Not surprisingly, when the LAN speeds were averaged out it was 0 and 0
However, over the slower connection .getSubString's average was 102ms and the read was 197ms.
