Handling Long Running Reports

Question

I am working on a ASP.net application written in C# with Sql Server 2000 database. We have several PDF reports which clients use for their business needs. The problem is these reports take a while to generate (> 3 minutes). What usually ends up happening is when the user requests the report the request timeout kills the request before the web server has time to finish generating the report, so the user never gets a chance to download the file. Then the user will refresh the page and try again, which starts the entire report generation process over and still ends up timing out. (No we aren't caching reports right now; that is something I am pushing hard for...).

How do you handle these scenarios? I have an idea in my head which involves making an aysnchronous request to start the report generating and then have some javascript to periodically check the status. Once the status indicates the report is finished then make a separate request for the actual file.

Is there a simpler way that I am not seeing?

Answer 1

What about emailing the report to the user. All the asp page should do is send the request to generate the report and return a message that the report will be emailed after is has finished running.

Answer 2

I would consider making this report somehow a little bit more offline from the processing point of view.

Like creating a queue to put report requests into, process the reports from there and after finish, it can send a message to the user.

Maybe I would even create a separate Windows Service for the queue handling.

Update: sending to the user can be email or they can have a 'reports' page, where they can check their reports' status and download them if they are ready.

Answer 3

Your users may not accept this approach, but:

When they request a report (by clicking a button or a link or whatever), you could start the report generation process on a separate thread, and re-direct the user to a page that says "thank you, your report will be emailed to you in a few minutes".

When the thread is done generating the report, you could email the PDF directly (probably won't work because of size), or save the report on the server and email a link to the user.

Alternatively, you could go into IIS and raise the timeout to > 3 minutes.

Answer 4

Using the filesystem here is probably a good bet. Have a request that immediately returns a url to the report pdf location. Your server can then either kick off an external process or send a request to itself to perform the reporting. The client can poll the server (using http HEAD) for the PDF at the supplied url. If you make the filename of the PDF derive from the report parameters, either by using a hash or directly putting the parameters into the name you will get instant server side caching too.

Answer 5

Here is some of the things I would do if I would be presented this problem:

1- Stop those timeout! They are a total waste of resources. (bring up the timeout value of asp pages)

2- Centralize all the db access in one single point, then gather stats about what reports ran when by whom and how much time it took. Investigate why it takes so long, is it because of report complexity? data range? server load? (you could actually all write that on a .csv file on the server and import this file periodically in the sql server to analyze later).

Eventually, it's going to be easier for you to "cache" reports if you go through this single access point (example, same query same date will return same PDF previously generated)

3- I know this really wasn't the question but have you tried diving into those queries to see why they are so long to run? Query tuning maybe?

4- Email/SMS/on screen message when report is ready seems great... if your user generally send a batch of report to be generated maybe a little dashboard indicating progression of "their" queue could be built in the app. A little ajax control would periodically refresh the status.. Hint: If you used that central db access and you have sufficient information about what runs when why and how-long you will eventually be able to roughly estimates the time it will take for a report to run.

If the response time is mission critical, should certain users be limited in the data range (date range for example) during some hours of the day?

Good luck and please post more details about your scenario if you want to get more accurate hints...

Answer 6

Query tuning is probably your best place to start. Though I don't know you are generating the report, that step shouldn't really take all that long. A poorly performing query on the other hand could absolutely kill your performance.

Depending on what you find in looking at the query, you may need to add some indexes, or possibly even set up a table to store the information for your report in a denormalized way, to make it available faster. This denormalized table could then be refreshed (through a SQL Server Job) every hour, or with whatever frequency your requirements dictate (within reason).

If its' a relatively static report, without varying user input parameters, then caching the report run earlier in the day would be a good idea as well, but its' hard to say any more about this without knowing your situation.

For a problem like this you really need to start at the database unless you have reason to suspect your report generating code of being the culprit. There are various band-aids you could use that might help for a while, but if your db is the root cause then those solutions will not scale well, and you'll likely run into similar problems (or worse) some time in the future.