Generating CSV reports that have 1 million+ rows in ASP.NET

I am seeking your advice with regards to generating CSV reports.

I have an ASP.NET application that is produces reports on billing data. Recently, I have noticed that when it tries to export a report file the web application is unresponsive and will throw in an OutOfMemory exception or Serve开发者_C百科r timeout exception.

What I did to amend this was to:

• Place the necessary indexes in SQL server to get the records faster
• Use SqlDataReader to fetch the data from SQL server
• Specify the criteria needed to pull out necessary records instead of getting all information in one call.

I have also thought of limiting the maximum number of records returned from the database but the system needs to produce all information regardless of how big the rowcount is returned because the information being downloaded will be critical for our client's financial records.

I am wondering, what methods/solutions that can be used to render CSV reports that have huge data in ASP.NET?

Looking forward for your response.

Cheers, Ann

To solve the server timeout exception I would try to produce the reports asynchronously. So when your application receives a request to generate a report it should launch another thread that does the generation and return the request immediately.

You'll then need another page that lets visitors pick up reports after they've been generated.

This all involves a bit more work within the application of course, to record what reports are being generated, and mark them as generated when complete.

As for the out of memory exception - that is specific to the way you are generating the reports. It's very rare to get out of memory exceptions unless you really are doing something quite crazy - so are you somehow consuming lots of memory in your CSV generation - creating lots and lots of string objects for example? Make sure you're using StringBuilder, or better still perhaps stream the output directly into a file.

Final tip - if you are serving huge files like this make sure you have GZIP or DEFLATE compression enabled for these file types on your IIS configuration. That will significantly reduce the size of the package to be delivered to the client - but at the cost of some processing time.

I am wondering, what methods/solutions that can be used to render CSV reports that have huge data in ASP.NET?

The best way is to not do it in ASP.NET. You need a separate process, at the very least a thread inside your ASP app but it would be better to offload to another server/service.

Pre render them and download them as URI\files. I have found that rendering big files like this needs to be out of the regular Click&Wait for the page to render and come back. Or if they must be onclick, A Ajax like start and a occasional query to determine percentage done is much better and when 100% then you release the URI.