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

Question

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

Я должен вставить около 2 миллионов строк из текстового файла.

И со вставкой я должен создать несколько мастер-таблиц.

Каков наилучший и быстрый способ вставить такой большой набор данных в SQL Server?

86

c# asp.net sql-server database ado.net

Источник

user1852537 05 дек '12 в 11:14

9 ответов

Решение

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

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

// 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 наверху

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

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

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

76

Источник

user314488 05 дек '12 в 11:18

Решение проблемы для 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 Класс может быть изменен для добавления сопоставления столбцов, если это необходимо. Например, у вас есть ключ идентификации в качестве первого столбца (при этом предполагается, что имена столбцов в таблице данных совпадают с базой данных)

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

33

Источник

user2421320 20 окт '16 в 13:53

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

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

6

Источник

user2249623 05 апр '13 в 15:12

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

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

foreach (traverse your loop here)
{
      toinsert.Add(string.Format("( '{0}', '{1}', '{2}' )", "Val1", "Val2", "Val3"));
}
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.

4

Источник

user10611503 25 дек '18 в 06:22

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

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

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

3

Источник

user1825029 05 дек '12 в 16:25

Я столкнулся с проблемой решения, которое должно работать с ADO, Entity и Dapper, поэтому я сделал эту библиотеку ; он генерирует партии в виде:

          IEnumerable<(string SqlQuery, IEnumerable<SqlParameter> SqlParameters)>  
    IEnumerable<(string SqlQuery, DynamicParameters DapperDynamicParameters)>

эта ссылка содержит инструкции. Это безопасно от SQL-инъекций, потому что вместо конкатенации используются параметры; вы также можете включить вставку идентификатора, если это необходимо, с помощью необязательного параметра.

Использование с ADO.NET:

      using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
    .SetTableName("People")
    .AddMapping(person => person.FirstName, columnName: "Name")
    .AddMapping(person => person.LastName, columnName: "Surename")
    .AddMapping(person => person.DateOfBirth, columnName: "Birthday")
    .Build();
var people = new List<Person>()
{ 
    new Person()
    {
        FirstName = "John", 
        LastName = "Lennon", 
        DateOfBirth = new DateTime(1940, 10, 9) 
    },
    new Person()
    {
        FirstName = "Paul", 
        LastName = "McCartney", 
        DateOfBirth = new DateTime(1942, 6, 18) 
    },
};
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";
var sqlQueriesAndParameters = new MsSqlQueryGenerator()
    .GenerateParametrizedBulkInserts(mapper, people);

using (var sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();
    
    // Default batch size: 1000 rows or (2100-1) parameters per insert.
    foreach (var (SqlQuery, SqlParameters) in sqlQueriesAndParameters)
    {
        using (SqlCommand sqlCommand = new SqlCommand(SqlQuery, sqlConnection))
        {
            sqlCommand.Parameters.AddRange(SqlParameters.ToArray());
            sqlCommand.ExecuteNonQuery();
        }
    }
}

Использование с Даппером:

      using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
    .SetTableName("People")
    .AddMapping(person => person.FirstName, columnName: "Name")
    .AddMapping(person => person.LastName, columnName: "Surename")
    .AddMapping(person => person.DateOfBirth, columnName: "Birthday")
    .Build();
var people = new List<Person>()
{ 
    new Person()
    {
        FirstName = "John", 
        LastName = "Lennon", 
        DateOfBirth = new DateTime(1940, 10, 9) 
    },
    new Person()
    { 
        FirstName = "Paul", 
        LastName = "McCartney", 
        DateOfBirth = new DateTime(1942, 6, 18) 
    },
};
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";
var sqlQueriesAndDapperParameters = new MsSqlQueryGenerator()
    .GenerateDapperParametrizedBulkInserts(mapper, people);

using (var sqlConnection = new SqlConnection(connectionString))
{
    // Default batch size: 1000 rows or (2100-1) parameters per insert.
    foreach (var (SqlQuery, DapperDynamicParameters) in sqlQueriesAndDapperParameters)
    {
        sqlConnection.Execute(SqlQuery, DapperDynamicParameters);
    }
}

Использование с Entity Framework:

      using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
    .SetTableName("People")
    .AddMapping(person => person.FirstName, columnName: "Name")
    .AddMapping(person => person.LastName, columnName: "Surename")
    .AddMapping(person => person.DateOfBirth, columnName: "Birthday")
    .Build();
