Problem in Using Update Query in PreparedStatment

[Nagendra Batchu]

Hi All,

I am facing a problem in Using Update query in prepared statement. I am trying to update a huge set of records by using Prepared Statement in JAVA


The Code is as follows:


statement5=connection.prepareStatement("update CHANNELINVENTORY set CHANNELCODE=?,LASTUPDATEON=sysdate where UPPER(ITEMCODE)=? and UPPER(SERIALNUMBER)=? ");
for(int j=0;j<channelInventoryDetails.size();j++){
String channelDetails=(String)channelInventoryDetails.get(j);
String[] channel=channelDetails.split("-");
statement5.setString(1,channel[0]);
statement5.setString(2,channel[1]);
statement5.setString(3,channel[2]);
if (j > 0 && j % 1000 == 0){
System.out.println("before addbatch-from if->"+j);
statement5.addBatch();
statement5.clearParameters();
statement5.executeBatch();
}else{
System.out.println("before addbatch-from else->"+j);
statement5.addBatch();
statement5.clearParameters();
}
}

In my present case i am trying to update around 3000 records, in my code after every 1000 records i am executing that batch , which is taking huge time ...nothing is coming its just hanging for huge time .. its just prints this ------------------->before addbatch-from if->1000 thats it

And in the DB there primary key constaints on both the (ITEMCODE+SERIALNUMBER) and there is a index on CHANNELCODE..

Please suggest me the changes...

Is this the right way to update huge data in the table ...or is there any other way to update huge data.

[Jan Cumps]

The CHANNELCODE index does not help for this query. It will cost:
- you don't use it in the where clause, so the index is not relevant to speed up the query.
- you are updating CHANNELCODE, so the index has to be updated every time you execute the update.

-- so far for CHANNELCODE.


Next ITEMCODE and SERIALNUMBER:
I don't know if these columns have an index.
But if they have, they will probably not be used, because you use UPPER() around them.

-- so far for ITEMCODE and SERIALNUMBER.

Next 1000:
Is your rollback tablespace big enough to deal with 1000 changes to your database at a single time?
Try your code with committing faster first (say: 2 records).
If that works, you confirmed that your logic is ok, and you can try to increase to a workable amount.
If possible: Commit after an update. This will free up your rollback segment.


Regards, Jan

[Nagendra Batchu]

Thanks fo your valuable response ..I will try removing the indexes on the column..Thanks a lot

[Jan Cumps]

Nagendra Batchu wrote:T..I will try removing the indexes on the column..

Oops, that was not what I was trying to suggest. I was explaining why it adds costs in this case. The other suggestions are more important.

[Jeanne Boyarsky]

How long does this query take to run once? If it's a non trivial amount of time, tuning it will immensely help your update. This is what I think Jan was getting at with the above post.

select count(*) from CHANNELINVENTORY where UPPER(ITEMCODE)=? and UPPER(SERIALNUMBER)=? 

Note that some databases like Oracle have "function based indexes" where you can add an index on UPPER(SERIALNUMBER).