Skip to main content
Inspiring
April 15, 2008
Question

Oracle proc call from CF has 4sec overhead

  • April 15, 2008
  • 1 reply
  • 682 views
G'day
I've been troubleshooting a slow-running Oracle (9i) proecdure call, which
was coming in at 4sec. When run via a Oracle SQL Developer window, it runs
in 0ms, which is more like what I'd expect form the amount of work it's
doing.

I applied FusionReactor to the situation, and it's reporting that the proc
is indeed *running* in 0ms, but the added overhead of CF calling it is
4sec. From the "Longest JDBC" screen, the figures were:

Total Time(ms): 3845
DB Time(ms): 0

What on earth is CF doing?

Notes:
- it's returning zero rows, so it's not a data-transmission overhead.
- the DB is running on the same box as CF.
- it's not a one-off. Running the proc with different input data (which
would yield any number of rows returned from 0 to 30000-odd) seems to make
little difference. The DB side of things runs between 0-100ms, and then
there's close to a 4sec overhead added by CF.

The stored proc is running a couple of select queries (a value from the
first contributing to the second), and returning a single resultant record
set. Nothing complicated.

Any ideas?

--
Adam
This topic has been closed for replies.

1 reply

Inspiring
April 15, 2008
Sorry, some software / versions:

Test 1 - my dev laptop:
CF: 8, multi-server, Windows XP Pro
Oracle: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production (same
machine)
JDBC: macromedia.jdbc.oracle.OracleDriver v3.50

Test 2 - QA server:
CF: 7.0.2, multi-server, Windows 2k3 Server
Oracle: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production (same
machine)
JDBC: macromedia.jdbc.oracle.OracleDriver v3.50

Test 3 - production:
CF: 7.0.2, multi-server, Windows 2k3 Server
Oracle: Oracle9i Release 9.2.0.8.0 - 64bit Production (that's on a separate
Solaris box, not sure of version)
JDBC: macromedia.jdbc.oracle.OracleDriver v3.50


All are experiencing similar results.


--
Adam
Participating Frequently
April 15, 2008
Is the problem with a single SP, or set of SPs, or all of your proc calls? If it is just one, you might provide your PL/SQL and ColdFusion cfstoredproc/cfprocparam/cfprocresult tags so that we may have a chance of spotting something.

Phil