Which approach is better performance-wise? (with regard to the usage of variable parameter markers in prepared statement in JDBC)

Question

My objective is to get the DeptNames of certain employees from the Emp table. But the number of employees is varying. Please tell if the below approach is correct and which of the below approach is better performance wise. Or is there a better way to go about it?

PreparedStatement pstmt = conn.prepareStatement("SELECT Dept from Emp WHERE EmpNum = ? ");
    for (int i = 0, len = empNumberList.size(); i < len; i++) {
      pstmt.setLong (1, empNumberList.get(i));
      ResultSet rs = pstmt.executeQuery();
    }

Or

StringBuffer buffer = new StringBuffer();
buffer.append("SELECT Dept from Emp WHERE EmpNum IN(?");
for (int i = 0, len = empNumberList.size(); i < len; i++) {
  buffer.append(",?");
}
buffer.append(")");
PreparedStatement pstmt = con.prepareStatement(buffer.toString());
for(int i = 0; i < len; i++) {
pstmt .setLong(i, empNumberList.get(i));
}
ResultSet rs = pstmt .executeQuery();

Or is there any other better way to do this? Please advice. Thanks in advance!

Ravilla

Answer 1

I would say the

"SELECT DISTINCT Dept FROM Emp WHERE EmpNum IN (" + queries + ")"

then doing the setLong()s would be the best approach and probably best performance, but to the only way to be sure about performance is to profile it.

Answer 2

Generally speaking, doing one query trumps doing several queries because the server round trip is viewed as a significant cost on most (trivial) queries.

That being said, the database vendor, JDBC or the driver may limit the number of parameters you can pass in that way so you have to be wary of that.

Like the other poster said: there's no need to filter on client. Just use DISTINCT in this caes.

Answer 3

Probably the second option as usually database queries take more time.

Besides, you have a bug in your code:

StringBuffer buffer = new StringBuffer();
buffer.append("SELECT Dept from Emp WHERE EmpNum IN(?");
for (int i = 1, len = empNumberList.size(); i < len; i++) { // one ? was written
  buffer.append(",?");
}
buffer.append(")");
PreparedStatement pstmt = con.prepareStatement(buffer.toString());
for(int i = 0; i < len; i++) {
pstmt .setLong(i+1, empNumberList.get(i)); // pstmt setXXX() are 1 based
}
ResultSet rs = pstmt .executeQuery();

Answer 4

I agree with the estimates that the second one will probably be faster.

However, if you really want to know: benchmark. Performance results are often unintuitive, and will vary between platforms, databases, architectures, and versions. So try it yourself and measure the difference.