Skip to main content

Database Retrieval Speed

No replies
abubakargurnah
Offline
Joined: 2011-06-07
Points: 0

Hi Guys,
Need your help, I am extracting 5Million record from (Oracle Table )table and inserting into MySQL table. But the insert is very taking very long. I was searching and googling, and found out setFetchSize can help me but it does for only couple of record, that it. Please I will post my code here, please review it and and give me an ideas please;
Class 1
<code>
String driver = "com.mysql.jdbc.Driver";
url = "jdbc:mysql://" + host + ":" + port + "/" + database;
Class.forName(driver);
conn = DriverManager.getConnection(url, uname, pwd);
...
stat = conn.createStatement();
</code>
Class 2
<code>

String query = ""
+ "oracle query";

ResultSet rsSub = calldb.executeQuery(query); //this is just a function calling class1 query
int count = 0;
String qact;
String act;
StringBuilder sb = new StringBuilder();

{

BufferedWriter bw = null;

try {
bw = new BufferedWriter(new FileWriter(GlobalConfig.getPath() + "sub.dump"));
rsSub.setFetchSize(500); //my problem is here where should i put this inorder to make my retrieval faster

long start = System.currentTimeMillis();
while (rsSub.next()) {

//doing somethings around here
if (count % 10000 == 0) {
long end = System.currentTimeMillis();
System.out.println((end - start) + " ms");
start = System.currentTimeMillis();

}
}

} catch (IOException | SQLException ex) {
ex.printStackTrace();
} finally {
try {

bw.close();

} catch (IOException ex) {
ex.printStackTrace();
}
}
}

}

</code>

please I need your inputs