netslt
asked on
Optimize Speed for MySQL Queries in Java
Hi
I have a small Java program that loops a million times and during each loop it makes about 8 mysql select queries and 8 mysql insert queries.
I do the queries like this:
This is the select query:
==
public static int resultcount(String query) {
int result;
int rowcount = 0;
try {
System.out.println("pre instance resultcount");
java.lang.Class.forName("c om.mysql.j dbc.Driver ").newInst ance();
//new com.mysql.jdbc.Driver();
Connection con = DriverManager.getConnectio n(url, userName, password);
//String sql = "select `domain` from domaintest limit 0,1";
String sql = query;
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
try {
if (rs.last()) {
rowcount = rs.getRow();
//rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
rs.first();
returnedid = rs.getInt("id");
}
} catch (Exception ex) {
System.out.println("error resultcount rs:" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
returnedid = 0;
}
System.out.println("found " + rowcount + " rows & id = " + returnedid + " -- SQL " + query);
int i = 0;
con.close();
} catch (Exception ex) {
//NewJFrame.showMessageDia log(null, ex.getMessage(),"Error");
System.out.println("error resultcount :" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
}
==
This is the insert query:
==
public static int mysqlinsert(String query) {
int result = 0;
int number2 = 0;
try {
System.out.println("pre insert " + query);
java.lang.Class.forName("c om.mysql.j dbc.Driver ").newInst ance();
//new com.mysql.jdbc.Driver();
Connection con = DriverManager.getConnectio n(url, userName, password);
//String sql = "select `domain` from domaintest limit 0,1";
String sql = query;
PreparedStatement ps = con.prepareStatement(sql);
Statement st = (Statement) con.createStatement();
st.executeUpdate(query, Statement.RETURN_GENERATED _KEYS);
ResultSet rs = st.getGeneratedKeys();
if (rs.next()) {
result = rs.getInt(1);
}
System.out.println("Insert ID:" + result);
con.close();
} catch (Exception ex) {
//NewJFrame.showMessageDia log(null, ex.getMessage(),"Error");
System.out.println("error mysqlinsert :" + ex.getMessage() + " " + ex.getStackTrace() + " -- SQL -- " + query);
}
==
Is there a way to seed things up - maybe by leaving the connection open, or doing other things that bring more speed?
The way it is now it will take ages to process all date even if i run 10 instances of the program....
Thanks
I have a small Java program that loops a million times and during each loop it makes about 8 mysql select queries and 8 mysql insert queries.
I do the queries like this:
This is the select query:
==
public static int resultcount(String query) {
int result;
int rowcount = 0;
try {
System.out.println("pre instance resultcount");
java.lang.Class.forName("c
//new com.mysql.jdbc.Driver();
Connection con = DriverManager.getConnectio
//String sql = "select `domain` from domaintest limit 0,1";
String sql = query;
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
try {
if (rs.last()) {
rowcount = rs.getRow();
//rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
rs.first();
returnedid = rs.getInt("id");
}
} catch (Exception ex) {
System.out.println("error resultcount rs:" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
returnedid = 0;
}
System.out.println("found " + rowcount + " rows & id = " + returnedid + " -- SQL " + query);
int i = 0;
con.close();
} catch (Exception ex) {
//NewJFrame.showMessageDia
System.out.println("error resultcount :" + ex.getMessage() + " " + ex.getStackTrace() + " --SQL-- " + query);
}
==
This is the insert query:
==
public static int mysqlinsert(String query) {
int result = 0;
int number2 = 0;
try {
System.out.println("pre insert " + query);
java.lang.Class.forName("c
//new com.mysql.jdbc.Driver();
Connection con = DriverManager.getConnectio
//String sql = "select `domain` from domaintest limit 0,1";
String sql = query;
PreparedStatement ps = con.prepareStatement(sql);
Statement st = (Statement) con.createStatement();
st.executeUpdate(query, Statement.RETURN_GENERATED
ResultSet rs = st.getGeneratedKeys();
if (rs.next()) {
result = rs.getInt(1);
}
System.out.println("Insert
con.close();
} catch (Exception ex) {
//NewJFrame.showMessageDia
System.out.println("error mysqlinsert :" + ex.getMessage() + " " + ex.getStackTrace() + " -- SQL -- " + query);
}
==
Is there a way to seed things up - maybe by leaving the connection open, or doing other things that bring more speed?
The way it is now it will take ages to process all date even if i run 10 instances of the program....
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It already looks as though a JOIN should be used for the two queries
ASKER
hi
these 2 queries are independent, these are just 2 samples of the type of queries i make with the resultcount(String query) method.
So you see these are basic queries which probably can not be optimized.
Thats why I ask about how to leave the connection open for all queries happening through one loop.
Any idea on that?
these 2 queries are independent, these are just 2 samples of the type of queries i make with the resultcount(String query) method.
So you see these are basic queries which probably can not be optimized.
Thats why I ask about how to leave the connection open for all queries happening through one loop.
Any idea on that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that helped a lot, everything is a lot faster now.
You're welcome!
ASKER
But how do you do that?
==
The queries look like this:
For the selects:
SQL: select * from table1 where domain='tumblr.com'
SQL: select * from table2 where fullhostname ='blabla.tumblr.com'
Example for the inserts:
insert into table2 (hostname,fullhostname,dom
Thanks for some help!