[thelist] CF: Query runs slower in ColdFusion than in Oracle

[Scott Brady]

Original Message:
> From: "Jeff Howden" <jeff at jeffhowden.com>

> scott, i commend you for barely having your head around the database.

Well, the most complex part of the query (the part that includes the sub query) was written by our Oracle DBA.  I'd ask him for help on this, but a) He's already swamped, b) he doesn't know anything about ColdFusion, and c) since the SQL itself seems to run quickly, I'm not sure what he could do. 

I guess I could ask him if there are any more indices he could create . . . .

> a quick way to test this is to set maxrows on the <cfquery> to 1.

That came back in 2.7 seconds.  That still seems kind of slow for 1 record, but I'm not psitive on that.  Perhaps there's some processing CF is doing with the query set before it closes the query?  That might explain the difference between Toad and CF.

Is there a way to get only one row in Oracle?  (I tried doing a "TOP 1" but I guess Oracle doesn't support that)
 
> a couple more questions, is the database server and coldfusion located on
> the same machine?  if not, how are they connected and are you accessing them
> via toad in the same manner that cf does?

The database server is on a different machine. CF is on my local machine.  They are connected via the MX (Updater 3) Oracle driver (JDBC?).  Both CF and Toad are utilizing the TNS Names file on my local machine.  And, I'm using the same login info in cfquery as I use to log in with Toad. [The CF application this query will be used in, uses a unique username and password for each user in cfquery]

> do you get any performance gains by tweaking the blockfactor setting of
> <cfquery>?

I seem to be getting about the same execution times with blockfactors of 10, 100, 1000, and 150, so . . . nope.
 
> which database driver are you using for coldfusion?

It's the Oracle driver that comes with MX Updater 3 (our application actually helped find a bug in the driver pre-Updater 3 [that was 3 weeks of development hell]), which I believe is a JDBC driver.

Thansk!

Scott

----------------------------
Scott Brady
http://www.scottbrady.net/