Collecting SQL Data with feedback to asp.net web app

Question

I have the following situation: I have a stored procedure that takes data from a bunch of tables and creates a record into a single table. After that the end user can get some graphics based on the data from the row in the resulting table. The problem is that the collecting of data from multiple tables into the resulting one can have a very long period of running. I would like from my Web application to give the possibility to the user to start this collection of data, and when it's done to inform him about this, so that he can access the graphics diagram based on resulting collected data. My question is how to implement this as best practice.

Answer 1

Start a background thread on the server to do the processing. You can store the processing results in session state, the database, or some other location.

On the client side, use an UpdatePanel and a Timer to regularly poll the server for results. When the results are there, disable the timer.

Answer 2

I would do something like Andomar suggests, but in a windows service instead of a thread in the asp.net process.

First I would setup a table in my database that would be used as a work queue - maybe your results table would suffice? This table would contain a job unique id, a job description (used by your service to determine what to do) and a job status code (new, working, finished).

Then I would setup a windows service application that would poll this table regularily. Maybe as often av every 5-30 seconds depending on your load. The service would be simple: If it found any new jobs it would mark the first job as "working" and then run the data collection process. When the process was done the service would mark the job as finished.

Finally, in my asp.net application, I would setup a job-order page where the user could request new data collection jobs. The request would insert a new record to the work queue table with status as new and the job description as needed. Then the page would redirect the user to a new page (or use ajax) that would check the status of the job and reload every XX seconds to check if the job is done. When the job status code is set to finished we display the page where the user can display the report or download the file or whatever.

What I suggest here is a simple implementation of a polling-based job queue. You could of course make a fancier version using MSMQ or something like that, but this should suffice for many if not most situations.