A

Anonymous

Answered

SLOW response of CF Page

Forum|Forum|17 years ago
December 10, 2008
7 replies
1331 views

I have a rather robust query that draws from an oracle server. The page does not load - and always crashes on a timing error after 60 seconds. If I immediately run it again if loads properly within a few seconds.

If I cut and paste the query to a SQL editor, the query runs in less than 4 secs.

Obviously this must be some kind of CF Administrator issue - perhaps something with the way I have it set up. I have the enterprise addition, that runs off an oracle DB on a server. I am a novice when it comes to configuring the system -any advice you would have, if you would gear it to a newbie, would be appreciated.

This topic has been closed for replies.

Correct answer Charlie Arehart

OK, we'll let that path go (the discussion of CFQUERYPARAM). But you say, "The page does not load - and always crashes on a timing error after 60 seconds. If I immediately run it again if loads properly within a few seconds. If I cut and paste the query to a SQL editor, the query runs in less than 4 secs. Obviously this must be some kind of CF Administrator issue..."

Well, I still wouldn't jump to that conclusion. I mean, you say that the first execution was slow, but then a refresh of the page makes it run fast. That just sounds like it took time for Oracle to compile the query and then find and load that data (into its cache) and pass it back to CF. The fact that it ran fast the 2nd time would seem to confirm this, as does your running the query in Oracle directly. Each of those could be re-running the execution plan created the first time, and may be pulling records from Oracle's buffers.

One way to confirm this is to change the SQL (run in Oracle directly) to get some other columns. That will cause Oracle to compile the SQL statement. Maybe also change the Where clause to cause it to get different records (so it loads different data into Oracle's buffers). Does that query then take "longer"? If so, if you run the same code in CF (after that), does it run "faster"? If so, this would confirm these suspicions.

I realize this may not be it, but it seems worth ruling out. Things aren't usually totally mysterious.

There are also ways you can ask the DBMS to report the execution plan and statistics about creating and running the query, from the DB side, though those aren't always easily available from CFML. Indeed, the tool I'd recommend for such a case in SQL Server is SQL Server Profiler, which allows you to watch what queries come into SQL Server from CF (among other things) and shows considerable detail. I would imagine there are tools like that for Oracle, too. It may pay to know more about that it things about these queries if/when they run slowly.

Hope that's helpful.

N

Newsgroup_User

Inspiring

Glad to help. It can be so tough sorting through all the possibilities when the
stuff hits the fan. Glad the thoughts may have helped. Once you do determine
the answer, be sure to mark it as such for future readers of the thread.

Charlie Arehart

Community Expert

Glad to help. It can be so tough sorting through all the possibilities when the stuff hits the fan. Glad the thoughts may have helped. Once you do determine the answer, be sure to mark it as such for future readers of the thread.

/Charlie (troubleshooter, carehart. org)

A

Anonymous

Carehart,

It does appear to be a caching situation. If I change the SQL it takes just as long to run. Also, I must have always run the SQL in the editor 'AFTER' I had just run it in my coldfusion page. I have started looking into why my SQL is so slow and it appears to be due to a union statement. I haven't made the changes yet, but it will be easy to separate the union into two separate queries and just join the results in the display.

Thank you for all your help!

Charlie Arehart

Correct answer

Community Expert

OK, we'll let that path go (the discussion of CFQUERYPARAM). But you say, "The page does not load - and always crashes on a timing error after 60 seconds. If I immediately run it again if loads properly within a few seconds. If I cut and paste the query to a SQL editor, the query runs in less than 4 secs. Obviously this must be some kind of CF Administrator issue..."

Well, I still wouldn't jump to that conclusion. I mean, you say that the first execution was slow, but then a refresh of the page makes it run fast. That just sounds like it took time for Oracle to compile the query and then find and load that data (into its cache) and pass it back to CF. The fact that it ran fast the 2nd time would seem to confirm this, as does your running the query in Oracle directly. Each of those could be re-running the execution plan created the first time, and may be pulling records from Oracle's buffers.

One way to confirm this is to change the SQL (run in Oracle directly) to get some other columns. That will cause Oracle to compile the SQL statement. Maybe also change the Where clause to cause it to get different records (so it loads different data into Oracle's buffers). Does that query then take "longer"? If so, if you run the same code in CF (after that), does it run "faster"? If so, this would confirm these suspicions.

I realize this may not be it, but it seems worth ruling out. Things aren't usually totally mysterious.

There are also ways you can ask the DBMS to report the execution plan and statistics about creating and running the query, from the DB side, though those aren't always easily available from CFML. Indeed, the tool I'd recommend for such a case in SQL Server is SQL Server Profiler, which allows you to watch what queries come into SQL Server from CF (among other things) and shows considerable detail. I would imagine there are tools like that for Oracle, too. It may pay to know more about that it things about these queries if/when they run slowly.

Hope that's helpful.

/Charlie (troubleshooter, carehart. org)

Charlie Arehart

Community Expert

Dan, does the query that "takes long sometimes" have CFQUERYPARAM? If so, I'd point out that when you copy the SQL to the DB, you would have to change the SQL to hard-code whatever variable values would have been passed to the CFQUERYPARAM. In doing so, you have really changed the query. I know it doesn't seem to, but you have. Before I elaborate, though, let's hear if you confirm you are indeed using a cfqueryparam.

/Charlie (troubleshooter, carehart. org)

A

Anonymous

Nope. I am not using any CFQUERYPARAMs.

I do put my functions in a separate file, the output calls these functions to display the data properly. I also have all of my SQL in a separate page. I access these files with a CFINCLUDE.

D

Daverms

Inspiring

Hi,

Which Coldfusion version you are using?...

If your version is 8 then try the "Performance Monitor" utility in your coldfusion administrator..

A

Anonymous

It is version 8. I will play around with the Performance Monitor and see what i can determine.

Ian:

My question though, would be, why does the same query run in my editor in 4 sec, when it takes over a minute in my coldfusion page?

N

Newsgroup_User

Inspiring

VegasDan wrote:
> any advice you would have, if you would gear it to a newbie, would be
> appreciated.

The first bit of advice is that the default 60 second timeout is
completely configurable.

You can configure it in the administrator for the entire server.

OR

You can configure an individual page to allow a longer timeout with the
<cfsetting requesttimeout="{seconds}"> tag.

Using one of these options you should be able to give the page enough
time to run. This should allow you to determine better what is going on
and see if it can be optimized, or if this is just one of those cases
where a process takes a long time and it just needs to be given the time
to do its job.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded