Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils [SPR-11100] #15726

Closed
spring-issuemaster opened this issue Nov 19, 2013 · 1 comment

Comments

@spring-issuemaster
Copy link
Collaborator

@spring-issuemaster spring-issuemaster commented Nov 19, 2013

M Claus opened SPR-11100 and commented

We notice a performance drop after we updated to a (much newer) library (3.2.5). We used to work on 3.0.5.

We connect to Oracle11g with ojdbc 11.2.0.4 drivers.

After some testing we noticed that the performance drop was introduced by a changed implementation in setNull. Our queries have lots of columns ~40 with many optional values. For every NULL value, the driver is now asked ps.getParameterMetaData().getParameterType(paramIndex). The drivers don't support this yet but will throw an exception.

We notice that calling this method 30ish times costs us extra time (around 100ms), which is quite noticable. Requests which require 2 inserts went from ~200ms to 400+ms. We omitted the getparametertype call by editing the source of 3.2.5 to check our hypothesis and the performance was restored to its old state.

Is it possible to cache the 'unsupported state' in case the databasedrivers do not support it, so it's only executed once per request.

Below is the oracle implementation of getParameterType

SQLException localSQLException = DatabaseError.createUnsupportedFeatureSqlException();
localSQLException.fillInStackTrace();
throw localSQLException;

PS: entered this issue as a bug because it impairs the function of the product

Somewhat related issue, different bug introduced due to the added functionality (#14818)


Affects: 3.2.5

Issue Links:

  • #14717 NamedParameterJdbcTemplate for "insert / select" clause, set NULL value causes JConnect 7 (Sybase) problem - java.io.IOException: JZ0SL: Unsupported SQL type 0. - Affects 3.1.3.RELEASE, works in 3.1.1.RELEASE
  • #15018 JdbcTemplate cannot set null parameter with Oracle driver
  • #15937 spring-jdbc-3.2.6.RELEASE doesn't support JDK 5.
  • #16013 PreparedStatement#getParameterMetaData() calls may trigger unwanted side effects
  • #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)
  • #19143 Avoid repeated getParameterType calls for setNull with Oracle 12c driver
  • #19196 Protect against getParameterType cursor leak with Oracle 12c JDBC driver

Referenced from: commits 2e15f94, 4c8a789

Backported to: 3.2.6

2 votes, 7 watchers

@spring-issuemaster
Copy link
Collaborator Author

@spring-issuemaster spring-issuemaster commented Dec 9, 2013

Juergen Hoeller commented

We're statically caching information about drivers which do not support that feature now. In case of multiple DataSources with different drivers, we're going to cache that information individually - but once cached, no further getParameterMetaData/getParameterType attempts are going to happen for the lifetime of that application (and the use of the same JDBC driver).

Juergen

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.