Невозможно выбрать более 700000 строк из SQL Server с помощью C#

Что-то пошло не так в вашем дизайне, если вы даже пытаться обработали 700 тысяч строк на C#. То, что вам это не удастся, следовало ожидать.

Если это получение данных для отображения: пользователь не сможет обработать такой объем данных. А фильтрация 700 тыс. Строк в графическом интерфейсе - пустая трата времени и полосы пропускания. Сразу 25-100 полей - это почти предел. Выполните фильтрацию или разбиение на страницы на стороне запроса, чтобы в итоге вы не извлекли на порядки больше, чем вы действительно можете обработать.

Если это некоторая форма массовой вставки или массового изменения: выполняйте такую ​​операцию в SQL Server, а не в своем коде. Получение, обработка на C# и последующая отправка просто добавляет уровни накладных расходов. Если вы добавите двухстороннюю передачу по сети, вы легко утроите это время.

NB: Per Дэвид Браун ответ, кажется, я неправильно понял, как работает пакетная обработка класса SqlBulkCopy. Отредактированный код все еще может быть вам полезен, поэтому я не удалял этот ответ (поскольку код все еще действителен), но ответ заключается не в том, чтобы устанавливать BatchSize, как я думал. См. Объяснение в ответе Дэвида.

Попробуйте что-нибудь вроде этого; ключом является установка свойства Размер партии для ограничения количества строк, с которыми вы работаете одновременно:

using (SqlConnection sourceConnection = new SqlConnection(constr))
{
    sourceConnection.Open();
    SqlCommand commandSourceData = new SqlCommand("select * from XXXX ", sourceConnection);
    using (reader = commandSourceData.ExecuteReader() { //add a using statement for your reader so you don't need to worry about close/dispose

        //keep the connection open or we'll be trying to read from a closed connection

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(constr2))
        {
            bulkCopy.BatchSize = 1000; //Write a few pages at a time rather than all at once; thus lowering memory impact.  See https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.batchsize?view=netframework-4.7.2
            bulkCopy.DestinationTableName = "destinationTable";

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(reader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw; //we've caught the top level Exception rather than somethign specific; so once we've logged it, rethrow it for a proper handler to deal with up the call stack
            }
        }
    }

}

Обратите внимание: поскольку класс SqlBulkCopy принимает IDataReader в качестве аргумента, нам не нужно загружать полный набор данных. Вместо этого считыватель дает нам возможность откатывать записи по мере необходимости (следовательно, мы оставляем соединение открытым после создания считывателя). Когда мы вызываем метод SqlBulkCopyWriteToServer, у него есть внутренняя логика для многократного цикла, выбирая новые записи BatchSize из считывателя, а затем отправляя их в целевую таблицу перед повторением / завершением после того, как считыватель отправил все ожидающие записи. Это работает иначе, чем, скажем, с DataTable, где нам пришлось бы заполнить таблицу данных полным набором записей, вместо того, чтобы иметь возможность читать больше при необходимости.

Один из потенциальных рисков этого подхода заключается в том, что поскольку мы должны поддерживать соединение открытым, любые блокировки на нашем источнике остаются на месте, пока мы не закроем наш читатель. В зависимости от уровня изоляции и того, пытаются ли другие запросы получить доступ к тем же записям, это может вызвать блокировку; в то время как подход таблицы данных взял бы единовременную копию данных в память, а затем закрыл бы соединение, избегая любых блоков. Если эта блокировка вызывает беспокойство, вам следует подумать об изменении уровня изоляции вашего запроса или применении подсказок ... Тем не менее, как именно вы подойдете к этому, будет зависеть от требований.

NB: На самом деле, вместо того, чтобы запускать приведенный выше код как есть, вам нужно немного реорганизовать вещи, чтобы ограничить объем каждого метода. Таким образом, вы можете повторно использовать эту логику для копирования других запросов в другие таблицы. Вы также можете сделать размер пакета настраиваемым, а не жестко запрограммированным, чтобы вы могли настроить значение, которое дает хороший баланс использования ресурсов и производительности (которая будет варьироваться в зависимости от ресурсов хоста). Вы также можете использовать методы async, чтобы позволить другим частям вашей программы развиваться, пока вы ждете потока данных из / в ваши базы данных.

Вот немного измененная версия:

public Task<SqlDataReader> async ExecuteReaderAsync(string connectionString, string query) 
{
    SqlConnection connection;
    SqlCommand command; 
    try 
    {
        connection = new SqlConnection(connectionString); //not in a using as we want to keep the connection open until our reader's finished with it.
        connection.Open();
        command = new SqlCommand(query, connection);
        return await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);  //tell our reader to close the connection when done.
    } 
    catch 
    {
        //if we have an issue before we've returned our reader, dispose of our objects here
        command?.Dispose();
        connection?.Dispose();
        //then rethrow the exception
        throw;
    }
}
public async Task CopySqlDataAsync(string sourceConnectionString, string sourceQuery, string destinationConnectionString, string destinationTableName, int batchSize)
{
    using (var reader = await ExecuteReaderAsync(sourceConnectionString, sourceQuery))
        await CopySqlDataAsync(reader, destinationConnectionString, destinationTableName, batchSize);
}
public async Task CopySqlDataAsync(IDataReader sourceReader, string destinationConnectionString, string destinationTableName, int batchSize)
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString))
    {
        bulkCopy.BatchSize = batchSize; 
        bulkCopy.DestinationTableName = destinationTableName;
        await bulkCopy.WriteToServerAsync(sourceReader);
    }
}
public void CopySqlDataExample()
{
    try 
    {
        var constr = ""; //todo: define connection string; ideally pulling from config 
        var constr2 = ""; //todo: define connection string #2; ideally pulling from config 
        var batchSize = 1000; //todo: replace hardcoded batch size with value from config
        var task = CopySqlDataAsync(constr, "select * from XXXX", constr2, "destinationTable", batchSize); 
        task.Wait(); //waits for the current task to complete / if any exceptions will throw an aggregate exception
    } 
    catch (AggregateException es)
    {
        var e = es.InnerExceptions[0]; //get the wrapped exception 
        Console.WriteLine(e.Message);
        //throw; //to rethrow AggregateException 
        ExceptionDispatchInfo.Capture(e).Throw(); //to rethrow the wrapped exception
    }
}

Этот код не должен вызывать исключение OOM. Когда вы передаете DataReader в SqlBulkCopy.WriteToServer, вы передаете строки из источника в место назначения. Где-то еще вы храните что-то в памяти.

SqlBulkCopy.BatchSize контролирует, как часто SQL Server фиксирует строки, загруженные в место назначения, ограничивая длительность блокировки и рост файла журнала (если он не регистрируется минимально и находится в простом режиме восстановления). Независимо от того, используете вы один пакет или нет, это не должно влиять на объем памяти, используемой ни в SQL Server, ни в клиенте.

Вот пример, который копирует 10 миллионов строк без увеличения объема памяти:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlBulkCopyTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var src = "server=localhost;database=tempdb;integrated security=true";
            var dest = src;

            var sql = "select top (1000*1000*10) m.* from sys.messages m, sys.messages m2";

            var destTable = "dest";

            using (var con = new SqlConnection(dest))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = $"drop table if exists {destTable}; with q as ({sql}) select * into {destTable} from q where 1=2";
                cmd.ExecuteNonQuery();
            }

            Copy(src, dest, sql, destTable);
            Console.WriteLine("Complete.  Hit any key to exit.");
            Console.ReadKey();
        }

        static void Copy(string sourceConnectionString, string destinationConnectionString, string query, string destinationTable)
        {
            using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
            {
                sourceConnection.Open();

                SqlCommand commandSourceData = new SqlCommand(query, sourceConnection);

                var reader = commandSourceData.ExecuteReader();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString))
                {
                    bulkCopy.BulkCopyTimeout = 60 * 10;
                    bulkCopy.DestinationTableName = destinationTable;
                    bulkCopy.NotifyAfter = 10000;
                    bulkCopy.SqlRowsCopied += (s, a) =>
                    {
                        var mem = GC.GetTotalMemory(false);
                        Console.WriteLine($"{a.RowsCopied:N0} rows copied.  Memory {mem:N0}");
                    };
                     // Write from the source to the destination.
                     bulkCopy.WriteToServer(reader);

                }
            }
        }


    }
}

Какие выходы:

. . .
9,830,000 rows copied.  Memory 1,756,828
9,840,000 rows copied.  Memory 798,364
9,850,000 rows copied.  Memory 4,042,396
9,860,000 rows copied.  Memory 3,092,124
9,870,000 rows copied.  Memory 2,133,660
9,880,000 rows copied.  Memory 1,183,388
9,890,000 rows copied.  Memory 3,673,756
9,900,000 rows copied.  Memory 1,601,044
9,910,000 rows copied.  Memory 3,722,772
9,920,000 rows copied.  Memory 1,642,052
9,930,000 rows copied.  Memory 3,763,780
9,940,000 rows copied.  Memory 1,691,204
9,950,000 rows copied.  Memory 3,812,932
9,960,000 rows copied.  Memory 1,740,356
9,970,000 rows copied.  Memory 3,862,084
9,980,000 rows copied.  Memory 1,789,508
9,990,000 rows copied.  Memory 3,903,044
10,000,000 rows copied.  Memory 1,830,468
Complete.  Hit any key to exit.