By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,263 Members | 1,551 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,263 IT Pros & Developers. It's quick & easy.

how to improve my java class performance while selecting and inserting

P: 36
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 ...

Expand|Select|Wrap|Line Numbers
  1.  import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Enumeration;
  8. import weblogic.jdbc.vendor.oracle.OracleArray;
  9. import atg.security.MD5PasswordHasher;
  10. import com.objectspace.jgl.HashMap;
  11.  
  12. public class PasswordMigration1 {
  13.  
  14.     /**
  15.      * @param args
  16.      */
  17.     static String userId;
  18.     static String asisPassword;
  19.  
  20.     public static void main(String[] args) {
  21.         // TODO Auto-generated method stub
  22.         try {
  23.             System.out.println("Password Migration Starts At time:"+System.currentTimeMillis());
  24.             DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
  25.              Connection conn = DriverManager.getConnection(
  26.           "jdbc:oracle:oci:@(description=(address=(host=172.20.232.35)" +
  27.             "(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_NAME=CVSDMGN)))",
  28.           "atgcore_o",
  29.           "atgcore_o"
  30.         );
  31.              conn.setAutoCommit(false); 
  32.               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");
  33.               ResultSet rset = stmt.executeQuery();
  34.               String sqlQuery;
  35.               Statement stmtMigrate=conn.createStatement();
  36.               String sqlQueryAsis;
  37.               Statement stmtAsis=conn.createStatement();
  38.  
  39.               int count=0;
  40.               long size=793336;
  41.                 while (rset.next())
  42.                 {
  43.                     count++;
  44.                     userId=rset.getString(1);
  45.                     asisPassword=rset.getString(2);
  46.                     String decryptedPassword=decryptPassword(asisPassword);
  47.                     String encryptedPassword=encryptPassword(decryptedPassword);
  48.                     sqlQuery="INSERT INTO TMIG_USER_PWD (user_id,password) VALUES(" +
  49.                     "'"+userId+"','"+encryptedPassword+"'"+")";
  50.                     stmtMigrate.addBatch(sqlQuery);
  51.                      sqlQueryAsis="INSERT INTO TMIG_USER_PWD_ASIS (user_id,password) VALUES(" +
  52.                       "'"+userId+"','"+decryptedPassword+"'"+")";
  53.                     stmtAsis.addBatch(sqlQueryAsis);
  54.                      if(count%100==0 || ((size-count)==0 ) ){
  55.                         stmtMigrate.executeBatch();
  56.                         stmtAsis.executeBatch();
  57.                         stmtMigrate.clearBatch();
  58.                         stmtAsis.clearBatch();
  59.                         count=0;
  60.                         size=size-100;
  61.                     }
  62.                 }
  63.                 stmtMigrate.close();
  64.                 stmtAsis.close();
  65.                 stmt.close();
  66.                 conn.close();
  67.                 System.out.println("Password Migration ENDS::"+System.currentTimeMillis());
  68.         } catch (SQLException e) {
  69.             // TODO Auto-generated catch block
  70.             e.printStackTrace();
  71.         }
  72.     }
  73.     private static String encryptPassword(String decryptedPassword) {
  74.         // TODO Auto-generated method stub
  75.             MD5PasswordHasher passwordHasher=new MD5PasswordHasher();
  76.             return passwordHasher.encryptPassword(decryptedPassword);        
  77.     }
  78.     private static String decryptPassword(String asisPassword) {
  79.         // TODO Auto-generated method stub
  80.         int length = asisPassword.length();
  81.         int temp=2;
  82.         char c;
  83.         StringBuffer sb = new StringBuffer();
  84.         for(int i=0;i<asisPassword.length();i=i+2)
  85.         {
  86.             String StringToken = (asisPassword.substring(i,temp));
  87.             int intToHex = Integer.parseInt(StringToken, 16);
  88.             c = (char) intToHex;
  89.             sb.append(c);
  90.             temp=((temp==length-1)?length:(temp+2));
  91.         }
  92.  
  93.         return sb.toString();
  94.     }
  95. }
Mar 13 '08 #1
Share this Question
Share on Google+
2 Replies


10K+
P: 13,264
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?
Mar 13 '08 #2

100+
P: 539
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
Mar 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.