c# - 处理 400 万条记录时内存不足

Question

我有以下代码试图处理 4,000,000 条数据库记录:

private void intenseProcess4()
{
    using (connection1 = new SqlConnection("connection string goes here"))
    {
        using (command1 = new SqlCommand(@"stored procedure name goes here", connection1))
        {
            command1.CommandType = CommandType.StoredProcedure;

            try
            {
                connection1.Open();

                using (reader1 = command1.ExecuteReader())
                {
                    while (reader1.Read())
                    {
                        int PrjNameIndex1 = reader1.GetOrdinal("full_path");
                        Directory.CreateDirectory(Path.Combine(reader1.GetString(PrjNameIndex1)));
                    }

                    if (reader1.NextResult())
                    {
                        while (reader1.Read())
                        {
                            System.IO.File.Copy(reader1.GetString(SourceIndex), reader1.GetString(DestinationIndex), true);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                File.AppendAllText(Path.Combine(@"h:\X\log\error.log"), ex + " SqlException caught." + Environment.NewLine);
            }
        }
    }
}

启动后，它可以正常工作大约一个小时，但随后会出现以下错误消息:

Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01:   devenv.exe
    Problem Signature 02:   12.0.21005.1
    Problem Signature 03:   524fcb34
    Problem Signature 04:   mscorlib
    Problem Signature 05:   4.0.30319.34209
    Problem Signature 06:   534894cc
Problem Signature 07:   226e
Problem Signature 08:   6
Problem Signature 09:   System.OutOfMemoryException
OS Version: 6.1.7601.2.1.0.256.49
Locale ID:  2057
Additional Information 1:   0a9e
Additional Information 2:   0a9e372d3b4ad19135b953a78882e789
Additional Information 3:   0a9e
Additional Information 4:   0a9e372d3b4ad19135b953a78882e789

当时，它只处理了大约 35000 条记录

Answer 1

试试CommandBehavior.SequentialAccess :

When you specify SequentialAccess, you are required to read from the columns in the order they are returned, although you are not required to read each column. Once you have read past a location in the returned stream of data, data at or before that location can no longer be read from the DataReader.

但是尝试处理 一次通过 4M 记录的智慧值得怀疑。我很确定您永远不会成功复制 4M 文件并且保持结果集打开。你注定要一遍又一遍地重试。考虑改为使用批处理，检索一小组文件进行处理、复制、记下进度，然后获取另一批处理。万一发生崩溃，从上次的进度恢复。

您还应该考虑并行执行多个副本(使用异步 IO，而不是线程!)。