Hi
New to calcite, running some basic test against local mysql db
Problem: When executed a query having multiple join and group by with
calcite it takes double the time to get results as compared to getting
results from normal jdbc statement
Questions :
1. Am I doing this correctly?
2. if yes why is it taking so much time to return results
3. What is the efficient way to get results faster than normal
jdbc connection ?
Below is result from attached java code
hikariConnectionPoolTest:Time to get data from local DB using Hikari
:715719320 (0.7s)
runQueryWithCalcite:Time to get data from local DB using Calcite Prepared
statement 1:5491470347 (5.5 sec)
runQueryWithCalcite2: Time to get data from local DB using Calcite
2045749851 (2.0)
runQueryWithCalcite3: Time to get data from local DB using Calcite
:1788924134 (1.7)
public class ConnectionManagerTest {
private static final String
QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL =
"select lc.id, pb.id as proficiency_band_id, count(grls.id) as
test_count " +
" from litpro.grl_students grls " +
" left join litpro.grades_proficiencies gp on
(grls.grade_id = gp.grade_id " +
" and (grls.grl between gp.grl_low and gp.grl_high)) " +
" left join litpro.proficiency_bands pb on
gp.proficiency_band_id = pb.id " +
" inner join litpro.users u on grls.student_id = u.id " +
" inner join litpro.schools s on u.school_id = s.id " +
" inner join litpro.grl_lf_classes grlc on grlc.grl_id =
grls.id " +
" inner join litpro.lf_classes lc on grlc.lf_class_id =
lc.id " +
" inner join litpro.grades g on grls.grade_id = g.id " +
" group by lc.id, pb.id order by lc.id";
private static final String
QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL_2 =
"select \"lc\".\"id\", \"pb\".\"id\" as proficiency_band_id,
count(\"grls\".\"id\") as test_count " +
" from \"litpro\".\"grl_students\" as \"grls\" " +
" left join \"litpro\".\"grades_proficiencies\" as \"gp\"
on (\"grls\".\"grade_id\" = \"gp\"" +
".\"grade_id\" " +
" and (\"grls\".\"grl\" between \"gp\".\"grl_low\" and
\"gp\".\"grl_high\")) " +
" left join \"litpro\".\"proficiency_bands\" as \"pb\" on
\"gp\".\"proficiency_band_id\" = \"pb\"" +
".\"id\" " +
" inner join \"litpro\".\"users\" as \"u\" on
\"grls\".\"student_id\" = \"u\".\"id\" " +
" inner join \"litpro\".\"schools\" as \"s\" on
\"u\".\"school_id\" = \"s\".\"id\" " +
" inner join \"litpro\".\"grl_lf_classes\" as \"grlc\" on
\"grlc\".\"grl_id\" = \"grls\".\"id\" " +
" inner join \"litpro\".\"lf_classes\" as \"lc\" on
\"grlc\".\"lf_class_id\" = \"lc\".\"id\" " +
" inner join \"litpro\".\"grades\" as \"g\" on
\"grls\".\"grade_id\" = \"g\".\"id\" " +
" group by \"lc\".\"id\", \"pb\".\"id\" order by
\"lc\".\"id\"";
public static void main(String[] args) {
ConnectionManagerTest connectionManagerTest = new
ConnectionManagerTest();
try {
connectionManagerTest.hikariConnectionPoolTest();
connectionManagerTest.runQueryWithCalcite();
connectionManagerTest.runQueryWithCalcite2();
connectionManagerTest.runQueryWithCalcite3();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
public void hikariConnectionPoolTest() {
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
try {
final long startTime = System.nanoTime();
DataSource dataSource = ConnectionManager.getDataSource();
connection = dataSource.getConnection();
pstmt =
connection.prepareStatement(QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL);
resultSet = pstmt.executeQuery();
final long duration = System.nanoTime() - startTime;
System.out.println("hikariConnectionPoolTest:Time to get data from
local DB using Hikari :" + duration);
//printData(resultSet );
} catch (Exception e) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
pstmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void runQueryWithCalcite() throws ClassNotFoundException,
SQLException {
final long startTime = System.nanoTime();
final String dbUrl = "jdbc:mysql://localhost/litpro";
Class.forName("org.apache.calcite.jdbc.Driver");
Properties info = new Properties();
info.setProperty("lex", "JAVA");
Connection connection = DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
final DataSource ds = JdbcSchema.dataSource(dbUrl,
"com.mysql.jdbc.Driver", "root", "root");
rootSchema.add("litpro", JdbcSchema.create(rootSchema, "litpro", ds,
null, null));
PreparedStatement stmt3 = calciteConnection.prepareStatement
(QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL_2);
ResultSet resultSet = stmt3.executeQuery();
final long duration = System.nanoTime() - startTime;
System.out.println("runQueryWithCalcite:Time to get data from local DB
using Calcite Prepared statement 1:" +
duration);
// printData(resultSet);
}
public void runQueryWithCalcite2() throws ClassNotFoundException,
SQLException {
final long startTime = System.nanoTime();
final String dbUrl = "jdbc:mysql://localhost:3306/litpro";
Class.forName("org.apache.calcite.jdbc.Driver");
Properties info = new Properties();
info.setProperty("lex", "JAVA");
Connection connection = DriverManager.getConnection("jdbc:calcite:",
info);
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
Class.forName("com.mysql.jdbc.Driver");
BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl(dbUrl);
dataSource.setUsername("root");
dataSource.setPassword("root");
Schema schema = JdbcSchema.create(rootSchema, "litpro", dataSource,
null, null);
rootSchema.add("litpro", schema);
Statement statement = calciteConnection.createStatement();
ResultSet resultSet =
statement.executeQuery(QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL);
final long duration = System.nanoTime() - startTime;
//printData(resultSet);
System.out.println("runQueryWithCalcite2: Time to get data from local
DB using Calcite " +
duration);
// resultSet.close();
// statement.close();
// connection.close();
}
public void runQueryWithCalcite3() throws ClassNotFoundException {
final String dbUrl = "jdbc:mysql://localhost:3306/litpro";
try {
final long startTime = System.nanoTime();
Connection connection =
DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
final DataSource ds1 =
JdbcSchema.dataSource(dbUrl, "com.mysql.jdbc.Driver",
"root", "root");
rootSchema.add("litpro",
JdbcSchema.create(rootSchema, "litpro", ds1, null, null));
Statement stmt = connection.createStatement();
ResultSet rs =
stmt.executeQuery(QUERY_READING_PROFICIENCY_DISTRIBUTION_FOR_GRADE_GRL_2);
//printData(rs); ;
final long duration = System.nanoTime() - startTime;
System.out.println("runQueryWithCalcite3: Time to get data from
local DB using Calcite :" + duration);
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
}
private void printData(ResultSet resultSet) {
try {
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + "," +
resultSet.getString(2) + "," + resultSet.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
