C# 性能 - 使用 AppendAllText 分块写入文件

Question

有没有更优雅/更快的方式来编写下面的代码？目前大约需要 45 秒。

query.sql 有 200,000 行，每行都有 SQL:

SELECT N'+dave' AS [AccountName], N'20005' AS [EmployeeID], N'-6' AS [PlatformID] UNION ALL

我发现通过将 block 分成 1000 个 block ，事情比等到最后并使用 WriteAllText(运行大约需要 20 分钟)要快得多

static void Main(string[] args)
{
    var s = new Stopwatch();
    s.Start();

    string textToWrite = "";
    string[] lines = File.ReadAllLines(@"e:\temp\query.sql");

    int i = 0;
    foreach (var line in lines)
    {
        var bits = line.Split('\'');

        var value1 = bits[1];
        var value2 = bits[3];
        var value3 = bits[5];

        var message = "INSERT [PreStaging].[Import_AccountEmployeeMapping] ([AccountName], [EmployeeID], [PlatformID]) VALUES (N" +
                    "'" + value1 + "', "
                    + value2 + ", "
                    + value3 + ")";

        textToWrite += message + Environment.NewLine;

        if (i % 1000 == 0)
        {
            Console.WriteLine(i + " " + DateTime.Now.ToLongTimeString());
            File.AppendAllText(@"e:\temp\query2.sql", textToWrite);
            textToWrite = "";
        }
        i++;
    }

    //File.WriteAllText(@"e:\temp\query2.sql", textToWrite);
    File.AppendAllText(@"e:\temp\query2.sql", textToWrite);

    s.Stop();
    TimeSpan ts = s.Elapsed;
    Console.WriteLine("Timespan: {0}m", ts.TotalMinutes);
    Console.WriteLine("Total records: " + i);

    Console.ReadLine();
}

编辑:StringBuilder 解决方案(1000 毫秒):

static void Main2(string[] args)
{
    var s = new Stopwatch();
    s.Start();

    var textToWrite = new StringBuilder();
    string[] lines = File.ReadAllLines(@"e:\temp\query.sql");

    int i = 0;
    foreach (var line in lines)
    {
        var bits = line.Split('\'');

        var value1 = bits[1];
        var value2 = bits[3];
        var value3 = bits[5];

        var message = "INSERT [PreStaging].[Import_AccountEmployeeMapping] ([AccountName], [EmployeeID], [PlatformID]) VALUES (N" +
                    "'" + value1 + "', "
                    + value2 + ", "
                    + value3 + ")"
                    + Environment.NewLine;

        textToWrite.Append(message);

        // Buffering
        if (i % 1000 == 0)
        {
            Console.WriteLine(i + " " + DateTime.Now.ToLongTimeString());
            File.AppendAllText(@"e:\temp\query2.sql", textToWrite.ToString());
            textToWrite = new StringBuilder();
        }
        i++;
    }

    File.AppendAllText(@"e:\temp\query2.sql", textToWrite.ToString());

    s.Stop();
    TimeSpan ts = s.Elapsed;
    Console.WriteLine("Timespan: {0}ms", ts.TotalMilliseconds);
    Console.WriteLine("Total records: " + i);

    Console.ReadLine();
}

编辑:StreamWriter 解决方案(450 毫秒)

static void Main(string[] args)
    {
        var s = new Stopwatch();
        s.Start();

        string[] lines = File.ReadAllLines(@"e:\temp\query.sql");
        int i = 0;
        using (StreamWriter writer = File.AppendText(@"e:\temp\query2.sql"))
        {
            foreach (var line in lines)
            {
                var bits = line.Split('\'');

                var value1 = bits[1];
                var value2 = bits[3];
                var value3 = bits[5];

                writer.WriteLine("INSERT [PreStaging].[Import_AccountEmployeeMapping] ([AccountName], [EmployeeID], [PlatformID]) VALUES (N'{0}', {1}, {2})",
                    value1, value2, value3);

                i++;
            }
        }

        s.Stop();
        TimeSpan ts = s.Elapsed;
        Console.WriteLine("Timespan: {0}ms", ts.TotalMilliseconds);
        Console.WriteLine("Total records: " + i);

        Console.ReadLine();
    }

Answer 1

正如其他人所指出的，使用 StringBuilder。所以在你的情况下，声明:

StringBuilder textToWrite = new StringBuilder();

然后:

textToWrite.AppendLine(message);
if (i % 1000 == 0)
{
    Console.WriteLine(i + " " + DateTime.Now.ToLongTimeString());
    File.AppendAllText(@"e:\temp\query2.sql", textToWrite.ToString());
    textToWrite = new StringBuilder();
}

虽然你最好完全放弃缓冲:

using (StreamWriter writer = File.AppendText(filename))
{
    // initialization stuff here

    foreach (var line in lines)
    {
        var bits = line.Split('\'');

        var value1 = bits[1];
        var value2 = bits[3];
        var value3 = bits[5];

        var message = "INSERT [PreStaging].[Import_AccountEmployeeMapping]                     ([AccountName], [EmployeeID], [PlatformID]) VALUES (N" +
                "'" + value1 + "', "
                + value2 + ", "
                + value3 + ")";

         writer.WriteLine(message); // write the line
    }
}