Массовая вставка в SQL Server миллионов записей выполняется медленно

Question

У меня есть таблица с 1 миллионом записей. Мне нужно иметь возможность переместить эти записи в другую базу данных и другую таблицу.

Я использую хранимую процедуру для получения данных. Он заполняет адаптер данных, затем записывает данные в новую таблицу.

Мы находимся на SQL Server 2005 и C# 4. Мы будем переходить на SQL Server 2012 или 2014 и Visual Studio 2015 с C# 4.6 или 5.0. Если есть какие-то функции, которые бы работали хорошо.

• для 10к записей процесс занимает менее 1 секунды
• для 500 тыс. записей у адаптера данных не хватает памяти, и процесс завершается неудачно. Пакетная обработка до 100 тыс. записей, оператор select является проблемой в SQL, возвращая 100 тыс. записей за раз, занимает 2 минуты на цикл.

Есть ли способ или что не так с моим кодом ниже, чтобы не допустить заполнения адаптера данных и вместо этого сопоставить столбцы и иметь BulkCopy оставайтесь на стороне сервера и просто отправляйте записи из БД в новую таблицу, как, возможно, SSIS?

Кажется, что массовое копирование происходит молниеносно, но заполнение адаптера происходит неуспешно, потому что ему не хватает памяти, пытаясь заполнить адаптер миллионами записей. Не делая по 1 строке за раз, я просто хотел бы перемещать данные между таблицами.

В одной таблице 27 столбцов, причем 5 столбцов отсутствуют в таблице 2, в которой 32 столбца, а некоторые столбцы не имеют одинаковых имен в обеих таблицах.

Это подтверждение концепции (PoC).

sourceConn_transDB.Open();
SqlCommand sourceCommand = new SqlCommand(queryString, sourceConn_transDB);
DataTable table = new DataTable();

sourceCommand.CommandTimeout = 600;

using (var adapter = new SqlDataAdapter(sourceCommand))
{
    WriteLineWithTime("Adapter Fill");
    adapter.Fill(table);
}

if ((table == null) || (table.Rows.Count <= 0))
   break;

using (SqlBulkCopy bulk = new SqlBulkCopy(targetConn_reportDB, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = "PatientEvent" })
{
    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("PatientID", "PatientID"));
}

Answer 1

Вы пытались использовать WriteToServer перегрузки, которые принимают IDataReaderтогда вам не нужно использовать DataTable совсем.

sourceConn_transDB.Open();
using(SqlCommand sourceCommand = new SqlCommand(queryString, sourceConn_transDB))
{
    sourceCommand.CommandTimeout = 600;

    using (SqlDataReader reader = sourceCommand.ExecuteReader())
    using (SqlBulkCopy bulk = new SqlBulkCopy(targetConn_reportDB, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = "PatientEvent" })
    {
        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("PatientID", "PatientID"));
        bulk.WriteToServer(reader);
    }
}