Improve default `setNull` performance on PostgreSQL and MS SQL Server (e.g. for `NamedParameterJdbcTemplate` batch updates)

[ricardoekm]

We've been investigating an issue where we had a 100x performance drop when switching from JdbcTemplate to NamedParameterJdbcTemplate in batch update operations.

We found out that the root cause is the handling of null values by StatementCreatorUtils:setNull, which in turn calls getParameterMetadata from the connection. It seems this issue a database call for every row to be inserted (call stack from profiler attached) which basically kills the performance gains of the batch insert.

We're using Postgres as back-end database. Spring JDBC 5.2.5.

Thanks.

[quaff]

Have you tried -Dspring.jdbc.getParameterType.ignore=true?

[quaff]

It seems spring.jdbc.getParameterType.ignore will not avoid ps.getConnection().getMetaData(), should spring need introducing a new spring.jdbc.getMetaData.ignore?

[quaff]

@jhoeller Could you explain why not simply call ps.setObject(paramIndex, null) ?

[jhoeller]

ps.getConnection().getMetaData() usually isn't expensive since it's cached in the connection pool. Just getParameterMetaData() may turn out to be expensive, and that's what spring.jdbc.getParameterType.ignore=true covers.

As for why not simply call setObject, it's about compatibility with certain drivers/databases. Quoting the setObject javadoc:

 *<b>Note:</b> Not all databases allow for a non-typed Null to be sent to
 * the backend. For maximum portability, the <code>setNull</code> or the
 * <code>setObject(int parameterIndex, Object x, int sqlType)</code>
 * method should be used
 * instead of <code>setObject(int parameterIndex, Object x)</code>.

[ricardoekm]

spring.jdbc.getParameterType.ignore=true did the trick, thanks!

[quaff]

@jhoeller I assume all modern databases allow non-typed Null, if it's true, spring.jdbc.getParameterType.ignore should be true by default.

[ricardoekm]

Reasonable defaults, that's what life is about :)

[jhoeller]

We're happy to refine our default assumptions there. However, a few years ago, there were only a few databases supporting untyped setObject(paramIndex, null) calls, e.g. Microsoft's then-new SQL Server driver. For all others, a setNull call with some SQL type argument is needed.

I'd particularly like to learn about recent driver behavior with MySQL and PostgreSQL there. We're effectively using setNull(paramIndex, Types.NULL) for the latter, like for other databases that we don't have explicit rules for. If this is known to work reliably, we can explictly opt into that for such well-known databases, not ever trying getParameterType for those then (independent from the spring.jdbc.getParameterType.ignore setting).

[jhoeller]

A StackOverflow answer about support for setObject vs setNull in common JDBC drivers (https://stackoverflow.com/a/10068111, dating back to 2016) and other articles suggest that MySQL does indeed reliably support untyped nulls, whereas PostgreSQL requires the specific SQL type to be set in some cases. I'd appreciate feedback on the latter since according to the indications above, setNull(paramIndex, Types.NULL) does seem to work for your cases here (since that's what we're effectively using when you set spring.jdbc.getParameterType.ignore=true), possibly due to a more recent JDBC driver?

A more recent discussion thread about PostgreSQL null handling (https://www.postgresql-archive.org/quot-could-not-determine-data-type-of-parameter-quot-with-timestamp-td5995489.html) suggests that setNull(paramIndex, Types.NULL) does usually work but that ParameterMetaData.getParameterType should be used for better execution performance. That's pretty much the situation that we have right now with the choice that spring.jdbc.getParameterType.ignore is providing, following the PostgreSQL recommendation by default but allowing to opt out of it.

Another interesting thread on StackOverflow (https://stackoverflow.com/questions/37942063/slow-insert-on-postgresql-using-jdbc) suggests that the pgjdbc-ng driver caches parameter data and is therefore much faster with our default settings.

I'll consider revising our default algorithm to take recent driver updates into account, reopening this issue for it. There might not be much that we can do about PostgreSQL specifically but this is nevertheless worth a fresh look now with 2020-era drivers.