ASP.NET: Strategy for handling really large reports...

Question

Maybe not specific to reports but still...

In my asp.net mvc web application there's a section for reports that show 5 columns of data that map almost directly to a table in the db.

The problem is, in some cases, the length of that report may exceed 40,000 records (I know, nobody can really process 40,000 records of data but the report is what it is) and as you can expect, it times out and throws an error.

The question is, what's a good way to process and deliver a report of that size? I thought about creating a small little console app that would build the report outside of the webserver but I'm kind of at a loss as to what direction to look into?

Answer 1

Does the report need to have up-to-the-minute data? If not, you can look at generating the report as a PDF at night (or whenever your server isn't busy) and just providing a link to the PDF. A scheduled task that runs a console app as you suggested could create the report and output it to a file. A lot of reporting tools like Crystal Reports will allow you to export the report to a PDF or an Excel spreadsheet. For that matter, you could generate the report on a completely different machine and then copy it over to the web server. This could allow you to update the report every hour (or whatever) without putting such a load on your web server.

Answer 2

Another frequent usage I've seen to handle large scale reporting is setting up a windows service that does the physical generation of the report which dumps the completed binary into a database or file store somewhere and then updates data to show the report is complete with the information needed for the application to link the completed report.

Then you could have your do report button fire off a request to initiate a report and then move them to a processing report page where it lists all the reports queued up / processing for them.

Answer 3

Generating the report while the user waits is probably not a good idea (not to mention SQL / IIS timeouts etc)

You could get the user request a report, then have a windows service pick up these requests, generate the report and email the user? (or have some kind of ajax polling script on the site to notify users when their reports are ready?)

You could extend this to scheduling of the same report at recurring intervals etc.

Answer 4

I would look into SQL Reporting Services (assuming this is running on SQL Server). There's several delivery options which may be better suited to your application's needs (you can schedule a PDF or Excel document to show up in someone's mailbox every night, for example).

There's also a great article from the StackOverflow team that allows background processes within ASP.NET if you can simply generate this report every so often instead of on-demand (maybe every 5-10 minutes?)

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

Answer 5

It's doubtful that a user would ever actually look at all of a 40,000 row report. So why not show just the top 1,000 most current rows ordered backwards? If you're using a reporting solution that supports on-demand reports, you could always drill down to second report that shows the next 1,000 most current rows. Just a thought...