We have upgraded to 3.6 as you suggested. We have determined that this SQL query is at the root of the delay
select p.approvalCd, ;
Sum(p.usedAmount) as usedAmount ;
from apprTran p ;
join arTran a on p.inv_track = a.track ;
AND a.cli_code = ToSel ;
AND Not a.paid ;
AND a.TranType = 'I';
group by 1 into cursor vt ;
When the problem is happening, this query takes close to 200 seconds to run. When the problem is not present, it takes well under 1 second.
If the problem continues, my next step is to break this query into 2 queries to see it is one table or the other that is causing the problem.
Sent by FoxWeb Support on 09/16/2011 01:21:14 PM:
Have you checked your server's resources utilization (CPU and I/O) during the periods that the script is running? I would be curious to find out.
Also, in your original message you mentioned FoxWeb 3.0. I recommend that you upgrade to version 3.6, which is a free and 100% compatible upgrade for you. It does not contain major new features, but it does contain some bug fixes. You can get it at http://www.foxweb.com/download/fw3.exe.
Other than that, I think you are on the right track with your debugging plan.
FoxWeb Support Team
Sent by Jeff Grippe on 09/15/2011 05:25:19 PM:
Once FW gets into the state where this problem is occurring, I can replicate it consistantly.
I have called the script from a browser which being RDP'd into the server and I've sat here watching all five channels go from Waiting to Busy. Then when the script finally returns data to the browser, the handful of requests that have piled up get quickly dealt with. The problem will continue to occur with this script until I close FW and restart it.
As soon as I close and restart FW, the script goes back to executing quickly returning data to the browser in seconds instead of minutes.
This script is honestly one of the simpler ones in my site. It does two SQL queries against free VFP tables and then returns the data to the browser in a table.
I've got other scripts that contact credit card processing servers, read data from uploaded excel spreadsheet, etc. and they don't have this problem.
Perhaps something simple that I can start with is to record the time into a memory variable upon entry into the script. Record it again after the first SQL query; again after the second, and then return that information to the browser so that I can see if it is either of the queries that is taking an unusually long time.
Thanks for all the help.
Sent by FoxWeb Support on 09/15/2011 01:33:53 PM:
From your past few messages there are a couple of things that absolutely make no sense, based on my knowledge of how FoxWeb works:
- You said that when you stop FoxWeb all VFP processes exit, but when it's running you are seeing twice the number of processes as you have channels. Can you please confirm this? Can you replicate it by a) verifying that you have the extra processes initially, b) stopping FoxWeb and verifying that all VFP processes are gone, and c) restarting FoxWeb and verifying that the extra processes are back? How long does it take for the extra processes to appear?
- You mentioned that a single request freezes all channels. This is simply impossible. There is no way that a single request can change the status of more than one channel from Ready to Busy. Some logging may be in order. You can start by examining your web server's log to see if other requests have arrived around the same time as the request for the rogue script. If this does not help, you can install additional logging in fw_enter.prg. Just add some code that writes a record in a log table (don't forget to deal with multi-channel file locking issues, otherwise you would be creating a new problem).
Do you not have a way to replicate the problem? If you can, you could try debugging the script, using one of the techniques listed in the Debugging FoxWeb Scripts page of the documentation (http://foxweb.com/document/Debug.htm).
Regarding your upgrade question, you could try installing a trial of version 4.5 and run it with the VFP run-time DLL (or VFP 9 if you have it), but I seriously doubt it will help with this particular problem.
FoxWeb Support Team
Sent by Jeff Grippe on 09/15/2011 10:21:09 AM:
My timeouts are very long because we have some large uploads.
Our script timeout is 999 and our session timeout is 120 min.
We do sometimes get timeouts. They are recorded in FWStart. There are no references to the script that is causing me a problem in FWStart.
When I restart FW, I look at the task manager to make sure that all of the VFP processes have completed.
What do you think of the idea of moving From FW 3.0 to the current version and from VFP 7 to VFP 9?
Sent by FoxWeb Support on 09/14/2011 04:49:11 PM:
I realize that after restarting channels, you still see 10 processes, but what about the period between when you stop FoxWeb and then start it again? Do the 5 processes remain? Have you tried killing them manually before restarting? It's possible that they are orphans from a previous session.
Also, you did not mention whether you tried looking at channel windows while the channels are locked.
By the way, what's your channel timeout? FoxWeb should be restarting channels when they take too long to respond and this would be evident in the log.
FoxWeb Support Team
Sent by Jeff Grippe on 09/14/2011 03:46:09 PM:
Here are the answers to your questions:
1. I was doing the test myself and I did not press the refresh button. I was only because I went to the server to try to see what was happening that I noticed that it did eventually finish. The single script did in fact block all the channels (according to the channel status window).
2. Immediately when I restart FW, I see 10 VFP processes in the task manager. I only see 5 on the task bar but I see 10 in task manager.
3. FWStart.log did not reveal anything. I'll email it but I don't think you'll find anything about this script there.
You can see channel after channel go busy when the script execute once FW has ceased to respond correctly to this script. After I restart FW, the script doesn't effect more than one channel and it returns result to the browser in about 1-2 seconds. When the problem is happening, it take many minutes for this script to return results to the browser.