posted 16 years ago
I am accessing an Oracle database that is about 4000 miles away.
The bandwidth on both ends is adequate, and the performance is
reasonable so long as I set the fetch size to an appropriate number.
Retrieving 31,000 rows of data without CLOBS behaves as follows:
When reading CLOB data from an Oracle database the driver does not provide
the contents of the CLOB at the same time as the rest of the row, instead
it provides an InputStream to read from. This results in an additional
roundtrip and causes significant overhead in my situation.
One approach I tried was to select the data in two batches - one with data smaller than 4000, and one with the data over 4000. e.g.:
query 1: select to_char(foo) from sometable where length(foo) <= 4000
query 2: select foo from sometable where length(foo) > 4000
I applied this technique to the following data set:
Rows with CLOB smaller than 4000: 30000
Rows with CLOB larger than 4000: 800
The result was that I could fetch the 30000 smaller records in 110 seconds,
while the 800 remaining records took an additional 7 minutes. The larger records were all under 30kb, and the total size of the smaller records was much higher than the total size of the larger records.
Bandwidth is not the issue here - it is the overhead from an excessive number of roundtrips.
Thus far I have not been able to find a way to transfer CLOB data in
batches along with the other row data - is there a way to do this?
[ June 05, 2007: Message edited by: Christopher Clark ]
[ June 05, 2007: Message edited by: Christopher Clark ]