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

[Desmond Miles]

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.

[Desmond Miles]

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