Menu
We're in the process of converting our database from Sybase to Oracle and we've hit a performance problem. In Sybase, we had a TEXT field and replaced it with a CLOB in Oracle.
This is how we accessed the data in our java code:
while(rs.next()) {
String clobValue = rs.getString(1); // This takes 176ms in Oracle!
.
.
}
The database is across the country, but still, we didn't have any performance problems with Sybase and its retrieval of TEXT data.
Is there something we can do to increase this performance?
395
A CLOB (character large object) value can be up to 2,147,483,647 characters long.
1) if you are storing 4000 bytes or less in the database, use VARCHAR2 otherwise use CLOB. 2) if you are storing 32k bytes or less in PLSQL use VARCHAR2 otherwise use CLOB.
Sybase database servers consist of a data server and a backup server. There are two processes of the Sybase database server, whereas an Oracle instance has five mandatory processes: SMON (System Monitor), PMON (Process Monitor), LGWR (Log Writer), DBWR (Database Writer), and CKPT (Checkpoint).
CLOB stands for character large objects, which are used to store string data too large to be stored in a VARCHAR column.
By default, LOBs are not fetched along with the table data and it takes an extra round-trip to the database to fetch them in getString.
If you are using Oracle's .NET provider, you may set InitialLOBFetchSize in the data reader settings to a value large enough to accommodate your large objects in memory so they could be fetched in all their entirety along with the other data.
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
