Does jooq has any performance lead over simple sql in java

Question

I want to use jooq for my java web project because from its specifications it seems simple and good query builder but does it has any performance gain over simple query or prepared statements in java.

Answer 1

There are ongoing (unpublished) benchmarks checking for bottlenecks and general performance issues in jOOQ. An example of a recently discovered issue can be seen here:

https://github.com/jOOQ/jOOQ/issues/1625

As Mikko stated as well, jOOQ cannot beat static SQL strings passed to JDBC directly, as you will always have the overhead of:

1. Constructing the jOOQ objects
2. Traversing the jOOQ objects for rendering SQL
3. Creating a PreparedStatement with that SQL
4. Traversing the jOOQ objects for bind variables, binding them to the PreparedStatement

If performance is critical in your environment, I suggest you do not let jOOQ perform these steps for every query execution. Instead, let jOOQ render SQL once and cache the SQL string yourself, as well as a reference to the reusable PreparedStatement. However, if you're not using an in-memory database, such as H2, we're talking about micro-optimisation here, as shown in the benchmark. We're talking about gaining < 1ms per query.

Answer 2

No, it does not offer performance gain. JOOQ itself communicates with database over JDBC (and uses prepared statements) and does not contain cache, so it simply cannot be faster than executing queries over JDBC directly.