Re: Coldfusion Query Delay Problem

[GArlington <garlington@xxxxxxxxxxxxx>]

On Feb 20, 1:35 pm, "Nick B" <webforumsu...@xxxxxxxxxxxxxx> wrote:
> Folks we are having some intermittent problems with our application web 
> servers.
>
>  We are using coldfusion to serve data from Oracle databases. SELECT 
> statements
> only, no updating of the database is done. The coldfusion application server
> and the oracle database server are seperate servers,
>
>  The problem we are having is as follows.
>
>  When the user opens the app for the 1st time a query is run to work out what
> access level they have. 95% of the time the query
>  is instant. We have been logging this using getticktick() often the time 
> taken
> is 0ms we also see 16ms and 31ms. The query returns
>  1 row with 1 field in it to size of data returned is not an issue.
>
>  Occasionally we get this query reportedly taking between 5 and 120 seconds
> using gettickcount() either side of it.
>
>  We turned on some logging at the Oracle end and this reported that EVERY time
> a query of this type it is taking absolutely no time all.
>
>  Our next port of call was the network. We have sniffed packets between the
> coldfusion server and the oracle server.
>
>  We managed to trap one of the requests where the response time is slow and 
> the
> scenario was as follows :
>
>  1. Coldfusion -> Oracle. Hello i've got some work for you to do. Completed in
> millisenconds
>  2. Oracle -> Coldfusion. Acknowledged, Yes what is it?  Completed in
> millseconds.
>  3. Coldfusion -> Oracle  Acknowledged......... delay of 17 seconds (delay
> times vary)..... it's this query
>  4. Oracle -> Coldfusion. Here are your results. completed in milliseconds
>  5. Coldufsion -> Oracle. Acknlowledged. completed in millseconds.
>
>  So it appears that internally within coldfusion a delay is occuring as the
> database and the network have been eliminated.
>
>  The delay time is never the same and can range from a couple of seconds to
> over a minute. The times of day that these delays occur at is not uniform.
>
>  The server is busiest between 0800 and 1700. But this server is NOT under
> heavy load at all, it is however query intensive. It is up 24 hours a day and
> there is a constant user base.
>
>  We have tuned all our queries and cached them as best we can. However i'm not
> sure how much this helps as we get delays on queries we know are performant.
>
>  We are currently upgrading one of our servers to CF8 using oracle thin jdbc
> drivers rather than the odbc to see if this makes a difference.
>
>  If anyone has previous experience of this or knows of anything settings we
> need to tweak at a java level that would be great.
>
>  Spec of what we are running is as below :
>
>  Windows 2003 Server
>  Coldfusion MX 7.0.2 Standard
>  Oracle 10g ODBC Drivers
>
>  Any help gratefully received as the users are starting to get fed up of 
> having
> these delays for no apparent reason.

It may be the query caching - try to play with number of queries
cached, I hope you are NOT caching too many BTW...