Вставьте 2 миллиона строк в SQL Server быстро

Ответ 1

Вы можете попробовать с классом SqlBulkCopy.

Позволяет эффективно выполнять массовую загрузку таблицы SQL Server с данными из другого источника.

Есть классный пост в блоге о том, как вы можете его использовать.

Ответ 2

1. Я думаю, что лучше читать данные текстового файла в DataSet

2. Попробуйте SqlBulkCopy - массовая вставка в SQL из приложения С#

   // connect to SQL
   using (SqlConnection connection = new SqlConnection(connString))
   {
       // make sure to enable triggers
       // more on triggers in next post
       SqlBulkCopy bulkCopy = new SqlBulkCopy(
           connection, 
           SqlBulkCopyOptions.TableLock | 
           SqlBulkCopyOptions.FireTriggers | 
           SqlBulkCopyOptions.UseInternalTransaction,
           null
           );
   
       // set the destination table name
       bulkCopy.DestinationTableName = this.tableName;
       connection.Open();
   
       // write the data in the "dataTable"
       bulkCopy.WriteToServer(dataTable);
       connection.Close();
   }
   // reset
   this.dataTable.Clear();
   

или же

после выполнения шага 1 наверху

2. Создать XML из DataSet
3. Передайте XML в базу данных и выполните массовую вставку

Вы можете проверить эту статью подробнее: массовая вставка данных с помощью С# DataTable и SQL Server OpenXML функция

Но он не протестирован с 2 миллионами записей, он будет использовать только память на машине, так как вам нужно загрузить 2 миллиона записей и вставить ее.

Ответ 3

Re решение для SqlBulkCopy:

Я использовал StreamReader для преобразования и обработки текстового файла. Результатом стал список моего объекта.

Я создал класс, чем принимает Datatable или List<T> и размер буфера (CommitBatchSize). Он преобразует список в таблицу данных с использованием расширения (во втором классе).

Он работает очень быстро. На моем ПК я могу вставить более 10 миллионов сложных записей менее чем за 10 секунд.

Вот класс:

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

namespace DAL
{

public class BulkUploadToSql<T>
{
    public IList<T> InternalStore { get; set; }
    public string TableName { get; set; }
    public int CommitBatchSize { get; set; }=1000;
    public string ConnectionString { get; set; }

    public void Commit()
    {
        if (InternalStore.Count>0)
        {
            DataTable dt;
            int numberOfPages = (InternalStore.Count / CommitBatchSize)  + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
            for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
                {
                    dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
                BulkInsert(dt);
                }
        } 
    }

    public void BulkInsert(DataTable dt)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // make sure to enable triggers
            // more on triggers in next post
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            // set the destination table name
            bulkCopy.DestinationTableName = TableName;
            connection.Open();

            // write the data in the "dataTable"
            bulkCopy.WriteToServer(dt);
            connection.Close();
        }
        // reset
        //this.dataTable.Clear();
    }

}

public static class BulkUploadToSqlHelper
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        PropertyDescriptorCollection properties =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }
}

}

Вот пример, когда я хочу вставить список моего пользовательского объекта List<PuckDetection> (ListDetections):

var objBulk = new BulkUploadToSql<PuckDetection>()
{
        InternalStore = ListDetections,
        TableName= "PuckDetections",
        CommitBatchSize=1000,
        ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();

Класс BulkInsert может быть изменен для добавления сопоставления столбцов, если это необходимо. Например, у вас есть ключ Identity в качестве первого столбца (это предполагает, что имена столбцов в datatable совпадают с базой данных)

//ADD COLUMN MAPPING
foreach (DataColumn col in dt.Columns)
{
        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}

Ответ 4

Я использую утилиту bcp. (Программа массового копирования) Каждый месяц я загружаю около 1,5 миллионов текстовых записей. Каждая текстовая запись имеет ширину 800 символов. На моем сервере требуется около 30 секунд, чтобы добавить 1,5 миллиона текстовых записей в таблицу SQL Server.

Инструкции для bcp находятся в http://msdn.microsoft.com/en-us/library/ms162802.aspx

Ответ 5

Недавно я столкнулся с этим сценарием (более 7 миллионов строк) и стал использовать sqlcmd через powershell (после разбора необработанных данных в операторы вставки SQL) в сегментах по 5000 экземпляров (SQL не может обрабатывать 7 миллионов строк в один кусок работы или даже 500 000 строк, если только он не разбит на меньшие 5K штук. Затем вы можете запускать каждый 5K script один за другим.), поскольку мне нужно было использовать новую команду последовательности в SQL Server 2012 Enterprise. Я не мог найти программный способ быстро и эффективно вставить семь миллионов строк данных с указанной командой последовательности.

Во-вторых, одна из вещей, которые нужно учитывать при вставке миллионных строк или более данных за один сеанс, - это потребление ЦП и памяти (в основном, память) во время процесса вставки. SQL будет потреблять память/процессор с заданием такого масштаба без освобождения упомянутых процессов. Излишне говорить, что если у вас недостаточно вычислительной мощности или памяти на вашем сервере, вы можете быстро ее свернуть в кратчайшие сроки (что я обнаружил на своем пути). Если вы дойдете до того, что потребление памяти превышает 70-75%, просто перезагрузите сервер, и процессы будут возвращены в нормальное состояние.

Мне пришлось запустить кучу пробных и пробных тестов, чтобы узнать, какие ограничения для моего сервера (учитывая ограниченные ресурсы ЦП/памяти для работы), прежде чем я мог бы иметь окончательный план выполнения. Я бы посоветовал вам сделать то же самое в тестовой среде, прежде чем вывести ее в производство.

Ответ 6

Я попытался с этим методом, и это значительно сократило время выполнения моей вставки базы данных.

List<string> toinsert = new List<string>();
StringBuilder insertCmd = new StringBuilder("INSERT INTO tabblename (col1, col2, col3) VALUES ");

foreach (var row in rows)
{
      // the point here is to keep values quoted and avoid SQL injection
      var first = row.First.Replace("'", "''")
      var second = row.Second.Replace("'", "''")
      var third = row.Third.Replace("'", "''")

      toinsert.Add(string.Format("( '{0}', '{1}', '{2}' )", first, second, third));
}
if (toinsert.Count != 0)
{
      insertCmd.Append(string.Join(",", toinsert));
      insertCmd.Append(";");
}
using (MySqlCommand myCmd = new MySqlCommand(insertCmd.ToString(), SQLconnectionObject))
{
      myCmd.CommandType = CommandType.Text;
      myCmd.ExecuteNonQuery();
}

* Создайте объект соединения SQL и замените его там, где я написал SQLconnectionObject.