5

I want JOOQ to generate ... in (?) and bind list as an array parameter (Postgres). My code looks like 

.where(
   Tables.TABLE.FIELD.in(idsList)
)
  1. How can I do this?
  2. Why it's not done by default since it's more efficient that generation (and parsing by PG) of the string in (?, ?, ?, ?, ...)
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Alex Dvoretsky
  • 938
  • 9
  • 21
  • It looks like you are probably only doing a few ids in the `in` clause but if you are doing hundreds consider making a [batch ids table](http://stackoverflow.com/a/11119642/318174). Consider it because you never know when a user will actually select a hundreds of different items (e.g. think gmail checkboxes). – Adam Gent Mar 30 '17 at 17:57

1 Answers1

7

How can I do this?

You can use the DSL.any(T[]) operator, e.g.

TABLE.FIELD.eq(any(1, 2, 3))

This will bind only one array variable to the JDBC statement

Why it's not done by default since it's more efficient that generation (and parsing by PG) of the string

It might be worth thinking about a flag in Settings. I've registered a feature request for this: https://github.com/jOOQ/jOOQ/issues/6029

In general, jOOQ allows users to write exactly the SQL they want to send to the database server, so the automatic "optimisation" and rewriting of SQL might appear quite unexpected to some users. This is just a general rule of thumb in jOOQ. It's always worth thinking about optimisations and making them opt-in through Settings.

Caution about premature optimisation!

However, it's always important to actually measure these things. While there is certainly a bit less of a parsing and SQL generation overhead with your suggested approach, beware that an array's cardinality may be much harder to estimate correctly than a hard-wired IN list. Using an array for small lists can have negative effects on your execution plan. So, the few microseconds you're saving on the parser side will weigh against the few milliseconds (?) at the execution side!

I've benchmarked this in the following blog post: https://blog.jooq.org/sql-in-predicate-with-in-list-or-with-array-which-is-faster/

The IN list seems to consistently outperform the array version (in my specific benchmark case) until a length of around 50

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • It'd also help if you post your benchmark code along with results. Does jOOQ provide any support for such benchmarks? Something like jOOQ-JMH? – Dmitry Gusev Mar 29 '17 at 21:33
  • 1
    @DmitryGusev: Benchmark link added. It contains the SQL query being benchmarked, along with a link to the Sakila sample database being used in the benchmark. Interesting idea with the jOOQ-JMH benchmark stub. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/6030. – Lukas Eder Mar 30 '17 at 12:32
  • I'm getting `no suitable method found for in(QuantifiedSelect>)` error on `TABLE.FIELD.eq(DSL.any(1, 2, 3))` – Alex Dvoretsky Mar 30 '17 at 21:02
  • Hmm, you're passing ints but your method is expecting longs... How about passing `1L, 2L, 3L` instead? – Lukas Eder Mar 30 '17 at 21:20
  • 1
    Oh, yes, just now realized that it should be `eq` and not `in`. Thank you – Alex Dvoretsky Mar 31 '17 at 04:56
  • Oh yes, that certainly helps :) – Lukas Eder Mar 31 '17 at 07:35