updating table data

From: yilmaz <yilmazay_at_yahoo.com>
Date: 29 Sep 2001 01:51:11 -0700
Message-ID: <b8a3c655.0109290051.3a26c6c_at_posting.google.com>

hi everybody!
i hope this is the correct place to seek a solution for my problem i have two tables with more than 40,000 rows 20 columns. i want to add two more columns into one of those tables from the other one.
i wrote a simple java application program to do this job. below is the related snippet of my code :

public static void main(String[] args) {   Connection conn=null;
  PreparedStatement pstmt=null;
ResultSet rs=null;
String str1,str2,str3="";
String query="Select cd.collcategory,cd.totid,cs.totid from cdfinal

       cd,csourcenew cs where cd.totid=cs.totid";

    try {       

Class.forName("oracle.jdbc.driver.OracleDriver"); conn=DriverManager.getConnection("jdbc:oracle:thin:_at_yilmaz:1521:ay","xxx","yyy");

stmt=conn.createStatement();
rs=stmt.executeQuery(query);
//conn.setAutoCommit(false);

pstmt=conn.prepareStatement("update csourcenew set collgroup=? where totid=?");
int count=0;

while(rs.next()) {
 str1=rs.getString(1);
//str2=rs.getString(2);

 str3=rs.getString(3);
 System.out.println(str3+" "+str1);

//pstmt.addBatch("update csourcenew set collgroup="+str1+" where
totid="+str3);
//pstmt.addBatch("update csourcenew set collcategory="+str2+" where
totid="+str3);

  pstmt.clearParameters();
  pstmt.setString(1,str1);
  pstmt.setString(2,str3);
  pstmt.executeUpdate();

 count++;

    }

    // conn.commit();
     //conn.setAutoCommit(true);
   System.out.println(count);
    }
....//below go the lines with catch
though this program works correectly, but.. my problem is : It is too slow.
just to give an idea about how slow it is i started my program 5 hours ago and
it is still running , sorry walking :)
i tried to use batch updating , it was very fast, but for some reasons it couldn't update the tables. It was running till the end of the table, at the end throwing an error message. When i check my table, there were no update.
Could someone give me some suggestions to optimize my code. i know that updating rows one by one is not a smart thing, but just i couldn't think any better way.
your suggestions or even your critics are welcome. just show me the right direction and i will do the rest. thanks for your kindness in advance
cheers :)
have a nice and peaceful weekend :) Received on Sat Sep 29 2001 - 10:51:11 CEST