how to improve my java class performance while selecting and inserting

[sdanda]

Hi ,

Do you have any idea how to improve my java class performance while selecting and inserting data into DB using JDBC Connectivity .........
This has to work for more than 8,00,000 of records ..... Can you give some performance tips if you have known

1) For this I am using oci driver ( because I m using oracle 10g) instead of thin driver
2) In that programme I m using prepared statement instead of statement
3) I am executing the statements as batches
4) I am using this conn.setAutoCommit(false); for improve my performance

Whether we have any option to improve my performance still ?

I am sending the code below,in that I have to change statement as preparedstatement ...
Check this once and send some of the performance improvement steps ...

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import weblogic.jdbc.vendor.oracle.OracleArray;
import atg.security.MD5PasswordHasher;
import com.objectspace.jgl.HashMap;
 
public class PasswordMigration1 {
 
    /**
     * @param args
     */
    static String userId;
    static String asisPassword;
 
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        try {
            System.out.println("Password Migration Starts At time:"+System.currentTimeMillis());
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
             Connection conn = DriverManager.getConnection(
          "jdbc:oracle:oci:@(description=(address=(host=172.20.232.35)" +
            "(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_NAME=CVSDMGN)))",
          "atgcore_o",
          "atgcore_o"
        );
             conn.setAutoCommit(false); 
              PreparedStatement stmt = conn.prepareStatement("select tmig_migrate_profile.mbr_nbr, password from Member, tmig_migrate_profile where Member.mbr_nbr=tmig_migrate_profile.mbr_nbr");
              ResultSet rset = stmt.executeQuery();
              String sqlQuery;
              Statement stmtMigrate=conn.createStatement();
              String sqlQueryAsis;
              Statement stmtAsis=conn.createStatement();
 
              int count=0;
              long size=793336;
                while (rset.next())
                {
                    count++;
                    userId=rset.getString(1);
                    asisPassword=rset.getString(2);
                    String decryptedPassword=decryptPassword(asisPassword);
                    String encryptedPassword=encryptPassword(decryptedPassword);
                    sqlQuery="INSERT INTO TMIG_USER_PWD (user_id,password) VALUES(" +
                    "'"+userId+"','"+encryptedPassword+"'"+")";
                    stmtMigrate.addBatch(sqlQuery);
                     sqlQueryAsis="INSERT INTO TMIG_USER_PWD_ASIS (user_id,password) VALUES(" +
                      "'"+userId+"','"+decryptedPassword+"'"+")";
                    stmtAsis.addBatch(sqlQueryAsis);
                     if(count%100==0 || ((size-count)==0 ) ){
                        stmtMigrate.executeBatch();
                        stmtAsis.executeBatch();
                        stmtMigrate.clearBatch();
                        stmtAsis.clearBatch();
                        count=0;
                        size=size-100;
                    }
                }
                stmtMigrate.close();
                stmtAsis.close();
                stmt.close();
                conn.close();
                System.out.println("Password Migration ENDS::"+System.currentTimeMillis());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    private static String encryptPassword(String decryptedPassword) {
        // TODO Auto-generated method stub
            MD5PasswordHasher passwordHasher=new MD5PasswordHasher();
            return passwordHasher.encryptPassword(decryptedPassword);        
    }
    private static String decryptPassword(String asisPassword) {
        // TODO Auto-generated method stub
        int length = asisPassword.length();
        int temp=2;
        char c;
        StringBuffer sb = new StringBuffer();
        for(int i=0;i<asisPassword.length();i=i+2)
        {
            String StringToken = (asisPassword.substring(i,temp));
            int intToHex = Integer.parseInt(StringToken, 16);
            c = (char) intToHex;
            sb.append(c);
            temp=((temp==length-1)?length:(temp+2));
        }
 
        return sb.toString();
    }
}

[r035198x]

1.) Use code tags when posting code. Posting code without code tags is against site rules.
2.) Use StringBuilder instead of StringBuffer.
3.) Don`t you have to call commit at some point if you`ve set autoCommit to false?

