views:

287

answers:

4

We have started a new CF8 app and it is running dog slow. A test where we go around ColdFusion (queries within a database utility) show normal speed (80ms). CF8 returns the same query in something like 60 to 80 seconds!

I have been looking online and seeing lots of posts about CF8 and performance problems, but don't get any overall sense of a solution; just lots of people trying things and saying that they didn't have the problem with CF7. We are also seeing instability on the server, and some errors relating to garbage collection and the memory heap.

We have a number of other applications running on CF8 and they perform adequately...our programmer is not an expert or a guru, he just plugs away. We have isolated this down to a single query that takes forever to return, so it is not a complicated test.

Are there any known CF8 problems or obvious tweaks that we should consider trying? If we have to start over and learn a new environment, I will never make deadline.

JoeBob

+2  A: 

Ok, here's the what I'd do:

  1. I'd put in query LIMIT 1 or TOP 1 (which db you have) to see what happens then.

  2. Try to run query which took you 60s as only thing on page. Don't output anything, just run query.

  3. If you see some problems with memory and GC, switch off debugging, uncheck all debugging options in administrator.

  4. Make sure that monitor tool which comes with CF8 has all options turned off.

If this doesn't help, we could go further.

EDIT: First thing which could be a problem is number of returned records. Second is that you do a lot of data processing, all things happening during page execution are logged if debugging is turned on. I saw server fill almost 10GB of RAM with debugging on, and same process took 40MB without it. Third option would be that really something is wrong with server or setup.

zarko.susnjar
we have tried Top 100, query still took same time or longer to return. It is now the only thing on the page, we are not outputting, just running the query as you suggesting. The query does take longer the more records it returns. If it only returns a few records it takes a somewhat reasonable few seconds to return, still too slow. But if it returns thousands of records, then it takes a minute or so. But again, we are not outputting anything in the test, we are just doing the select. I will check on debugging options
JoeBob
A: 

Based on your last explanation, the problem seems to be with your query and not CF. Which database are you running? Do you have indexes on tables?

Kaasu
In addition to what database, what protocol and driver are you using to connect to it? ODBC or JDBC? I've recently had an issue with SQL Server 2005, using an SSL JDBC driver taking forever to run a query that uses a CFQUERYPARAM of cfsqltype="CF_SQL_VARCHAR". Using a stored procedure instead of a query solved the lag issue.
iKnowKungFoo
A: 

Do you need all of the columns you are returning? Are you using SELECT *?

Return only the columns you need.

Even if you are not outputting the data...a lot of data is still being passed from the database to the web server.

A: 

Thanks to everyone for their help...I think, as suggested, that the problem wasn't CF problem at all...we are still running tests to time the identical query in PHP with comparable conditions, but all my experience is with non-web applications, and I just didn't realize that "select" really meant "select and transfer all the data to the web server", even without any output to the screen. The transfer to 20,000 plus records (including many unwanted fields) was taking a huge amount of time. I had been told earlier that the database server was not active during this delay, and it turned out that was wrong information. I had to bypass the programmer and get the admin and look at performance monitors to see that was clearly wrong; transferring the data was taking time and bandwidth.

JoeBob

JoeBob