Sybase ASE Stored Procedure sp_jdbc_getprocedurecolumns and performance

Categories: Infrastructure, Programming

My employer has a large Java-based application which uses Sybase ASE as its data store, and 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 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 method com.sybase.jdbc4.jdbc.SybCallableStatement.getParameterMetaData(..). This is invoked when application code calls:

  • standard method someCallableStatement.getParameterMetaData(), or
  • someCallableStatement.setBigDecimal(...) - which in the jconnect implementation triggers a call to getParameterMetaData (via class SybPreparedStatement), or
  • someCallableStatement.setObject(index, anyObj, type) with type being javax.sql.Types.NUMERIC or javax.sql.Types.DECIMAL, or
  • someCallableStatement.setObject(index, someBigDecimal)

The setObject methods effectively follow the same path as setBigDecimal. 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).

We found that we weren’t actually using BigDecimal very often in our persistence layer; money amounts are usually passed as floats (that’s possibly a bad idea, but that’s a topic for another day). The solution we took is therefore to replace 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 MONEY values, 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);
}