SLOW response of CF Page

[Guest]

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.

[Newsgroup_User]

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.

[Daverms]

Hi,

Which Coldfusion version you are using?...

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

[Guest]

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?

[Newsgroup_User]

VegasDan wrote:
> 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?
>

I don't know, but if you allow it to run and look at the debugging and
all the other information that is produced, maybe it can be figured out.

It is not impossible that it could be a driver issue. Some process that
work fine when run directly against the database work very differently
when passed through another level of the database driver. If this is
the bottle neck, a possible solution would be to put your code into the
database as a stored procedure and just call the procedure for the
results, instead of passing all the code through the driver.

But other then this type of generic advise there are way to many
variable for us to be able to say why you are experiencing the issue.
We can only guide you to some well worn paths on how to track these
variables down.

[Charlie Arehart]

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.

[Guest]

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.

[Charlie Arehart · Accepted solution]

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.

[Guest]

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]

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.

[Newsgroup_User]

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.