Avoid repeated getParameterType calls for setNull with Oracle 12c driver [SPR-14574]

[spring-projects-issues]

Tom Jahncke opened SPR-14574 and commented

In the StatementCreatorUtils class, setNull method, the call to ps.getParameterMetaData().getParameterType(paramIndex), happens for every null parameter.

If the database is not local to where the Java application is running the overhead of this repeated statement is a significant performance hit due to the latency overhead of each DB operation. For example, we are performing a batch insert from a remote site back to the main enterprise system of thousands of records each with several nulls.

Not sure how feasible it is, but could you cache the sql type for a prepared statement / parameter index so once it is determine it does not have to be derive again?

Thanks!!

---

Affects: 4.2.5

Issue Links:

• performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils [SPR-11100] #15726 performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils
• Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825] #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)
• Protect against getParameterType cursor leak with Oracle 12c JDBC driver [SPR-14629] #19196 Protect against getParameterType cursor leak with Oracle 12c JDBC driver

Backported to: 4.2.8

[spring-projects-issues]

Tom Jahncke commented

This relates to #18398 and #15726. To work around this challenge we set "spring.jdbc.getParameterType.ignore" to true.

[spring-projects-issues]

Juergen Hoeller commented

I'm afraid there is not much we can do here, since we lack the contextual information to properly cache statement objects. This is better done inside a connection pool with statement pooling turned on, which may also cache retrieved ParameterMetaData objects within their corresponding pooled PreparedStatement objects. I'm surprised that common connection pools apparently don't do this by default...

If there is anything we can cleanly do to avoid repeated getParameterMetaData calls on re-execution of a statement, even in a database-specific manner, I'd be happy to learn about it.

[spring-projects-issues]

Tom Jahncke commented

Juergen, thank you for your quick response and research. I also didn't think there was a facility for you to accomplish what I requested, but I wasn't sure. One possibility might be: if the equal statement of a preparedStatement allowed you to keep a cache of the statements that have already had the correct sqlType determine for a given parameter.

I wasn't aware of statement pooling. I'll try that and let you know the outcome.

I have also thought about changing at least my insert statements to be by parameter name instead of parameter index using BeanPropertySqlParameterSource. One gotta ya with doing this is we have enum attributes on the bean. Today with the parameters being by index I loop through the set of parameters prior to calling Spring and convert them to the correct persist primitive data type / value. I did find a similar challenge (without a solid resolution) on stack overflow . I did think about extending BeanPropertySqlParamterSoure to address this, however, I would love a more elegant solution.

[spring-projects-issues]

Juergen Hoeller commented

Revised for 4.3.3 and 4.2.8 now: StatementCreatorUtils does not use getParameterType on any Oracle driver by default now. If you're on 12c (possibly 12.2 or a custom patch) and would like it to be used, specify spring.jdbc.getParameterType.ignore=false. Since those calls seem to be expensive as well, it is arguably better to only use them when actually needed anyway.

[spring-projects-issues]

Juergen Hoeller commented

It would be great if you could give a recent 4.2.8.BUILD-SNAPSHOT or 4.3.3.BUILD-SNAPSHOT a try... I hope the new defaults (and override options) work for you.

[spring-projects-issues]

Tom Jahncke commented

I have tried out version 4.2.8.BUILD-SNAPSHOT and it works as expected, thanks. Details of what I found are:

• With 4.2.6 the setNull method exectue sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
• With 4.2.8.BUILD-SNAPSHOT (with jdbc driver ojdbc712.1.0.2) with not execute the logic ps.getParameterMetaData().getParameterType(paramIndex) by default.
• I did also verify with 4.2.8-BUILD-SNAPSHOT that you can set the spring.jdbc.getParameterType.ignore property to false and it the logic will work as it did in 4.2.5 + 4.2.6
  • System.setProperty(StatementCreatorUtils.IGNORE_GETPARAMETERTYPE_PROPERTY_NAME, Boolean.FALSE.toString());
• Disclaimer, I was not able to reproduce the java.sql.SQLException: ORA-01000 errors with 4.2.6, not sure why ... sorry.