![Setting the JDBC Assertion.setFetchSize() to 1 for Single Row Queries – Java, SQL and jOOQ. Setting the JDBC Assertion.setFetchSize() to 1 for Single Row Queries – Java, SQL and jOOQ.](https://techtoguide.com/wp-content/uploads/2023/10/A-simple-example-configuration-to-generate-package-private-jOOQ-code.png)
[ad_1]
An fascinating trace by Vladimir Sitnikov has made me take into consideration a brand new benchmark for jOOQ:
The benchmark ought to verify whether or not single row queries ought to have a JDBC Assertion.setFetchSize(1)
name made to them by default. The Javadoc of the strategy says:
Offers the JDBC driver a touch as to the variety of rows that ought to be fetched from the database when extra rows are wanted for
ResultSet
objects generated by thisAssertion
. If the worth specified is zero, then the trace is ignored. The default worth is zero.
If an ORM (e.g. jOOQ) is aware of that it’ll fetch just one row, or if it is aware of that there could be just one row, then that trace definitely is sensible. Examples in jOOQ embrace:
- When customers name
ResultQuery.fetchSingle()
, orfetchOne()
, orfetchOptional()
, or any comparable methodology, then it’s affordable to anticipate solely 0 – 1 rows to be returned. Within the case of these strategies returning greater than 1 row, an exception is thrown, so even when there are extra rows, 2 rows might be fetched at most. - When customers add a
LIMIT 1
clause on a high stage question, there can by no means be greater than 1 row. - When the question is trivial (no joins, or solely to-one joins, no
GROUP BY GROUPING SETS
, noUNION
, and so forth.) and an equality predicate on aUNIQUE
constraint is current, there may also be not more than 1 row.
The database optimiser is aware of all of these items as properly. When you add LIMIT 1
to a question, then the optimiser could be fairly anticipated to take that as a powerful trace concerning the end result set dimension. However the JDBC driver doesn’t know these items (or at the very least, it shouldn’t be anticipated to), as a result of it’s unlikely that it parses the SQL and calculates statistics on it, or considers meta knowledge for such optimisations.
So, the consumer might trace. And since that might be very tedious for customers, even higher, the ORM (e.g. jOOQ) ought to trace. Or so it appears.
Benchmarks
However ought to it? Is it actually definitely worth the hassle? Right here’s Vladimir’s evaluation concerning the pgjdbc driver, the place he wouldn’t anticipate an enchancment now, however maybe sooner or later.
Higher than making assumptions, let’s measure, utilizing a JMH benchmark. JMH is often used for microbenchmarking issues on the JVM, to check assumptions about JIT runtime behaviour. That is clearly not a microbenchmark, however I nonetheless like JMH’s strategy and output, which incorporates commonplace deviations and errors, in addition to ignores warmup penalties, and so forth.
First off, the outcomes:
As a result of benchmark outcomes can’t be revealed for some business RDBMS (at the very least not when evaluating between RDBMS), I’ve normalised the outcomes so a comparability of precise execution pace between RDBMS will not be potential. I.e. for every RDBMS, the quicker execution is 1, and the slower one is a few fraction of 1. That means, the RDBMS is just benchmarked in opposition to itself, which is honest.
The outcomes are under. We’re measuring throughput, so decrease is worse.
Db2 --- Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.677 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 MySQL ----- Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.985 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 Oracle ------ Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.485 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000 PostgreSQL ---------- Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSize1 thrpt 1.000 JDBCFetchSizeBenchmark.noFetchSize thrpt 0.998 SQL Server ---------- Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.972 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000
For every RDBMS, I’ve run a trivial question producing a single row with 1 column. Every time, I’ve re-created a JDBC Assertion
, and fetched the ResultSet
. In fetchSize1
, I’ve specified the fetch dimension trace. In noFetchSize
, I left the default untouched. As could be summarised:
In these RDBMS, there was no impact
- MySQL
- PostgreSQL
- SQL Server
In these RDBMS, issues obtained considerably worse (not higher!):
That is fairly shocking, because the benchmark consists of working all the assertion on the server, so I might have anticipated, at greatest, a negligible end result.
For this benchmark, I used to be utilizing these server and JDBC driver variations:
- Db2 11.5.6.0 with jcc-11.5.6.0
- MySQL 8.0.29 with mysql-connector-java-8.0.28
- Oracle 21c with ojdbc11-21.5.0.0
- PostgreSQL 14.1 with postgresql-42.3.3
- SQL Server 2019 with mssql-jdbc-10.2.0
The benchmark logic is right here:
bundle org.jooq.check.benchmarks.native;
import java.sql.*;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;
@Fork(worth = 1)
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class JDBCFetchSizeBenchmark {
@State(Scope.Benchmark)
public static class BenchmarkState {
Connection connection;
@Setup(Degree.Trial)
public void setup() throws Exception {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres",
"postgres",
"check"
);
}
@TearDown(Degree.Trial)
public void teardown() throws Exception {
connection.shut();
}
}
@FunctionalInterface
interface ThrowingConsumer<T> {
void settle for(T t) throws SQLException;
}
personal void run(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Assertion> c
) throws SQLException {
strive (Assertion s = state.connection.createStatement()) {
c.settle for(s);
strive (ResultSet rs = s.executeQuery(
"choose title from t_book the place id = 1")
) {
whereas (rs.subsequent())
blackhole.devour(rs.getString(1));
}
}
}
@Benchmark
public void fetchSize1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
run(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void noFetchSize(Blackhole blackhole, BenchmarkState state)
throws SQLException {
run(blackhole, state, s -> {});
}
}
A number of remarks:
- The question is under no circumstances consultant of a manufacturing workload. But when issues did get improved by the
fetchSize
flag, the advance ought to have manifested - The benchmark didn’t use ready statements, which might have eliminated some side-effects, or added some side-effects. Be at liberty to repeat the benchmark utilizing ready statements.
- It isn’t but understood why issues didn’t matter in some drivers, or why they did in others. For the conclusion, the “why” will not be too essential, as a result of nothing might be modified because of this weblog publish. If you understand why (the db2 driver and ojdbc code isn’t open supply, regrettably), I’d be curious.
Conclusion
Optimisations are a difficult beast. Some issues appear to make plenty of sense when reasoning about them, however in precise measurements, the seemingly extra optimum factor is definitely worse, or irrelevant.
On this case, at first, it seemed as if we must always trace the JDBC driver about our intentions of fetching just one row. I don’t know why the JDBC driver behaved worse than if I didn’t trace it. Maybe it allotted a buffer that was too small, and needed to enhance it, moderately than allocating a buffer that was too massive, however massive sufficient. I do know now, because of Douglas Surber’s touch upon the reddit dialogue. The issue is that ojdbc doesn’t know whether or not there might be extra rows, so the JDBC rs.subsequent()
name has to do one other spherical journey. For extra particulars, see the linked remark above.
I’ve completed comparable benchmarks previously, attempting to “optimise” preliminary sizes of ArrayList
or StringBuilder
. I used to be hardly in a position to persistently outperform the defaults. Generally, the “enchancment” did appear to enhance issues. Generally, it worsened issues.
With no clear wins (that are understood, don’t blindly belief benchmark outcomes both, even in the event you’re profitable!), I misplaced confidence in these enhancements, and didn’t implement them ultimately. This case right here is identical. I’ve not been in a position to obtain enhancements, however in 2/5 instances, issues obtained considerably worse.
Observe up
On /r/java, there had been a dialogue about this text. It instructed 2 extra checks:
1. Attempt utilizing a fetchSize of two
You’d be tempted to assume that different fetch sizes might nonetheless be acceptable, e.g. 2
, to forestall that potential buffer dimension increment. I simply tried that with Oracle solely, producing:
JDBCFetchSizeBenchmark.fetchSize1 thrpt 0.513 JDBCFetchSizeBenchmark.fetchSize2 thrpt 0.968 JDBCFetchSizeBenchmark.noFetchSize thrpt 1.000
Whereas the penalty of setting the fetchSize
to 1
has disappeared, there’s once more no enchancment over the default worth. For a proof, see once more Douglas Surber’s touch upon reddit
2. Attempt utilizing PreparedStatements
For my part, PreparedStatement
utilization shouldn’t matter for this particular benchmark, which is why I had initially left them out. Somebody on the reddit dialogue was keen to place all their cash on the one PreparedStatement
card, so right here’s an up to date end result, once more with Oracle solely, evaluating static statements with ready ones (up to date benchmark code under):
Benchmark Mode Rating JDBCFetchSizeBenchmark.fetchSizePrepared1 thrpt 0.503 JDBCFetchSizeBenchmark.fetchSizeStatic1 thrpt 0.518 JDBCFetchSizeBenchmark.fetchSizePrepared2 thrpt 0.939 JDBCFetchSizeBenchmark.fetchSizeStatic2 thrpt 0.994 JDBCFetchSizeBenchmark.noFetchSizePrepared thrpt 1.000 JDBCFetchSizeBenchmark.noFetchSizeStatic thrpt 0.998
The end result is identical for each. Not simply that, it may be seen that in my specific setup (Querying Oracle XE 21c in docker, regionally), there’s completely no distinction between utilizing a static assertion and a ready assertion on this case.
It could once more be fascinating to research why that’s, hypotheses might embrace e.g.
- ojdbc caches additionally static statements within the ready assertion cache
- the impact of caching a ready assertion is negligible in a benchmark that runs solely a single assertion, which is nowhere close to consultant of a manufacturing workload
- the consumer aspect impact of getting ready statements is irrelevant in comparison with the advantages of the cursor cache on the server aspect, or in comparison with the detrimental impact of setting the
fetchSize
to1
The up to date benchmark code:
bundle org.jooq.check.benchmarks.native;
import java.sql.*;
import org.openjdk.jmh.annotations.*;
import org.openjdk.jmh.infra.Blackhole;
@Fork(worth = 1)
@Warmup(iterations = 3, time = 3)
@Measurement(iterations = 7, time = 3)
public class JDBCFetchSizeBenchmark {
@State(Scope.Benchmark)
public static class BenchmarkState {
Connection connection;
@Setup(Degree.Trial)
public void setup() throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
connection = DriverManager.getConnection(
"jdbc:oracle:skinny:@localhost:1521/XEPDB1",
"TEST",
"TEST"
);
}
@TearDown(Degree.Trial)
public void teardown() throws Exception {
connection.shut();
}
}
@FunctionalInterface
interface ThrowingConsumer<T> {
void settle for(T t) throws SQLException;
}
personal void runPrepared(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Assertion> c
) throws SQLException {
strive (PreparedStatement s = state.connection.prepareStatement(
"choose title from t_book the place id = 1")
) {
c.settle for(s);
strive (ResultSet rs = s.executeQuery()) {
whereas (rs.subsequent())
blackhole.devour(rs.getString(1));
}
}
}
personal void runStatic(
Blackhole blackhole,
BenchmarkState state,
ThrowingConsumer<Assertion> c
) throws SQLException {
strive (Assertion s = state.connection.createStatement()) {
c.settle for(s);
strive (ResultSet rs = s.executeQuery(
"choose title from t_book the place id = 1")
) {
whereas (rs.subsequent())
blackhole.devour(rs.getString(1));
}
}
}
@Benchmark
public void fetchSizeStatic1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void fetchSizeStatic2(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> s.setFetchSize(2));
}
@Benchmark
public void noFetchSizeStatic(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runStatic(blackhole, state, s -> {});
}
@Benchmark
public void fetchSizePrepared1(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> s.setFetchSize(1));
}
@Benchmark
public void fetchSizePrepared2(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> s.setFetchSize(2));
}
@Benchmark
public void noFetchSizePrepared(Blackhole blackhole, BenchmarkState state)
throws SQLException {
runPrepared(blackhole, state, s -> {});
}
}
[ad_2]