results.txt

3.17.0-SNAPSHOT-20220330005052
Benchmark                                Mode  Cnt     Score     Error  Units
SubqueryBenchmark.fastTranslate          avgt    3     1.570 ±   0.331  ms/op
SubqueryBenchmark.slowTranslate          avgt    3    20.642 ±   7.235  ms/op
SubqueryBenchmark.superSlowTranslate     avgt    3  1004.046 ± 221.523  ms/op
SubqueryBenchmark.superSlowTranslateFix  avgt    3   346.528 ± 130.967  ms/op

3.16.5
Benchmark                                Mode  Cnt    Score     Error  Units
SubqueryBenchmark.fastTranslate          avgt    3    1.591 ±   1.012  ms/op
SubqueryBenchmark.slowTranslate          avgt    3   22.331 ±   3.458  ms/op
SubqueryBenchmark.superSlowTranslate     avgt    3  889.232 ± 317.375  ms/op
SubqueryBenchmark.superSlowTranslateFix  avgt    3  288.679 ±  78.107  ms/op

3.15.9
Benchmark                                Mode  Cnt    Score     Error  Units
SubqueryBenchmark.fastTranslate          avgt    3    1.628 ±   1.379  ms/op
SubqueryBenchmark.slowTranslate          avgt    3   21.291 ±  11.842  ms/op
SubqueryBenchmark.superSlowTranslate     avgt    3  916.638 ± 201.604  ms/op
SubqueryBenchmark.superSlowTranslateFix  avgt    3  290.706 ± 152.454  ms/op

3.14.15
Benchmark                                Mode  Cnt    Score     Error  Units
SubqueryBenchmark.fastTranslate          avgt    3    1.033 ±   0.244  ms/op
SubqueryBenchmark.slowTranslate          avgt    3   21.262 ±   7.593  ms/op
SubqueryBenchmark.superSlowTranslate     avgt    3  573.610 ± 430.635  ms/op
SubqueryBenchmark.superSlowTranslateFix  avgt    3  133.100 ±  22.160  ms/op

SubqueryBenchmark.java

package org.jooq.mcve.test.java;

import lombok.SneakyThrows;
import lombok.experimental.UtilityClass;
import org.jooq.*;
import org.jooq.impl.*;
import org.openjdk.jmh.annotations.Mode;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;

import java.lang.reflect.Modifier;
import java.lang.reflect.Proxy;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

import static org.jooq.mcve.test.java.MyTable.TABLE;

@State(Scope.Benchmark)
@BenchmarkMode(Mode.AverageTime)
@OutputTimeUnit(TimeUnit.MILLISECONDS)
@Fork(value = 1)
@Warmup(iterations = 2, time = 5)
@Measurement(iterations = 3, time = 10)
public class SubqueryBenchmark {
    private DSLContext ctx;

    @Setup(Level.Invocation)
    public void setup() {
        System.getProperties().setProperty("org.jooq.no-logo", "true");
        System.getProperties().setProperty("org.jooq.no-tips", "true");
        ctx = DSL.using(SQLDialect.POSTGRES);
    }

    @Benchmark
    public void superSlowTranslate(Blackhole blackhole) {
        superSlow(blackhole);
    }

    @Benchmark
    @Measurement(iterations = 3, time = 60)
    public void superSlowTranslateFix(Blackhole blackhole) {
        FixJooqSlowAliasing.fix();
        superSlow(blackhole);
    }

    private void superSlow(Blackhole blackhole) {
        Field<Integer> rn = DSL.rowNumber().over().as("rn");
        TableLike<Record> sub = DSL
                .select(TABLE.fields())
                .select(rn)
                .from(TABLE);
        List<? extends Field<?>> columns = TABLE.fieldStream().map(sub::field).collect(Collectors.toList());

        SelectConditionStep<Record> select = ctx
                .select(columns)
                .from(sub)
                .where(sub.field(rn).gt(1));

        blackhole.consume(select.getSQL());
    }

    @Benchmark
    @Measurement(iterations = 3, time = 60)
    public void slowTranslate(Blackhole blackhole) {
        Field<Integer> rn = DSL.rowNumber().over().as("rn");
        TableLike<Record> sub = DSL
                .select(TABLE.fields())
                .select(rn)
                .from(TABLE)
                .asTable("sub");
        List<? extends Field<?>> columns = TABLE.fieldStream().map(sub::field).collect(Collectors.toList());

        SelectConditionStep<Record> select = ctx
                .select(columns)
                .from(sub)
                .where(sub.field(rn).gt(1));
        blackhole.consume(select.getSQL());
    }

    @Benchmark
    public void fastTranslate(Blackhole blackhole) {
        Field<Integer> rn = DSL.rowNumber().over().as("rn");
        Table<Record> sub = DSL
                .select(TABLE.fields())
                .select(rn)
                .from(TABLE)
                .asTable("sub");
        List<? extends Field<?>> columns = TABLE.fieldStream()
                .map(f -> DSL.field(DSL.name(sub.getName(), f.getName()), f.getDataType()))
                .collect(Collectors.toList());

        SelectConditionStep<Record> select = ctx
                .select(columns)
                .from(sub)
                .where(sub.field(rn).gt(1));
        blackhole.consume(select.getSQL());
    }

    public static void main(String[] args) throws Exception {
        org.openjdk.jmh.Main.main(args);
    }
}

class MyTable extends CustomTable<MyRecord> {
    public final TableField<MyRecord, Integer> F;
    public final List<TableField<MyRecord, Integer>> PAYLOAD = new ArrayList<>();

    static MyTable TABLE = new MyTable();

    protected MyTable() {
        super(DSL.name("my_table"));

        F = createField(DSL.name("f"), SQLDataType.INTEGER);

        for (int i = 0; i < 1000; i++) {
            PAYLOAD.add(createField(DSL.name("p" + i), SQLDataType.INTEGER));
        }
    }

    @Override
    public Class<MyRecord> getRecordType() {
        return MyRecord.class;
    }
}

class MyRecord extends CustomRecord<MyRecord> {
    protected MyRecord() {
        super(TABLE);
    }
}

@UtilityClass
class FixJooqSlowAliasing {
    /**
     * It fixes slow alias rendering in jooq Tools class. Tools uses CTX static field of DSLContext type
     * to render QueryParty and then creates hashCode of the rendered String. And it only uses
     * render method from the CTX field. This sets CTX to our own DSLContext object which implements
     * only render method which renders the QueryPart to identity hash code. Which is much faster
     * but it does not render sql and it would be a problem if it was used anywhere else than here.
     */
    @SneakyThrows
    void fix() {
        Class<?> toolsClass = DSLContext.class.getClassLoader().loadClass("org.jooq.impl.Tools");
        java.lang.reflect.Field field = toolsClass.getDeclaredField("CTX");
        setNonFinal(field);
        field.setAccessible(true);
        field.set(null, CTX);
    }

    private void setNonFinal(java.lang.reflect.Field field) throws NoSuchFieldException, IllegalAccessException {
        java.lang.reflect.Field modifiersField = java.lang.reflect.Field.class.getDeclaredField("modifiers");
        modifiersField.setAccessible(true);
        modifiersField.setInt(field, field.getModifiers() & ~Modifier.FINAL);
    }

    private static final DSLContext CTX = createCtx();

    @SneakyThrows
    private static DSLContext createCtx() {
        DefaultConfiguration configuration = new DefaultConfiguration();

        return (DSLContext) Proxy.newProxyInstance(DSLContext.class.getClassLoader(), new Class[]{DSLContext.class}, (proxy, method, args) -> {
            if (method.getName().equals("render") && Arrays.equals(method.getParameterTypes(), new Class[]{QueryPart.class})) {
                return Integer.toString(System.identityHashCode(args[0]));
            }
            if (method.getName().equals("configuration") && method.getParameterTypes().length == 0) {
                return configuration;
            }

            throw new IllegalStateException("Internal error in workaround code in jooq library. Method " + method + " was called.");
        });
    }
}