var people = new List<Person>()
{ 
    new Person() 
    { 
        FirstName = "John", 
        LastName = "Lennon", 
        DateOfBirth = new DateTime(1940, 10, 9) 
    },
    new Person()
    { 
        FirstName = "Paul", 
        LastName = "McCartney", 
        DateOfBirth = new DateTime(1942, 6, 18) 
    },
};
var sqlQueriesAndParameters = new MsSqlQueryGenerator()
    .GenerateParametrizedBulkInserts(mapper, people);

// Default batch size: 1000 rows or (2100-1) parameters per insert.
foreach (var (SqlQuery, SqlParameters) in sqlQueriesAndParameters)
{
    _context.Database.ExecuteSqlRaw(SqlQuery, SqlParameters);
    // Depracated but still works: _context.Database.ExecuteSqlCommand(SqlQuery, SqlParameters);
}

2

Источник

user15243302 10 апр '21 в 19:44

Другой способ: если текстовые данные находятся в формате Json, вы можете использовать метод OPENJSON в SQL Server для массовой вставки. Я проверил на своем локальном компьютере и смог вставить один миллион записей за 51 секунду.

Это хранимая процедура:

      CREATE PROCEDURE sp_upsert_play_user_details1  
(  
@array VARCHAR(MAX)  
)  
AS  
BEGIN  
BEGIN TRY  
BEGIN TRANSACTION  
  
 INSERT INTO tbl_play_user_details    
 (vc_first_name, vc_last_name, vc_full_name, vc_gender, vc_phone_number, vc_email, vc_pet, vc_vehicle_model, vc_vehicle_no, int_created_on, int_created_by)    

 SELECT firstName, lastName, fullName, gender, phoneNumber, email, pet, vehicle, vehicleNumber, GETDATE(), createdBy  FROM OPENJSON(@array)  
        WITH (  firstName VARCHAR(100),  
    lastName VARCHAR(100),  
    fullName VARCHAR(100),  
    gender VARCHAR(100),  
    phoneNumber VARCHAR(100),  
    email VARCHAR(100),  
    pet VARCHAR(100),  
    vehicle VARCHAR(100),  
    vehicleNumber VARCHAR(100),  
                createdBy int);  
  
COMMIT TRANSACTION  
END TRY  
BEGIN CATCH  
ROLLBACK TRANSACTION                            
DECLARE @ErrorMessage NVARCHAR(4000)=ERROR_MESSAGE()+' Please verify "'+ERROR_PROCEDURE()+'" stored procedure at the line number '+CONVERT(NVARCHAR(20),ERROR_LINE() )+ '.';                                      
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();                                      
DECLARE @ErrorState INT=ERROR_STATE();                                      
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)   
END CATCH  
END  
GO

Пример JSON для тестирования:

      DECLARE @array VARCHAR(MAX);  
SET @array = '[{  
        "firstName": "Winston",  
        "lastName": "Lemke",  
        "fullName": "Winston Lemke",  
        "gender": "Male",  
        "phoneNumber": "466.780.4652 x268",  
        "email": "Josefa89@yahoo.com",  
        "pet": "Villanuco de Las Encartaciones",  
        "vehicle": "Mazda Escalade",  
        "vehicleNumber": "8CP7UC1N83MY25770",  
        "createdBy": 1  
    },  
 {  
        "firstName": "Finn",  
        "lastName": "Bartoletti",  
        "fullName": "Finn Bartoletti",  
        "gender": "Female",  
        "phoneNumber": "1-931-498-0214 x454",  
        "email": "Clair.Rodriguez@hotmail.com",  
        "pet": "Bouvier des Flandres",  
        "vehicle": "Tesla Ranchero",  
        "vehicleNumber": "MG1XVY29D0M798471",  
        "createdBy": 1  
    }]';  

EXEC sp_upsert_play_user_details1 @array;

В С# я проанализировал данные из локального файла и передал строку хранимой процедуре:

      string text = System.IO.File.ReadAllText(@"C:\Users\krish\OneDrive\Desktop\object.json");

_playdb.CommandTimeout = 3000;
_playdb.sp_upsert_play_user_details1(text);

Как упоминалось выше, для вставки записей в один миллион потребовалось всего 51 секунда и, вероятно, намного быстрее на более быстром сервере / рабочей машине.

0

Источник

user14401306 07 май '22 в 12:06

SqlBulkCopy это хорошо.

но вы должны подготовить Данные не являются проблемой, например, PK\FK не конфликтует, поэтому вставить 200k, записанные в SQL Server, очень просто.

0

Источник

user2488622 23 сен '18 в 13:18

Другие вопросы по тегам c# asp.net sql-server database ado.net

user447156 05 дек '12 в 11:23 2012-12-05 11:23 · Accepted Answer · 2012-12-05 11:23

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

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

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

63

Источник

user447156 05 дек '12 в 11:23