Highlighted

Very slow Oracle Stored Procedure in ColdFusion 11

New Here ,
Jul 03, 2014

Copy link to clipboard

Copied

Recently I installed ColdFusion 11 Developer version (on Windows 8 & IIS) to test the migration of a CF9 based website that uses Oracle as its underlying DB. 1 of the first problems I encountered are the PLSQL stored procedures that seem to be very slow, like 10-50 times slower than with CF9. So I did a test where I execute the exact same SQL statement, which returns ~11000 rows, with both cfstoredproc and cfquery. The processing time of the stored procedure is 111645ms while the processing time of the cfquery is 362ms. The query doesn't return any (C)LOBs, per row there are 4 columns returned, 3 having a number, 1 having a varchar2 with a max size of 256 characters. I uninstalled CF11 several times but everytime I get the same results, no matter what I try in the CFIDE. Maybe I'm missing something obvious here, but for now I don't see it...

TOPICS
Database access

Views

443

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Very slow Oracle Stored Procedure in ColdFusion 11

New Here ,
Jul 03, 2014

Copy link to clipboard

Copied

Recently I installed ColdFusion 11 Developer version (on Windows 8 & IIS) to test the migration of a CF9 based website that uses Oracle as its underlying DB. 1 of the first problems I encountered are the PLSQL stored procedures that seem to be very slow, like 10-50 times slower than with CF9. So I did a test where I execute the exact same SQL statement, which returns ~11000 rows, with both cfstoredproc and cfquery. The processing time of the stored procedure is 111645ms while the processing time of the cfquery is 362ms. The query doesn't return any (C)LOBs, per row there are 4 columns returned, 3 having a number, 1 having a varchar2 with a max size of 256 characters. I uninstalled CF11 several times but everytime I get the same results, no matter what I try in the CFIDE. Maybe I'm missing something obvious here, but for now I don't see it...

TOPICS
Database access

Views

444

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 03, 2014 0
New Here ,
Jul 04, 2014

Copy link to clipboard

Copied

It seems this has something to do with empty/null values in the varchar2 column. The more empty/null values returned, the (exponentially) slower it becomes with cfstoredproc.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 04, 2014 0