• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem in Using Update Query in PreparedStatment

 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.



 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks fo your valuable response ..I will try removing the indexes on the column..Thanks a lot
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
author & internet detective
Posts: 41878
909
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.


Note that some databases like Oracle have "function based indexes" where you can add an index on UPPER(SERIALNUMBER).
 
reply
    Bookmark Topic Watch Topic
  • New Topic