Reports pulled via our in-house application from Coldfusion takes very long and can hang the clients

[BeesZZA]

Hi Everyone

I am a IT technician/Sysadmin with my company and have been tasked to help sort out this long running problem.

I do not know much about programming (nothing really) but know more or less what coldfusion does and how the process flows from the client to the server and back.

The problem we face is that at certain times of the day some reports pulled via CF takes very long, it can even hang the clients browser, and computer in some cases.

There is an ODBC connection to our iSeries server and one to our Helpdesk's SQL db.

This is what I have done so far to troubleshoot:

1. 1.Ran wireshark on my PC with a filter to only show activity for the server IP, then ran a query to CF.Didnt see much, except for some  "Zero Window" messages going back and forth between my PC and the server as soon as the report popped up in my browser, after googling it I understand that it has to do with the network card buffer or CPU load on client or server.As soon as I save the document it stops doing the zero window thing.

1. 2.Loaded "Coldfusion reactor" to monitor requests and how long they take.Also loaded the JDBC Wrappers on all the ODBC drivers.There seems to be nothing wrong here as CF handles the queries fast and

response times are good as soon as it does its thing.

I have still to monitor the ODBC connections more closely but so far I dont think they are the problem.

I have noticed in CF reactor that the memory usage is quite high during midday and at some point was using 97% of the "assigned 3.2GB".I dont know how this is possible because its running on

a virtual machine with 6GB memory and four processors.

Any help as to what I can try next would be much appreciated.

This is the version:

System Information Server Details Server Product ColdFusion Version 9,0,0,251028 Edition Enterprise  Operating System Windows Server 2008 OS Version 6.0

Adobe Driver Version 4.0 (Build 0005) JVM Details Java Version 1.6.0_14 Java Vendor Sun Microsystems Inc. Java Vendor URL http://java.sun.com/ Java Home D:\JRun4\jre Java File Encoding Cp1252 Java Default File Separator \ Path Separator ; Line Separator Chr(13)  User Home C:\ User Dir D:\JRun4\bin Java VM Specification Version 1.0 Java VM Specification Vendor Sun Microsystems Inc. Java VM Specification Name Java Virtual Machine Specification Java VM Version 14.0-b16 Java VM Vendor Sun Microsystems Inc. Java VM Name Java HotSpot(TM) 64-Bit Server VM Java Specification Version 1.6 Java Specification Vendor Sun Microsystems Inc. Java Specification Name Java Platform API Specification Java Class Version 50.0

[p_sim]

FusionReactor is a great tool diagnose such problems. Start digging which page that takes time to process; and then drill down to the queries. See which piece of code that caused CF Server CPU utilization spikes.

[BeesZZA]

Fusion reactor is cool but It didnt really show me anything new I couldnt see in the CF administrator itself.

Problem still persists, programmers are getting fed-up because their report builders are forever hanging and accountants the same with certain reports taking up to 10 Minutes to complete.

We are now looking at the possibility that there is a problem on one of our Core switches but I personally feel this is unlikely because none of the other Servers show any sign of this.

[p_sim]

First, run the query on the database server. How long does it take?

If the query completed in a reasonable time, then run the client/page directly on the Web server. How long does it take?

Network issue could be the problem. It is unlikely, but it happened to me.

[Carl Von Stetten]

It's a pretty open-ended question, because a number of things can be causing slow performance.  You mention needing to look at ODBC connection performance - I found that using ODBC drivers for iSeries was very slow in the past.  You might want to look at using the JDBC driver in the IBM JTOpen Toolkit - it made a big difference for us when we were querying iSeries in the past.

When you refer to "reports" in this thread's title, do you mean reports from ColdFusion Report Builder?  Or to just ColdFusion .cfm pages?  How complex are these reports?  How much information is displayed, especially in tabular form?  Just rendering huge HTML tables (as in thousands of rows of data) will often cause browsers to become temporarily unresponsive or hang.  If you provide more details, we might better be able to target where the "pain point" really is.

-Carl V.