CODEWITHSUNDEEP
c#sqlreporting-servicesexport-to-excelssis-2012
prabhashankar pandey
prabhashankar pandey

Reputation: 23

EXPORT 1million rows with 500 column Data into excel from SQL database from application buil using .NET 3.5 framework

Excel report should support 1million rows and 800 columns. The application has to generate excel in two modes 1.online, where on selecting an entity on grid view excel report has to be generated . 2.offline,user can select multiple entities and using a button on UI can trigger it to generate excel and can receive excel via mail.

Currently application supports the above functionality ,but the export is very slow .Application and Database is on the same server. Keeping that in mind we have to design a framework where it will be fast even incase of Data on the application is increased by 10x with minimal performance issues.

Does SSIS will serve the purpose or if any other please suggest?

Upvotes: 2

Views: 1061

Answers (2)

Vasil Popov
Vasil Popov

Reputation: 1258

1 million of records definitely will take some time and in a concurrent environment this is not good. It is best to have services oriented architecture, or just execute your code asynchronously to prevent this hanging. The processing of the export (for example in a service) should run in background threads to handle the load.

When completed generate and send mail as you mentioned. Or save in an application temp dir and use SignalR for example (or just pooling the server with Ajax) to check if the process in the service has completed. If yes, redirect the client to download the file.

Upvotes: 1

PhillipH
PhillipH

Reputation: 6222

You dont say what the UI is, but since you mention "online" I assume its a browser. Consequently the fastest way would be to query the SQL Server, extract the data set, and use the documented OpenDocument XML to generate an XLSX file as part of the Response Stream being returned by the MVC Controller/Web API. This generates least handling of data, and keeps the text handling on the Web Server.

Upvotes: 0

Related Questions