Performance issue with Oracle stored procedure. Pull Request or not?

dev google-group-archive
1

Hi all and happy new year btw

TL;DR
Our company is about to use Lucee instead of Adobe CFMX 9 for some of out
apps. We’re currently testing our code with Lucee in order to list possible
imcompatibilites.
I’ve noticed a performance issue when calling Oracle stored procedure using
Oracle JDBC driver (ojdbc7.jar but same issue with ojdbc14.jar).
We are not using the provided Oracle datasource type in Lucee because it
cannot be specified an Oracle service name instead of an SID.

I’ve investigated in Lucee source code and the performance issue is caused
by the runtime null value of the schemaPattern parameter passed to
*java.sql.DatabaseMetaData.getProcedureColumns()*I’ve noticed that this
method call is a direct consequence of the Railo issue below which has been
fixed in Railo (which codebase has been inherited in Lucee since the fork).
Issue “tag storedproc fails with oracle” :
https://issues.jboss.org/browse/RAILO-3287

I’ve ran the testcase of this issue (above) in Lucee (latest 4.x version)
but the 1st test case fails while the 2nd succeed.
The 1st test case is expected to call an Oracle function using cfstoredproc
but I doubt Oracle function are expected to be called that way.
Indeed Oracle function can be either called with a simple SQL select
statement or in pure PL/SQL.

Anyway, even if metadata seems to load fast with current parameters value
passed to getProcedureColumns(), this will be very slow in large Oracle
database (say 100 of packages with 100 of stored procedure per package).
The reason is that these parameters values are use to query database
metadata (e.g ALL_TABLES). Passing null as the schemaPattern to
getProcedureColumns() will lead to performance issue…

One solution would consist of commenting the modification of the commit of
RAILO-3287 (see above). This will disable the call to getProcedureColumns().
Another solution in which I got the best performance is when I pass the
following parameters to getProcedureColumns() :

  • catalog : Pass the connection schema returned by
    java.sql.Connection.getSchema()
  • schemaPattern : Pass the stored procedure package or an empty string but
    not null.
  • procedureNamePattern : Pass the name of the stored procedure or an empty
    string by not null.
  • columnNamePattern : This is always has null value in Railo and Lucee.
    This is the solution I’ve implemented and build in an *.lco file (see
    below).

Short version
I’ve implemented and successfully tested my solution (see above) in the
4.5.2.018 branch tag which I’ve cloned locally.
Anyway, I need to know what to do ?

  • Should I send a pull request ? What are chances to get it refused ?
  • What else ?

Because we are short on our due date we are going to use my patched version
but we will need some manual trick to avoid our patch being overrided when
patching Lucee.

2

Sorry I made huge mistake while typing my previous post (see below)On Monday, January 4, 2016 at 12:52:18 PM UTC+1, Desmond Miles wrote:

Another solution in which I got the best performance is when I pass the
following parameters to getProcedureColumns() :

  • catalog : Pass the connection schema returned by
    java.sql.Connection.getSchema()
  • schemaPattern : Pass the stored procedure package or an empty string but
    not null.
  • procedureNamePattern : Pass the name of the stored procedure or an empty
    string by not null.
  • columnNamePattern : This is always has null value in Railo and Lucee.
    This is the solution I’ve implemented and build in an *.lco file (see
    below).

This is what I really did (corrected) :

  • catalog : This must be the stored procedure’s package name or an empty
    string. This is the case in Lucee 4.5.2.018 and higher

*- schemaPattern : This is expected to be Connection.getSchema() which is
not available in JDK6. So it SHOULD BE an empty string instead of
null.Passing null will make getProcedureColumns() very slow on a large
Oracle database (i.e many packages and procedure per packages) e.g I’ve
noticed 5 times slower…Notice that passing **Connection.getSchema() is a
bit faster (up to *
10% faster) but it requires JDK7 or higher and ojdbc7.jar or higher. Lucee
sources cannot be build with JDK7…- procedureNamePattern : This must
be the procedure name (w/o the package name). This is the case in Lucee
4.5.2.018 and higher

  • columnNamePattern : This is always has null value in Railo and Lucee.

The solution I’ve implemented consists of passing an empty string instead
of null for schemaPattern. Since I did not tried to get the connection
schema in JDK6…
I’ve tested cfstoredproc using value like “PACKAGE.procedureName” as the
“procedure” attribute value and got the following duration results :

  • Lucee 4.5.2.018 (ojdbc14) : 7.4 secs
  • Modified Lucee 4.5.2.018 (ojdbc14) - empty string as schemaPattern : 1.7
    secs