Dumping a DB table to a flat file in C# efficiency issue

Question

I am having issues with efficiency when dumping a flat file using C# instead of SSIS. All that I'm doing is dumping a database table (SqlServer) of about 100k rows to a text file. Using SSIS this takes less than one second and using the C# code below takes over a minute which is unacceptable. What am I doing wrong?:

while (Reader.Read())
{
    foreach (string ColumnName in cols)
    {
        sb.Append('"' + Reader[ColumnName].ToString() + "\","); 
    }

    //save lines to file
    WriteLineToFile(TableDefinition.GetTableName(), sb.ToString());

    sb.Clear();
}

notes: sb variable is StringBuilder. This code dumps multiple tables hence the Reader.Read() on the while loop. The query is essentially a select * from tablename for each table in a list

WriteLineToFile method:

public static void WriteLineToFile(string TableName, string Text)
{
    System.IO.File.AppendAllText(OutputDir + @"\" + TableName + ".dat", Text + "\r\n");
}

Answer 1

In a nutshell SSIS most likely performs bulk operations for database read and export (more details here: SQL Server Bulk Operations) and your application writes to file one line at a time which is significantly less efficient.

You have not posted your C# code that reads from database so I can't comment on that but there may be similar inefficiency there as well.

Answer 2

So it looks as if you are going wrong with System.IO.File.AppendAllText() according to the documentation this opens and closes the file. Such IO operations are usually relatively expensive.

You might need to restructure your loop so that you open the file once, write all the lines to it and then close the file.

var lines = new List<string>();
while (Reader.Read())
{

    foreach (string ColumnName in cols)
    {
        sb.Append('"' + Reader[ColumnName].ToString() + "\","); 
    }
    lines.Add(sb.ToString());    

    sb.Clear();
}

var path = OutputDir + @"\" + TableName + ".dat";

File.WriteAllLines(path, lines, Encoding.UTF8);