Best way to run a long DB query in classical ASP?

Question

Hi guys,

I have a couple of queries for a web site that take a long time to run due to the data model and the amount of data held in the tables. So far I've been running them manually against the database to avoid any timeout issues etc.. however the site owner has asked for these to be made available on the site so he can get the query results.

I had thought of doing this via a .NET web service and having the classical ASP page call this asynchronously. The web page would just initiate the process and before redirecting the user to another screen. The web service would then run the query and email the user the results in a CSV.

However, I can't seem to get this to work. The service runs ok if I invoke it through the screen in IE but calling it through an Ajax call in ASP seems to be an issue - no error is generated but neither is the CSV file created.

I've enclosed the classical ASP code below. The service only has one method with a parameter of the name email which is of the type string. Can anyone see anything wrong with it? Also, this the best way to be doing this or should I be thinking of another approach?

Thanks in advance,

Phil

CODE

<%
message = "http://wwww.mywebsite.com/service/query.asmx/GetResults?email=test"
set req = server.createobject("MSXML2.XMLHTTP")
With req
    .open "GET", message, False
    .setRequestHeader "Content-Type", "text/xml"
    .send
End With

works = req.responseText
response.redirect "http://www.bbc.co.uk"

%>

Answer 1

The idea of asynchronously requesting the work and arranging for its later delivery seems very reasonable to me. I don't speak ASP well enough to know what's wring with your attempt, but is that really an asnch call you have there? Would the seb service also suffer from an HTTP connection timeout?

My approach would have been for an Ajax request to place a request on a queue and return, no need for a redirect, you're still on the page where the user makes teh request, your java script could just acknowledge that the requests was sent. Alterntively, your more traditional "submit a page, stash the request, display another page" appraoch can work, but the the stashing is just to put the request on a queue.

An advantage of the queueing approach is that by controlling the number of daemons we can get controlled parallelism in servicing the requests - avoid overloading the DB. Also the queues can persist and allow a leisurely delivery of the responses.

I assume that MS queues then let you have a daemon processing the reuquest and delivering the responses. Clearly email works, but strikes me as a tad unfriendly. With Ajax style interfaces it would be quite easy to invisibly poll for the status of requests and obtain the results when they are ready, or even to use Comet-style push delivery of the responses.

Answer 2

The problem here, as djna noted, is that you are not calling a callback function. Due to the asynchronously aspect of Ajax, you have set up a callback function that will be executed when the Ajax call ends.

Long story short:
Call the webservice from a javascript function, preferably using JQuery to avoid cross browser incompatibilities

Code:

<div id="results">Processing query. Please wait</div>

<script type="text/javascript">
$(document).ready(function(){
    $("#results").load("http://wwww.mywebsite.com/service/query.asmx/GetResults?email=test&amp;Rnd=" + Math.random().toString()); 
});
</script>

Answer 3

thanks guys. the more i think about it the more i think that djna's suggestion is the more sensible approach to the problem