[sukatoa]

Hi ,

Do you have any idea how to improve my java class performance while selecting and inserting data into DB using JDBC Connectivity .........
This has to work for more than 8,00,000 of records ..... Can you give some performance tips if you have known

1) For this I am using oci driver ( because I m using oracle 10g) instead of thin driver
2) In that programme I m using prepared statement instead of statement
3) I am executing the statements as batches
4) I am using this conn.setAutoCommit(false); for improve my performance

Whether we have any option to improve my performance still ?

I am sending the code below,in that I have to change statement as preparedstatement ...
Check this once and send some of the performance improvement steps ...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import weblogic.jdbc.vendor.oracle.OracleArray;
import atg.security.MD5PasswordHasher;
import com.objectspace.jgl.HashMap;

public class PasswordMigration1 {

/**
* @param args
*/
static String userId;
static String asisPassword;

public static void main(String[] args) {
// TODO Auto-generated method stub
try {
System.out.println("Password Migration Starts At time:"+System.currentTimeMillis());
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:oci:@(description=(address=(host=172. 20.232.35)" +
"(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_ NAME=CVSDMGN)))",
"atgcore_o",
"atgcore_o"
);
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement("select tmig_migrate_profile.mbr_nbr, password from Member, tmig_migrate_profile where Member.mbr_nbr=tmig_migrate_profile.mbr_nbr");
ResultSet rset = stmt.executeQuery();
String sqlQuery;
Statement stmtMigrate=conn.createStatement();
String sqlQueryAsis;
Statement stmtAsis=conn.createStatement();

int count=0;
long size=793336;
while (rset.next())
{
count++;
userId=rset.getString(1);
asisPassword=rset.getString(2);
String decryptedPassword=decryptPassword(asisPassword);
String encryptedPassword=encryptPassword(decryptedPasswor d);
sqlQuery="INSERT INTO TMIG_USER_PWD (user_id,password) VALUES(" +
"'"+userId+"','"+encryptedPassword+"'"+")";
stmtMigrate.addBatch(sqlQuery);
sqlQueryAsis="INSERT INTO TMIG_USER_PWD_ASIS (user_id,password) VALUES(" +
"'"+userId+"','"+decryptedPassword+"'"+")";
stmtAsis.addBatch(sqlQueryAsis);
if(count%100==0 || ((size-count)==0 ) ){
stmtMigrate.executeBatch();
stmtAsis.executeBatch();
stmtMigrate.clearBatch();
stmtAsis.clearBatch();
count=0;
size=size-100;
}
}
stmtMigrate.close();
stmtAsis.close();
stmt.close();
conn.close();
System.out.println("Password Migration ENDS::"+System.currentTimeMillis());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static String encryptPassword(String decryptedPassword) {
// TODO Auto-generated method stub
MD5PasswordHasher passwordHasher=new MD5PasswordHasher();
return passwordHasher.encryptPassword(decryptedPassword);
}
private static String decryptPassword(String asisPassword) {
// TODO Auto-generated method stub
int length = asisPassword.length();
int temp=2;
char c;
StringBuffer sb = new StringBuffer();
for(int i=0;i<asisPassword.length();i=i+2)
{
String StringToken = (asisPassword.substring(i,temp));
int intToHex = Integer.parseInt(StringToken, 16);
c = (char) intToHex;
sb.append(c);
temp=((temp==length-1)?length:(temp+2));
}

return sb.toString();
}
}

About your while loop and for loop

Comparison to 0 is faster than comparisons to most other numbers. The VM has
optimizations for comparisons to the integers -1, 0, 1, 2, 3, 4, and 5. So rewriting loops to
make the test a comparison against may be faster.[1] This alteration typically reverses the
iteration order of the loop from counting up (0 to max) to counting down (max to 0).

That's all i can share....
Sukatoa