/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to you under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package net.hydromatic.optiq;
import net.hydromatic.optiq.impl.java.ReflectiveSchema;
import net.hydromatic.optiq.jdbc.OptiqConnection;
import org.openjdk.jmh.annotations.*;
import java.sql.*;
import java.util.*;
/**
* Compares {@link java.sql.Statement} vs {@link java.sql.PreparedStatement}.
*
* <p>This package contains micro-benchmarks to test calcite performance.
*
* <p>To run this and other benchmarks:
*
* <blockquote>
* <code>mvn package &&
* java -jar ./target/ubenchmarks.jar -wi 5 -i 5 -f 1</code>
* </blockquote>
*
* <p>To run with profiling:
*
* <blockquote>
* <code>java -Djmh.stack.lines=10 -jar ./target/ubenchmarks.jar
* -prof hs_comp,hs_gc,stack -f 1 -wi 5</code>
* </blockquote>
*/
public class StatementTest {
/**
* Connection to be used during tests.
*/
@State(Scope.Thread)
@BenchmarkMode(Mode.AverageTime)
public static class HrConnection {
Connection con;
int id;
HrSchema hr = new HrSchema();
Random rnd = new Random();
{
try {
Class.forName("net.hydromatic.optiq.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new IllegalStateException(e);
}
Connection connection;
try {
Properties info = new Properties();
info.put("lex", "JAVA");
info.put("quoting", "DOUBLE_QUOTE");
connection = DriverManager.getConnection("jdbc:calcite:", info);
} catch (SQLException e) {
throw new IllegalStateException(e);
}
OptiqConnection optiqConnection;
try {
optiqConnection = connection.unwrap(OptiqConnection.class);
} catch (SQLException e) {
throw new IllegalStateException(e);
}
final SchemaPlus rootSchema = optiqConnection.getRootSchema();
rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
try {
optiqConnection.setSchema("hr");
} catch (SQLException e) {
throw new IllegalStateException(e);
}
con = connection;
}
@Setup(Level.Iteration)
public void pickEmployee() {
id = hr.emps[rnd.nextInt(4)].empid;
}
}
/**
* Tests performance of reused execution of prepared statement.
*/
public static class HrPreparedStatement extends HrConnection {
PreparedStatement ps;
{
try {
ps = con.prepareStatement("select name from emps where empid = ?");
} catch (SQLException e) {
throw new IllegalStateException(e);
}
}
}
@GenerateMicroBenchmark
public String prepareBindExecute(HrConnection state) throws SQLException {
Connection con = state.con;
Statement st = null;
ResultSet rs = null;
String ename = null;
try {
final PreparedStatement ps =
con.prepareStatement("select name from emps where empid = ?");
st = ps;
ps.setInt(1, state.id);
rs = ps.executeQuery();
rs.next();
ename = rs.getString(1);
} finally {
close(rs, st);
}
return ename;
}
@GenerateMicroBenchmark
public String bindExecute(HrPreparedStatement state)
throws SQLException {
PreparedStatement st = state.ps;
ResultSet rs = null;
String ename = null;
try {
st.setInt(1, state.id);
rs = st.executeQuery();
rs.next();
ename = rs.getString(1);
} finally {
close(rs, null); // Statement is not closed
}
return ename;
}
@GenerateMicroBenchmark
public String executeQuery(HrConnection state) throws SQLException {
Connection con = state.con;
Statement st = null;
ResultSet rs = null;
String ename = null;
try {
st = con.createStatement();
rs = st.executeQuery("select name from emps where empid = " + state.id);
rs.next();
ename = rs.getString(1);
} finally {
close(rs, st);
}
return ename;
}
@GenerateMicroBenchmark
public String forEach(HrConnection state) throws SQLException {
final Employee[] emps = state.hr.emps;
for (Employee emp : emps) {
if (emp.empid == state.id) {
return emp.name;
}
}
return null;
}
private static void close(ResultSet rs, Statement st) {
if (rs != null) {
try { rs.close(); } catch (SQLException e) { /**/ }
}
if (st != null) {
try { st.close(); } catch (SQLException e) { /**/ }
}
}
// Disable checkstyle, so it doesn't complain about fields like "customer_id".
//CHECKSTYLE: OFF
public static class HrSchema {
@Override
public String toString() {
return "HrSchema";
}
public final Employee[] emps = {
new Employee(100, 10, "Bill", 10000, 1000),
new Employee(200, 20, "Eric", 8000, 500),
new Employee(150, 10, "Sebastian", 7000, null),
new Employee(110, 10, "Theodore", 11500, 250),
};
public final Department[] depts = {
new Department(10, "Sales", Arrays.asList(emps[0], emps[2])),
new Department(30, "Marketing", Collections.<Employee>emptyList()),
new Department(40, "HR", Collections.singletonList(emps[1])),
};
}
public static class Employee {
public final int empid;
public final int deptno;
public final String name;
public final float salary;
public final Integer commission;
public Employee(int empid, int deptno, String name, float salary,
Integer commission) {
this.empid = empid;
this.deptno = deptno;
this.name = name;
this.salary = salary;
this.commission = commission;
}
public String toString() {
return "Employee [empid: " + empid + ", deptno: " + deptno
+ ", name: " + name + "]";
}
}
public static class Department {
public final int deptno;
public final String name;
public final List<Employee> employees;
public Department(
int deptno, String name, List<Employee> employees) {
this.deptno = deptno;
this.name = name;
this.employees = employees;
}
public String toString() {
return "Department [deptno: " + deptno + ", name: " + name
+ ", employees: " + employees + "]";
}
}
}
// End StatementTest.java