Title: How to improve performance while update column Clob in oracle?
Tags: java;sql;performance;oracle;jdbc
Question: I used the following codes to update column Clob in oracle, it seems to be okay and work properly, after performance testing, it reported that need consumed more than 200ms while the length of string is more than 130000. Is it any good way to improve it?
private void updateClobDetailsField(Map<Integer, String&gt; idToDetails){
long s1 = System.currentTimeMillis();
Connection conn = null;
PreparedStatement pStmt = null;
ResultSet rset = null;
Map<Integer, Clob&gt; idToDetailsClob = new HashMap<Integer, Clob&gt;();
int BATCH_SIZE = CMType.BATCH_UPDATE_MAXSIZE;
try
{
conn = getConnection();
ServerAdapter adapter = ServerAdapter.getServerAdapter();
List<Integer&gt; IDList = new ArrayList<Integer&gt;();
for(Integer id : idToDetails.keySet()){
IDList.add(id);
}
List<Integer&gt; tempIDList = new ArrayList<Integer&gt;(IDList);
while(!tempIDList.isEmpty()){
int size = tempIDList.size() < BATCH_SIZE ? tempIDList.size() : BATCH_SIZE;
List<Integer&gt; currentBatch = tempIDList.subList(0, size);
String inClause = SQLHelper.prepareInClause("ID",currentBatch.size());
pStmt = conn.prepareStatement("SELECT ID, DETAILS FROM PROGRAM_HISTORY WHERE " + inClause);
for(int i = 0; i < currentBatch.size(); i++){
pStmt.setInt(i+1, (currentBatch.get(i)));
}
rset = pStmt.executeQuery();
while(rset.next()){
int id = rset.getInt(1);
Clob detailsClob = rset.getClob(2);
Writer writer = adapter.getCharacterOutputStream(detailsClob);
String details = idToDetails.get(id);
if (details != null) {
writer.write(details);
}
writer.flush();
writer.close();
idToDetailsClob.put(id, detailsClob);
}
currentBatch.clear();
BaseSQLHelper.close(pStmt, rset);
}
int counter = 0;
pStmt = conn.prepareStatement("UPDATE PROGRAM_HISTORY SET DETAILS = ? WHERE ID = ?");
for(int i=0; i<IDList.size(); i++){
int index = 1;
Clob detailsClob = (Clob) idToDetailsClob.get(IDList.get(i));
pStmt.setClob(index++, detailsClob);
pStmt.setInt(index++, IDList.get(i));
pStmt.addBatch();
counter++;
if(counter % BATCH_SIZE == 0) {
pStmt.executeBatch();
pStmt.clearBatch();
counter = 0;
}
}
if(IDList.size() % BATCH_SIZE &gt; 0) {
pStmt.executeBatch();
}
}
catch (SQLException se)
{
se.printStackTrace();
}
catch (IOException se)
{
se.printStackTrace();
}
finally
{
cleanup(conn, pStmt, null);
}
System.out.println(System.currentTimeMillis()-s1);
Comment: I don't know where this Java is running regarding your server, but it seems not too bad. Assuming a 10Mbit connection, you will almost need all that time to get the this amount of data to the server in the first place. You probably have a better connection, but to put things in perspective.


Here is another answer: If I understand your code correctly, you are appending text to your details clob column.
Doing it in PL/SQL would be faster since you wouldn't have to fetch the clob across the network. For example you could prepare this statement:
DECLARE
l_details CLOB;
SELECT details INTO l_details FROM program_history WHERE ID = ?;
dbms_lob.append(l_details, ?);
and bind currentBatch.get(i) and idToDetails.get(id).
Notice that you don't need an additional update with PL/SQL.

Here is another answer: Execute your query with an updatable ResultSet so that you can update the data as you scroll through without separate update statements being executed.
You need to create your prepared statement with the resultSetConcurrency set to ResultSet.CONCUR_UPDATABLE. Check out the oracle documentation on dealing with streams for the various ways you can handle the clob data.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:07/27/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><sql><performance><oracle><jdbc>', 'Title': 'How to improve performance while update column Clob in oracle?', 'ViewCount': 1571, 'id': 6842694, 'user_AboutMe': None, 'user_AccountId': 788286.0, 'user_DisplayName': 'Whtisop', 'user_Reputation': 441.0, 'user_Views': 21.0, 'user_id': 864671.0}}