tracker issue : CF-3785178

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)
   is
   begin
      o_status := 'OK';
      open o_data for select * from test;
   exception
      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">
</cfstoredproc>

5. Run the same query using cfquery:

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

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)

- IIS

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

Attachments:

Comments: