Best way to run multiple queries per second on database, performance wise?
- by Michael Joell
I am currently using Java to insert and update data multiple times per second. Never having used databases with Java, I am not sure what is required, and how to get the best performance.
I currently have a method for each type of query I need to do (for example, update a row in a database). I also have a method to create the database connection. Below is my simplified code.
public static void addOneForUserInChannel(String channel, String username) throws SQLException {
Connection dbConnection = null;
PreparedStatement ps = null;
String updateSQL = "UPDATE " + channel + "_count SET messages = messages + 1 WHERE username = ?";
try {
dbConnection = getDBConnection();
ps = dbConnection.prepareStatement(updateSQL);
ps.setString(1, username);
ps.executeUpdate();
} catch(SQLException e) {
System.out.println(e.getMessage());
} finally {
if(ps != null) {
ps.close();
}
if(dbConnection != null) {
dbConnection.close();
}
}
}
And my DB connection
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
This seems to be working fine for now, with about 1-2 queries per second, but I am worried that once I expand and it is running many more, I might have some issues. My questions:
Is there a way to have a persistent database connection throughout the entire run time of the process? If so, should I do this?
Are there any other optimizations that I should do to help with performance?
Thanks
