tracker issue : CF-3785178

select a category, or use search below
(searches all categories and all time range)

CFSTOREDPROC becomes very slow when returning many null values in 1 or more columns

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Bart Vanhulle / Bart Vanhulle (Bart Vanhulle)

Created: 07/04/2014

Components: Database

Versions: 11.0

Failure Type: Performance Issue

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / All users will encounter

Locale/System: English / Windows 8.1/64bit

Vote Count: 0

Listed in the version Issues Fixed doc
Problem Description:
When a cfstoredproc executes an SQL statement in an Oracle PLSQL statement that returns many empty values for a varchar2 column it becomes extremely slow. A repeated test showed that the same SQL statement took 170000ms+ to execute while the cfquery version only took 300ms.

Steps to Reproduce:
1. Create a table test in Oracle with 1 column of type varchar2(50)
2. Insert 10000 empty records into it: insert into test (column) values ('')
3. Create a PLSQL package with a procedure that looks like this:

procedure qry_test(o_status                              out varchar2,
                      o_data                                out sys_refcursor)
      o_status := 'OK';
      open o_data for select * from test;
      when others then
         o_status := sqlerrm;
   end qry_test;

4. Create a CFML page that calls the procedure using cfstoredproc:

<cfstoredproc procedure="test.qry_test" datasource="dsn">
<cfprocresult resultset="1" name="testData" />
<cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="sqlstatus">

5. Run the same query using cfquery:

<cfquery name="testData" datasource="dsn">
select * from test

Actual Result:
CFSTOREDPROC response time: 170000ms +
CFQUERY: 300ms

Expected Result:
CFSTOREDPROC should have similar response times to CFQUERY

Any Workarounds:

Update all empty fields in the table so they're no longer null (any value will do, for example 'x')

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3785178

External Customer Info:
External Company:  
External Customer Name: Biebel37
External Customer Email:  
External Test Config: - ColdFusion 11 Developer mode

- Oracle 11g SE (over OpenVPN connection from the local client)


- Windows 8.1 64-bit, Core i73770, 16GB of memory



How long does the proc take to run in SQL Developer (etc)? This doesn't really demonstrate it's a CF problem, it could just as likely be the proc itself. Best remove that as a possibility before claiming it's a CF bug. -- Adam
Comment by External U.
11703 | July 05, 2014 08:06:44 AM GMT
I've had similar issues on SQL Server, and it seems to be caused by the arithabort setting on the JDBC connection string. It *could* be the same for Oracle? -- Adam
Comment by External U.
11704 | July 05, 2014 08:09:06 AM GMT
Thanks for you reply Adam. When I run it directly in the PLSQL editor it takes 200-300ms (returning all rows). This code runs fine in CF9, the problem only occurs when I test the application under CF11. I initially created a forum topic for this issue : After more testing I was able to link it to the null values. I'll forward the arithabort comment to our DBAdmin, it's interesting to see that this problem didn't occur with the CF9 drivers though.
Comment by External U.
11705 | July 06, 2014 08:17:29 AM GMT
G'day mate. Oddly it affects us on CF9. Could perhaps just be down to driver idiosyncrasies. Or be unrelated :-/ Have you tried different Oracle drivers? I do not mean you should migrate completely to different drivers, but just for the sake of testing this one case, try a different driver and see if there's a difference. It might cast more light on what's going on. Adobe, any time you want to participate here, please feel welcome to... -- Adam
Comment by External U.
11706 | July 06, 2014 08:30:20 AM GMT
It might indeed be a driver issue. When I check our CF9 setup on our dev server it uses is 4.1 (which I believe is the driver that came with the original installation). CF11 comes with 5.1. I ran the test again on both setups, on CF9 the cfstoredproc took 130ms while on CF11 it took 190731ms. It's strange that you experience the problem on CF9 as well since for us it seems to be fine. Our dev environment runs on top of Linux while my CF11 runs on top of Windows, perhaps that might be of influence as well? I'll try with some different drivers (maybe the 4.1) and see if that makes any difference. I'll see if we can get CF11 running on our LInux dev environment so we can repeat the tests there with the 5.1 driver. Bart
Comment by External U.
11707 | July 07, 2014 04:27:40 AM GMT
I swapped the macromedia_drivers.jar on my local CF11 setup with the one from CF9 ( 4.1). I ran the test again and the processing time of the cfstoredproc was only 500ms this time. Stopped the CF11 server, removed the CF9 macromedia_drivers.jar and put back the CF11 macromedia_drivers.jar ( 5.1), restarted and ran the cfstoredproc and it went back to 186633ms processing time. I think that confirms the driver to be the problem. We will do 1 more test on our dev environment with Linux as the OS (and CF11 most likely running on Tomcat) but I suspect it will return the same results. I also tried to use the Oracle Thin JDBC driver but ran into a problem with that one because cfprocresult doesn't work with it and it'd be too much of a problem to refactor our DB calls which use cfprocresult. For now we could use the 4.1 but I'm not sure if that's a good idea. It would be good if the 5.1 driver can be fixed, or at least a logical explanation can be given why null values are problematic with it. Bart
Comment by External U.
11708 | July 07, 2014 09:09:42 AM GMT
We've now done the same tests on a CF11 (tomcat) linux installation. With the 4.1 driver the cfstoredprod takes 300ms while with the 5.1 driver it takes 4000-5000ms, which is significantly faster than on my local Windows installation but still at least 10times slower than with the 4.1 driver. So aside from the driver it seems that the OS plays a role as well. In any case there's a clear performance difference between the 5.1 and 4.1 driver so hopefully this can be looked into. Bart
Comment by External U.
11709 | July 10, 2014 09:42:39 AM GMT
This issue is fixed. This fix will be available with the upcoming update of ColdFusion 11.
Comment by Nimit S.
11710 | September 13, 2014 10:28:22 AM GMT