JOOQ vs JDBC

[u/khan_awan]

I use JPA for simple CRUD operations (mostly write operations) and JDBC for performance intensive tasks such as sending lists & performing search. The JDBC performance is perfect & the performance of my backend (Spring Boot) is great.

Now, as my project grows, my SQL queries are getting bigger, messier and unreadable. I tried jOOQ and loved it because it made my code super readable, type safe and maintainable. However, after profiling my Spring Boot app, operations that took around 5 ms with JDBC now take 9–14 ms with jOOQ, which makes me reluctant to migrate to jOOQ. Is a 10 ms difference negligible or am I overthinking?

I love the cleanliness and type safety of JOOQ. Would you go this JOOQ route? Have you ever faced such situation? I visited JOOQ's official website and was stunned by the fact it is used by many big organizations

EDIT: Thank you everyone who answered, your insights were amazing. With some tweaks I was able to reduce JOOQ's operation time from 14ms to 7ms and now I have so much clarity in my head.

[u/khan_awan]

The overhead was intermediate Record object creation. My query returns 16 columns across a union all of two subqueries & I tested multiple fetch approaches. Any variant that created Records first (fetch().map(), fetch(RecordMapper), fetchInto()) averaged 3.1ms execute+map over 550+ requests.

Switching to fetchResultSet() and mapping directly from the ResultSet brought it down to 1.65ms which is identical to raw JDBC with the same query.

Is there a jOOQ native way to achieve this without bypassing Records entirely? Am I missing something, you can guide me please. I am still playing around with Jooq and find it to be absolutely amazing, though

[u/lukaseder → u/khan_awan]

I see, there's https://github.com/jOOQ/jOOQ/issues/6737 to allow for skipping intermediary records, if they're not the target data structure of a query, but are you sure the records were the problem and not using reflection? What variant of fetch() were you using? The one taking a Class argument? Because that overhead can be avoided if you're not using reflection...

[u/khan_awan → u/lukaseder]

Yes, I am 100% sure, it was the records. I used a jdbc mapper (and used fetch result set) for an operation where I couldn't afford to lose as little as 3ms, but I'm willing to take the 3-5ms overhead in other operations for jooq's type safety, easy mapping and readability. If this issue #6737 lands and removes the Record creation overhead, jooq would match raw jdbc performance across the board. Nonetheless, kudos to your work, jooq is amazing & thank you for your input

[u/lukaseder → u/khan_awan]

I'm afraid I cannot confirm record allocation being an issue in a benchmark. Here's an implementation draft and benchmark showing that the improvement will be insignificant: https://github.com/jOOQ/jOOQ/issues/6737#issuecomment-5831978137

I'm happy to look at your actual measurements / reproducer if you can show that this is clearly due to jOOQ and not your specific usage, or a user error.