Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell command) to export it to a text file, one line for each row of data? The .net process should know when the export have been finished or if there was any error. The datatype will be all int or nvarchar.

I am assuming that a pure C# code using ado.net to execute a select * command and looping over the datareader and writing to a file for each record will be slow and there is no way I can parallelize this.

Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine. The SQL Server will be a HA cluster. Is SSIS better suited for this, no data transformation required?

The .Net process would run on Machine A, SQL Server on Machine B and ultimate file destination is a network share. One option is SQL server writes the file directly to the network share. The other option is SQL Server writes to machine A and then when the file is written the .net process copies it to network share. I don't have formal SLA but expecting 30 mins - 1 hr for the file write.

share|improve this question
2  
"Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine." -- where will the .NET app run from? Worst-case, this could mean the data has to move through 2 network hops, which will probably be the biggest bottleneck. Also, the number of rows is somewhat irrelevant -- what is the approximate total data size? Do you have a performance SLA you need to meet for this process? –  Jon Seigel Nov 23 '12 at 17:53
add comment

4 Answers 4

Looping through all those articles is an option if you want to get old watching it execute.

Some different options that you should try are:

And you can try all these options while looping in another session, just for fun :-).

share|improve this answer
add comment

I would simply use the Import Export Wizard. At the end you're given the option to save the task, which you can then schedule with SQL Server Agent. Add yourself as an operator and configure DB Mail on the server, and it can email you when the job completes or fails.

Seriously, why reinvent the wheel?

http://msdn.microsoft.com/en-us/library/ms140052.aspx

share|improve this answer
    
Not bad at all, actually this saves an SSIS package at the end. If it's a one time only task it can be a solution. –  Marian Nov 24 '12 at 11:24
add comment

Others have stated that bcp should be the fastest way but I don't see any advantage over a CLR solution. On inserts to database tables, the various bulk copy implementations will always win. This is primarily due to the way that they minimize logging and enable multi-threaded writes. You don't have these constraints when writing to a flat file.

At my work, we use a CLR to dump query output to a file. We also incorporate the Ionic.Zip.dll so that the file can be automatically zipped after creation.

Here's an example which the op claims to be twice as fast as bcp on blobs: http://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

share|improve this answer
    
That particular question is not very detailed. I'd suggest you read the following question - Optimising BCP performance for BLOB data here. It will surely give an idea about the many playing options you have with BCP only :-). –  Marian Nov 24 '12 at 11:28
    
I see the links and fully agree. But most of the optimization points are for loading data into sql server. I've yet to see any facts related to bcp being faster than a streamwriter or filestream CLR solution when writing sql data to a flat file. –  brian Nov 24 '12 at 16:10
add comment

I think bcp.exe from commandline should be the fastest way.

http://msdn.microsoft.com/en-us/library/ms162802.aspx

share|improve this answer
5  
While you may be right, could you shed some light on details about this solution? How it is used (a link to the docs may be enough), why do you think it is the faster etc. –  dezso Nov 23 '12 at 9:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.