Spring Framework
  1. Spring Framework
  2. SPR-11100

performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Complete
    • Affects Version/s: 3.2.5
    • Fix Version/s: 3.2.6, 4.0 GA
    • Component/s: Data:JDBC
    • Labels:
      None
    • Last commented by a User:
      false

      Description

      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 (SPR-10185)

        Issue Links

          Activity

          Hide
          Juergen Hoeller added a comment -

          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

          Show
          Juergen Hoeller added a comment - 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

            People

            • Assignee:
              Juergen Hoeller
              Reporter:
              M Claus
              Last updater:
              Juergen Hoeller
            • Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                19 weeks, 1 day ago