(OT) Handling long-running queries with mod

Ovid has asked for the wisdom of the Perl Monks concerning the following question:

We have a large, complicated Web site that allows users to view a bunch of reports and select filters for said reports. However, sometimes the filter combinations can create a very long running query in Oracle, but we can't always tell until the query is run. We'd like to present the user with a page explaining the problem and offering to let them wait or receive the report by email. We have several ideas on how to implement this but they're all pretty ugly. I'm curious to know how others have solved (or might solve) this problem.

We are running Perl 5.6.1 on RH Advanced Server 2.3 with Mason 1.03 (yeah, I know it's old.)

Cheers,
Ovid

New address of my CGI Course.

Comment on (OT) Handling long-running queries with mod_perl/Mason

Replies are listed 'Best First'.
Re: (OT) Handling long-running queries with mod_perl/Mason by perrin (Chancellor) on Sep 10, 2004 at 03:12 UTC
Your neighbor has a column on that. Adding a "we'll just mail it to you" option after N refreshes to it shouldn't be too hard.	[reply]
Re: (OT) Handling long-running queries with mod_perl/Mason by simonm (Vicar) on Sep 09, 2004 at 22:50 UTC
On your "submit" request, return a page that says "please wait a moment" with a 3 second refresh to a related URL; then start your query/report process with `$r->register_cleanup`. Have that report place the results in a standard location when it's done. When you receive the request for the related URL from the client, check to see if the report has finished; if you can't show it to them yet, return a page that says "still working on it, click here to receive the results in email instead" which goes to a third URL to register the email request in some kind of a queue.	[reply]
Re: (OT) Handling long-running queries with mod_perl/Mason by shenme (Priest) on Sep 10, 2004 at 04:13 UTC
In one application, after a certain amount of time I switch to "Hey, this should've finished by now - it must be have broken." But of course if you can actually monitor the processing and _know_ that it is continuing and _progressing_ all the better. Do allow the user to see _when_ the request was posted, what time it is _now_, and do the math of what the interval is and display that also.	[reply]
Re: (OT) Handling long-running queries with mod_perl/Mason by Tuppence (Pilgrim) on Sep 10, 2004 at 21:36 UTC
Here's my thoughts. Save all information needed to generate the report, and get a md5 of this information to use as a filename. Store a copy of the dataset in this file, possible with DBD::SQLite. Now, you probably do not want to be making your report queries with your main apache process. It would probably be a good idea to have a seperate server process that gets you the actual data, to avoid having an expensive and huge apache process spin it's wheels while the query runs to return some piddly amount of computationally intensive data. This would also be handy for paging through datasets, because once the dataset is created you can page within it without re-running the query, as long as no filter options change. Update: OK, so yeah I cheated - I know the problem ;) I still think this method mixed in with some interstitial goodness will take care of it though.	[reply]
Re^2: (OT) Handling long-running queries with mod_perl/Mason by Ovid (Cardinal) on Sep 10, 2004 at 22:42 UTC
You know, I thought it was surprising that you would nail one of our proposed solutions so exactly, then I realized who you were :) Cheers, Ovid New address of my CGI Course.	[reply]