Sybase ASE Stored Procedure sp_jdbc_getprocedurecolumns and performance

Categories: Infrastructure, Programming

The Core Problem

My employer has a large Java-based application which uses Sybase ASE as its data store, and frequently invokes database-side stored procedures. A mysterious stored procedure sp_jdbc_getprocedurecolumns (which we never call directly) is causing performance problems. It took quite a lot of work to track down why.

This application handles a large amount of traffic, and the database is reaching its capacity limits. Instrumenting the application and database with Prometheus shows that built-in stored procedure sp_jdbc_getprocedurecolumns was using up to 15% of the database capacity - although the application never calls such a procedure.

The problem procedure isn’t being called all that often, but the code is very complex and slow. This stored-procedure is bundled with the database, and consists of server-side logic to query a lot of internal tables and combine/restructure the info. The result is a table of the input/output params for a specified stored-procedure.

It turns out that Sybase’s jconnect JDBC driver (we use version 7) calls this stored procedure from two different code-paths:

  • from method com.sybase.jdbc4.jdbc.SybDatabaseMetaData.getProcedureColumns, or
  • from method com.sybase.jdbc4.jdbc.SybCallableStatement.getParameterMetaData

The SybDatabaseMetaData Path

Class SybDatabaseMetaData implements jdbc-standard interface javax.jdbc.DatabaseMetaData. Its implementation of standard method getProcedureColumns uses the problematic Sybase built-in stored procedure to fetch the necessary data that the jdbc specification requires it to return.

Sadly, the stored procedure implementation on the Sybase server side (sp_jdbc_getprocedurecolumns) is very inefficient. Fortunately, this method is not something that any coder is likely to call very often - at least deliberately.

But unfortunately Spring’s jdbc library can cause it to be executed frequently if a coder is not careful - and we had fallen into this trap. Each instance of Spring jdbc class SimpleJdbcCall wraps calls to a specific target stored procedure. By default each instance loads metadata for its target stored procedure when “compiled”, via calls to two different methods on class DatabaseMetaData (getProcedures and getProcedureColumns). Each of these methods triggers a call to a stored procedure within Sybase. Of course any round-trip to the database should be avoided if possible, but at least the stored-procedure backing method getProcedures is relatively efficient. The same cannot be said of the stored-procedure backing getProcedureColumns (sp_jdbc_getprocedurecolumns).

The SimpleJdbcCall “compilation” step can be done explicitly; if not then it is executed the very first time a stored-procedure call is made via an instance of this type (lazy initialisation).

Given that this compilation (metadata-lookup) only happens once for each SimpleJdbcCall instance, the impact isn’t too bad if the instance is reused for all invocations of that specific stored procedure. However if a programmer falls into the trap of creating a new SimpleJdbcCall instance for each call to a stored procedure, then the code works but performance is extremely poor; what looks like a single call to the database is in fact preceded by two stored procedure calls, the second of which is very CPU-intensive.

The solution is simple:

  • for each stored-procedure, create only one SimpleJdbcCall instance and reuse it, or
  • disable the metadata-lookup for the SimpleJdbcCall instance, instead manually providing the necessary parameter metadata

Of course it is possible to do both, ie manually provide parameter meta-data even on “singleton” instances. We chose to do that, which has the nice advantage that we can enable logging for the problem stored-proc within the database; any call to the stored-procedure indicates that we need to fix our code and the logged parameters provide the name of the stored-procedure whose meta-data is being fetched.

Disabling meta-data lookup does have disadvantages:

  • it means more work for the programmer (explicitly providing parameter metadata), and
  • it means the code needs to be kept in sync with the database.

The “keep in sync” step isn’t too much of a burden as the calling code and the called stored procedure generally need to be kept in sync anyway. However there are a few cases where the fetch-metadata-from-database-at-runtime approach can provide better error messages when inconsistencies exist.

Here is an example of how we now configure SimpleJdbcCall instances:

public class SomeDao {
  private final SimpleJdbcCall someStoredProc;

  public SomeDao(DataSource dataSource) {
    someStoredProc = new SimpleJdbcCall(dataSource)
      .withProcedureName("someTargetStoredProcedureName")
      .withoutProcedureColumnMetaDataAccess()
      .declareParameters(
        new SqlParameter("param1", Types.INTEGER),
        new SqlParameter("param2", Types.BIGINT))
      .returningResultSet(...);
    someStoredProc.compile();
  }
}

The call to withoutProcedureColumnMetaDataAccess disables both of the stored-procedure calls that SimpleJdbcCall usually makes - and means declareParameters is mandatory.

For the cases where the SimpleJdbcCall instance cannot be a final member on a “singleton” class, the same pattern is still followed; because metadata lookup is disabled, SimpleJdbcCall is relatively efficient even in this approach.

