jOOQ Benchmarks

gistfile1.txt

package org.jooq.test.benchmark;

import static org.jooq.example.db.h2.Tables.AUTHOR;
import static org.jooq.example.db.h2.Tables.BOOK;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import org.jooq.DSLContext;
import org.jooq.Select;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.Fork;
import org.openjdk.jmh.annotations.Level;
import org.openjdk.jmh.annotations.Measurement;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.Setup;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.annotations.TearDown;
import org.openjdk.jmh.annotations.Warmup;
import org.openjdk.jmh.infra.Blackhole;

@Fork(value = 3, jvmArgsAppend = "-Djmh.stack.lines=3")
@Warmup(iterations = 5)
@Measurement(iterations = 7)
public class ComparingWithJDBCBenchmark {

    static Select<?> select(DSLContext ctx) {
        return ctx
            .select(BOOK.ID, BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
            .from(BOOK)
            .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
            .where(AUTHOR.ID.lt(3))
            .orderBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.TITLE);
    }

    @State(Scope.Benchmark)
    public static class BenchmarkState {

        Connection connection;
        DSLContext ctx;
        Select<?>  select;
        Select<?>  selectKeepStatement;

        @Setup(Level.Trial)
        public void setup() throws Exception {
            try (InputStream is = BenchmarkState.class.getResourceAsStream("/config.properties")) {
                Properties p = new Properties();
                p.load(is);
                connection = DriverManager.getConnection(
                    p.getProperty("db.url"),
                    p.getProperty("db.username"),
                    p.getProperty("db.password")
                );
            }

            ctx = DSL.using(connection, new Settings().withExecuteLogging(false));
            select = select(ctx);
            selectKeepStatement = (Select<?>) select(ctx).keepStatement(true);
        }

        @TearDown(Level.Trial)
        public void teardown() throws Exception {
            connection.close();
        }
    }

    @Benchmark
    public void testQueryWithFewResultsJOOQDSLBuildQueryAfresh(Blackhole blackhole, BenchmarkState state) {
        blackhole.consume(
            state.ctx.select(BOOK.ID, BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
                     .from(BOOK)
                     .join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                     .where(AUTHOR.ID.lt(3))
                     .orderBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.TITLE)
                     .fetch()
        );
    }

    @Benchmark
    public void testQueryWithFewResultsJOOQDSLReuseQuery(Blackhole blackhole, BenchmarkState state) {
        blackhole.consume(
            state.select.fetch()
        );
    }

    @Benchmark
    public void testQueryWithFewResultsJOOQReuseQueryKeepStatement(Blackhole blackhole, BenchmarkState state) {
        blackhole.consume(
            state.selectKeepStatement.fetch()
        );
    }

    @Benchmark
    public void testQueryWithFewResultsJDBC(Blackhole blackhole, BenchmarkState state) throws Exception {
        try (PreparedStatement stmt = state.connection.prepareStatement(
            "SELECT book.id, book.title, author.first_name, author.last_name "
          + "FROM book "
          + "JOIN author ON book.author_id = author.id "
          + "WHERE author.id < ? "
          + "ORDER BY author.first_name, author.last_name, book.title")) {

            stmt.setInt(1, 3);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    blackhole.consume(rs.getInt(1));
                    blackhole.consume(rs.wasNull());
                    blackhole.consume(rs.getString(2));
                    blackhole.consume(rs.getString(3));
                    blackhole.consume(rs.getString(4));
                }
            }
        }
    }

//    @Benchmark
//    public void testQueryWithManyResultsJOOQDSL(Blackhole blackhole, BenchmarkState state) {
//        Book b1 = BOOK.as("b1");
//        Book b2 = BOOK.as("b2");
//        Book b3 = BOOK.as("b3");
//        Book b4 = BOOK.as("b4");
//        Book b5 = BOOK.as("b5");
//        Book b6 = BOOK.as("b6");
//
//        blackhole.consume(
//            state.ctx.select(b1.ID, b2.ID, b3.ID, b4.ID, b5.ID, b6.ID)
//                     .from(b1, b2, b3, b4, b5, b6)
//                     .orderBy(b1.ID, b2.ID, b3.ID, b4.ID, b5.ID, b6.ID)
//                     .fetch()
//        );
//    }
//
//    @Benchmark
//    public void testQueryWithManyResultsJOOQPlainSQL(Blackhole blackhole, BenchmarkState state) {
//        blackhole.consume(
//            state.ctx.fetch(
//                "SELECT b1.id, b2.id, b3.id, b4.id, b5.id, b6.id "
//              + "FROM book b1, book b2, book b3, book b4, book b5, book b6 "
//              + "ORDER BY b1.id, b2.id, b3.id, b4.id, b5.id, b6.id")
//        );
//    }
//
//    @Benchmark
//    public void testQueryWithManyResultsJDBC(Blackhole blackhole, BenchmarkState state) throws Exception {
//        try (PreparedStatement stmt = state.connection.prepareStatement(
//            "SELECT b1.id, b2.id, b3.id, b4.id, b5.id, b6.id "
//          + "FROM book b1, book b2, book b3, book b4, book b5, book b6 "
//          + "ORDER BY b1.id, b2.id, b3.id, b4.id, b5.id, b6.id")) {
//
//            try (ResultSet rs = stmt.executeQuery()) {
//                while (rs.next()) {
//                    blackhole.consume(rs.getInt(1));
//                    blackhole.consume(rs.wasNull());
//                    blackhole.consume(rs.getInt(2));
//                    blackhole.consume(rs.wasNull());
//                    blackhole.consume(rs.getInt(3));
//                    blackhole.consume(rs.wasNull());
//                    blackhole.consume(rs.getInt(4));
//                    blackhole.consume(rs.wasNull());
//                    blackhole.consume(rs.getInt(5));
//                    blackhole.consume(rs.wasNull());
//                    blackhole.consume(rs.getInt(6));
//                    blackhole.consume(rs.wasNull());
//                }
//            }
//        }
//    }
}