I have a java program that needs to iterate through a HashMap to get a parameters that are then used to query the MySQL database.
The code is as follows:
Iterator<Entry<String, Double>>it = ws.entrySet().iterator();
Connection con = null;
while(it.hasNext())
{
Entry<String, Double>pairs = it.next();
PreparedStatement ps = con.prepareStatement("select doc_freq from lookup where word=?");
ps.setString(1, pairs.getKey());
ResultSet rs = ps.executeQuery();
}
The process of repeatedly accessing the database for every iteration of the loop (which is about 500 times) is slowing down my application. Is there any way I can send all these parameters at once so that i access the database only once?
Considering ws is a map, you can do a single query that way:
Connection con = getConnection();
Set<String> ks = ws.keySet();
if (ks.size() > 0) {
StringBuilder inStatement = new StringBuilder("?");
for (int i = 1; i < ks.size(); i++) {
inStatement.append(", ?");
}
PreparedStatement ps = con.prepareStatement("select doc_freq from lookup where word in (" + inStatement.toString() + ")");
int k = 1;
for (String key : keySet) {
ps.setString(k++, key);
}
ResultSet rs = ps.executeQuery();
}
It isn't clear from the question why it's slow but one usual problem is the overhead of each transaction.
You can set innodb_flush_log_at_trx_commit to 0 or 2 and everything will go faster. Be warned though that the default value of 1 is the only ACID compliant setting. For most setups 2 is a perfectly fine value.
set global innodb_flush_log_at_trx_commit = 2;
Prepare the statement once then iterate and set parameter to it then execute. This is from javadoc
A SQL statement with or without IN parameters can be pre-compiled and
stored in a PreparedStatement object. This object can then be used
to efficiently execute this statement multiple times.
This method is optimized for handling parametric SQL statements that
benefit from precompilation. If the driver supports precompilation...
Iterator<Entry<String, Double>>it = ws.entrySet().iterator();
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement("select doc_freq from lookup where word=?");
while(it.hasNext())
{
Entry<String, Double>pairs = it.next();
ps.setString(1, pairs.getKey());
ResultSet rs = ps.executeQuery();
}
Ok, I'll explain that. When the query is compiled to the db it makes it faster to execute and retrieve results one by one, this is the same as executing one query with multiple parameters. performance is equal. But if you prepare, compile the same SQL query multiple types the db will create the execution plan each time you compile the query this is time offensive. That's why this techique called efficient in the docs. The term is also know as explain plan and it's created by the db to better optimize the query.
