I'm checking some sql queries performance variation between raw query, PreparedStatement. As I'm beginner using JMH couldn't get how internal flow calling our methods. Getting confused in below code :-
private static final String url = "jdbc:postgresql://localhost:5432/postgres";
static Connection getConnection(){
try {
conn = DriverManager.getConnection(url, user, password);
System.out.println("Connected to the PostgresSQL server successfully. ");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
@Benchmark
@OutputTimeUnit(TimeUnit.MILLISECONDS)
@BenchmarkMode(Mode.SingleShotTime)
@Fork(value = 1, warmups = 5)
public static String executeSql() {
if(conn == null) {
conn = getConnection();
}
String query = "select * from EmpDetails where EMP_ID=123 ";
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
return rs.getString(1);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return "";
}
Results :-
Benchmark mode: Single shot invocation time
Benchmark: com.mnk.BenchMarking.executeSql
Run progress: 0.00% complete, ETA 00:00:00
Warmup Fork: 1 of 5
Iteration 1: Connected to the PostgresSQL server successfully.
190.550 ms/op
Run progress: 10.00% complete, ETA 00:00:11 Warmup Fork: 2 of 5 Iteration 1: Connected to the PostgresSQL server successfully.
174.217 ms/op
Run progress: 20.00% complete, ETA 00:00:09 Warmup Fork: 3 of 5 Iteration 1: Connected to the PostgresSQL server successfully.
175.219 ms/op
Run progress: 30.00% complete, ETA 00:00:08 Warmup Fork: 4 of 5 Iteration 1: Connected to the PostgresSQL server successfully.
180.964 ms/op
Run progress: 40.00% complete, ETA 00:00:07 Warmup Fork: 5 of 5 Iteration 1: Connected to the PostgresSQL server successfully.
163.894 ms/op
Here I'm expecting getConnection() method to call only once and that connection object will be reused every iteration. But getConnection() method too called for every iteration which is causing invalid results. Also due to this I couldn't evaluate executeQuery() performance.
Kindly someone give insights about how I can approach this problem.