ASP.NET: A Strategy for Processing Really Big Reports

Question

Perhaps this does not apply to reports, but still ...

My asp.net mvc web app has a section for reports that show 5 columns of data that display almost directly into a table in db.

The problem is that in some cases the length of this report can exceed 40,000 records (I know no one can process 40,000 data records, but the report is what it is), and, as you can expect, this time produces a mistake.

The question is, what is a good way to process and deliver a report of this size? I thought about creating a small, small console application that would build a report outside the web server, but I don’t seem to understand which direction to look?

Answer 1

Should the report have the latest data? If not, you can look at creating a PDF report at night (or when your server is not busy) and just provide a link to the PDF. The scheduled task that launches the console application, as you suggested, can create a report and output it to a file. Many reporting tools, such as Crystal Reports, allow you to export a report to PDF or an Excel spreadsheet. In this case, you can generate the report on a completely different machine, and then copy it to the web server. This may allow you to update the report every hour (or something else) without loading your web server.

Answer 2

Generating a report while waiting for a user is probably not a good idea (not to mention SQL / IIS timeouts, etc.)

Can you get a user request for a report, and then use Windows services to receive these requests, generate a report and send an email to the user? (or do you have some kind of ajax polling script on the site to notify users when their reports are ready?)

You can extend this to planning the same report at repeating intervals, etc.

Answer 3

I would take a look at SQL Reporting Services (assuming it works on SQL Server). There may be several delivery options that may better suit the needs of your application (for example, you can plan to have a PDF or Excel document displayed in someone’s mailbox every night, for example).

There's also a great article from the StackOverflow team that allows you to use background processes in ASP.NET if you can just generate this report as often as on demand (maybe every 5-10 minutes?)

http://blog.stackoverflow.com/2008/07/easy-background-tasks-in-aspnet/

Answer 4

Another common use I've seen to handle large-scale reports is to create a Windows service that physically generates a report that deletes the populated binary in a database or file vault somewhere, and then updates the data to show the report. complete with information needed for the application to link the completed report.

You can then force the do report button to cancel the request to initiate the report, and then move them to the processing report page, which lists all the reports queued / processed for them.

Answer 5

It is doubtful that the user would really look at the entire report in 400,000 lines. So why not show only the 1000 most recent rows ordered back? If you are using a reporting solution that supports on-demand reporting, you can always skip to the second report, which displays the next 1000 most recent lines. Just a thought ...