Re: JDBC prepared statement: a 32767 limit of arguments number

On 3/9/22 14:38, Vladislav Malyshkin wrote:
> On 09/03/2022 10.03, Tom Lane wrote:
>>
>> I concur with David's opinion that if you think you need more
>> parameters, you're doing it wrong. One idea to consider is
>> aggregating similar values into an array parameter.
> I disagree:
>
> 1. It is extremely convenient to insert multiple values in a single
> SQL insert:
>
> *INSERT INTO table_name (f1,f2,f3) VALUES (1,"text",2.4),
> (2,"text2",2.5),...*
>
> Setting all values as JDBC parameters is the easiest way to integrate
> java/scala and SQL.
> A single insert of 10000 records is several orders of magnitudes
> *faster* than 10000 separate inserts, not to mention transaction
> simplification.

Those aren't your only alternatives, of course. Even for a fairly wide
table of say 100 columns you could insert a batch of 327 sets of values
in a single statement. Experiments I did some years ago on multi-valued
inserts suggested that the benefit didn't scale linearly (no, I don't
have a reference, I'm relying on memory). Your example above could of
course accommodate the 10,000 sets of values you refer to.

>
> 2. For automatic scala<->jdbc integration tools such as
> https://github.com/mal19992/sqlps a number of  JDBC arguments can be
> generated by an automatic transformation, the SQL and JDBC arguments
> are autogenerated and can be a very large number.
> Suggested by David approach "like using a temp table and a join
> instead of an IN operator." is extremely inconvenient for automatic tools.

Above you were concerned about performance, but here you want to avoid a
more performant usage pattern for the sake of convenience. Some years
ago I managed to speed up a client's app by about an order of magnitude
by replacing an IN clause with 3000 values with a temp table join, so
regardless of the limit on the number of placeholders this is something
you should consider. If your automated tools find that inconvenient then
that's a problem they should deal with.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com