Slow JDBC time

[coldfusion_user_123456]

I have a pretty simple query that takes about one (1) second to run directly in my Oracle client.

SELECT <fields>
FROM <view> s
WHERE <field value> = 30
ORDER BY <fields>

There are only 404 rows that are being returned.

When this same query is executed through a <cfquery> tag, it takes upwards of 35 seconds for the request to complete. 

It's not the datasource because we run thousands of queries against this same datasource without any issues. Many of them return thousands of records. 

Any direction is appreciated in what and how to look for the bottleneck. Thank you.

[Charlie Arehart]

There are surprisingly a lot of factors that may explain this (none being, "cf sucks at doing queries"--which, while you may not be suggesting it, some could. To be clear, cf itself should add no overhead to the processing of a query in the db.)

First and foremost, when you run the oracle client, is that Oracle client on the server where cf is running? If not, endeavor to put it there, even if a hassle. That way, you can know whether even a call from the oracle client is slow, if made from the cf server.

If it is, perhaps there's a networking or other configuration issue (on either box or between them, or perhaps even in oracle).

If it works well from an oracle client on the CF server, are you logging in with the same user that the cf dsn is using? If not, you'd get a different query plan.

If using the same user, are you using 100% the identical sql, down to line end characters and spacing? That, too, is how most db query plan compilers create a key on the plan--via a hash of the sql string.

Finally (for now), is the query as generated in cf using cfqueryparam? If so, are you hard-coding the col value in the client? If so, again those are NOT the same query, beyond all I've said above. The db engine would create and cache a compiled plan based on the value of such a queryparam the FIRST time the plan was compiled, which may be a different than would be compiled for another value. (Plans are cleared on db restarts, and certain db ops, or can be cleared manually.) This is one reason why even the same query in cf could sometimes be fast then slow.

Of course, other reasons can make "the same query be sometimes fast and sometimes slow", even from the same "client", like cf) such as conflicts with other running queries, or other processes in the db, or other processes on the db server. I'll assume that when you do your test like above, it's at the same moment that it's slow in cf. In that case, I hope one of the other several ideas above may help.

Pleass do let us know, either way. There are still other possibilities we might want to consider, based on what you'd say. 

[Charlie Arehart]

Any thoughts? 

[coldfusion_user_123456]

Hi Charlie,

Thanks for your assistance. I have modified the query to help with overall performance. It's faster now on both the server and through CF. It's still slower in CF however. What it almost appears to be doing is not necessarily returning all records from the view however, it performs like it is. To clarify, the view takes about 11 second to execute and return ALL records on the server. When I throw in a WHERE condition to return 404 records, it takes less than a second. When executed through the cfquery tag it takes just over 11 seconds, but returns only those 404 records as I would expect. I am currently troubleshooting that "issue?"

[BKBK]

Might the Oracle client be optimizing the query perhaps?

In any case, run code similar to the following, and share with us the value of the query's execution time. 

<cfquery name="yourQuery" datasource="yourOracleDSN" result="queryResult">
SELECT <fields>
FROM <view> s
WHERE <field value> = 30
ORDER BY <fields>
</cfquery>

<p><cfoutput>The query's execution time is #queryResult.executionTime# milliseconds</cfoutput></p>