What is the fastest way to write to a text file from a database table?

Question

I have data analysis application and I need to be able to export database tables to a delimited text file using c#. Because of the application architecture, that data must be brought to the c# application. No database exporting functionality can be used. The tables size can range from a few columns and a few hundred rows to ~100 columns to over a million rows.

Further clarification based on comments --

I have a Windows Service acting as the data access layer that will be getting the request for the export from the presentation layer. Once the export is complete, the service will then need to pass the export back to the presentation layer, which would either be a WPF app or a Silverlight app, as a stream object. The user will then be given an option to save or open the export.

What is the fastest way to do this?

Thanks

Answer 1

hmm, first of all, if its not a must to use c#, the sql managment console is capable of such a task.

To achieve best perfrormance i would you a consumer-producer 2 thread concept,

• One thread will be the reader, responsible for reading items from the DB - in which case i highly recommand using the IReader to read the values, and put them in a cuncurrent queue.
• The other will be the writer who will simply use a fileStream to write the data from the queue.

you can also achieve much greater performance by reading the information via a paged manner, thats is, if you know you'll have 100000 records, devide it to chunks of 1000, have a reader reading those chunks from the DB and putting them in a queue.

Although the later solution is more complicated he'll allow you to utilize your CPU in the best way possibble and avoid latency.