Sqldev talk in St. Louis

In case anyone reads this is in St. Louis there's a user group meeting Friday at Ameren details are here:

http://www.missourioracle.com/

Clob read speeds over jdbc

Thought I'd pass on some findings on clob read speed over jdbc.

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.