The explicit call to compile above is optional, but means that validation of the declared parameters happens on class construction (and as DAOs are typically singletons that means it happens on application startup). This is IMO better than having this validation occur only on first actual use of the instance. If you are using SimpleJdbcCall without disabling metadata-access, then explicitly using compile is even more important as it ensures the (slow) metadata-fetch occurs on startup and not randomly during application execution.

The names used when declaring parameters do not need to match param-names in the stored-procedure declaration; only the types (and order) are important. When using the variants of method SimpleJdbcCall.execute which take a map of parameters, then the param-names in the map need to match the names used in declareParameters (case insensitive).

And as a last note: if the stored-procedure has OUT parameters, then these must be passed to method declareParameters before any input parameters and must use type SqlOutParameter.

The SybCallableStatement Path

This (problematic) code-path is invoked when application code calls any of the following jdbc-standard methods:

  • someCallableStatement.getParameterMetaData() (ie a direct call to the problematic driver method), or
  • someCallableStatement.setBigDecimal(...) or
  • someCallableStatement.setObject(index, anyObj, type) with type being javax.sql.Types.NUMERIC or javax.sql.Types.DECIMAL, or
  • someCallableStatement.setObject(index, someBigDecimal)

The jconnect implementation of method setBigDecimal triggers a call to getParameterMetaData (via class SybPreparedStatement). The setObject methods effectively follow the same path as setBigDecimal when the parameters specify that a decimal type is being processed.

It isn’t 100% clear to me why setBigDecimal needs the parameter meta-data stuff, but it looks like it is trying to scale the BigDecimal to match the precision declared for the matching input parameter of the stored-procedure being invoked.

The results of this stored-procedure are cached on the CallableStatement instance, ie multiple calls to setBigDecimal on the same instance will not re-execute it. However most persistence frameworks (including the one we use) do not reuse the same CallableStatement instance over multiple calls to the same procedure - and indeed this is very difficult to do when using a database connection pool. So in effect, each call to a stored procedure involving a Java BigDecimal parameter triggers a secondary call to sp_jdbc_getprocedurecolumns.

This problem does not occur when calling someCallableStatement.setFloat(..).

To make this even more obscure, however, Spring’s JDBC Template library method execute(params..) will detect when a float object is being passed to a stored-procedure parameter of type DECIMAL or NUMERIC and automatically convert it into .. a BigDecimal. Note also that the Sybase non-standard type MONEY is effectively a NUMERIC type (with 4 decimal places).

For historical reasons, our problem application’s persistence layer is using a mix of direct JDBC calls and Spring JDBC calls. We found that we weren’t actually using BigDecimal very often in direct calls; money amounts are usually passed as floats (that’s possibly a bad idea, but that’s a topic for another day). We therefore replaced BigDecimal values with floats where possible, otherwise replace them with strings and parse the string back into a numeric value within the stored procedure. For Spring calls with target parameters whose declared type was one of (DECIMAL, NUMERIC, MONEY) we resorted to the pass-numeric-params-as-strings approach; fortunately while many procedure had INTEGER params, few had params of the problem types.

For DECIMAL and NUMERIC params, converting to a string in Java and back to a number in the stored-procedure is trivial.

For MONEY values, it is slightly more complex on the caller side. The stored procedure looks like:

select
  @Price = convert(money, @PriceAsStr),
  ...

and the Java code to format a money amount looks like:

/**
 * When formatting values for passing to the DB as "money" strings, use US
 * symbols as that is what the DB expects ("12.34" not German "12,34" or other),
 */
private static final DecimalFormatSymbols MONEY_FORMAT_SYMBOLS = DecimalFormatSymbols.getInstance(Locale.US);

/**
 * Format a double representing a monetary amount into a string for the purpose of passing to the database.
 * <p>
 * Money amounts are represented by Sybase using 4 decimal digits, rounding when the provided value has more digits.
 * Therefore here provide up to 5 decimal digits so rounding works correctly at the receiving end.
 * See: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36271_1251/html/blocks/X34590.htm
 * </p>
 */
static String moneyToString(double money) {
  // Note: DecimalFormat is not thread-safe so create a new instance each time
  DecimalFormat df = new DecimalFormat("0.00###", MONEY_FORMAT_SYMBOLS);
  df.setRoundingMode(RoundingMode.FLOOR);
  return df.format(money);
}

Conclusion

Fixing our code to avoid calling setBigDecimal (directly or implicitly via Spring), and ensuring all Spring SimpleJdbcCall objects are configured with withoutDatabaseMetaData eliminated all calls to sp_jdbc_getprocedurecolumns and reduced the total load on our database by 15%.