SQL Server에 200만 행 빠르게 삽입

SQL Server에 200만 행 빠르게 삽입

텍스트 파일에서 약 200만 행을 삽입해야 합니다.

삽입으로 마스터 테이블을 작성해야 합니다.

SQL Server에 이러한 대규모 데이터 세트를 삽입하는 가장 빠르고 최선의 방법은 무엇입니까?

1. 데이터셋에 있는 텍스트 파일의 데이터를 읽는 것이 좋다고 생각합니다.

2. SqlBulkCopy - C# App에서 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. DataSet에서 XML 생성
3. XML을 데이터베이스에 전달하고 대량 삽입을 수행합니다.

자세한 내용은 C# DataTable 및 SQL Server OpenXML 함수를 사용한 데이터 일괄 삽입을 참조하십시오.

그러나 200만 장의 레코드로 테스트되지 않고 200만 장의 레코드를 로드하여 삽입해야 하므로 기계상의 메모리만 소비합니다.

수업과 함께 해 볼 수 있습니다.

다른 원본의 데이터를 사용하여 SQL 서버 테이블을 효율적으로 대량 로드할 수 있습니다.

사용법에 대한 멋진 블로그 투고가 있습니다.

SqlBulkCopy 솔루션:

StreamReader를 사용하여 텍스트 파일을 변환하고 처리했습니다.결과는 내 물건 목록이었다.

수강 이상의 클래스를 만들었습니다.Datatable또는List<T>및 버퍼 크기(CommitBatchSize(두 번째 클래스의) 확장자를 사용하여 목록을 데이터 테이블로 변환합니다.

그것은 매우 빨리 작동한다.PC에서는 10초 이내에 1,000만 개 이상의 복잡한 레코드를 삽입할 수 있습니다.

클래스는 다음과 같습니다.

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 키를 첫 번째 열로 지정합니다(데이터 테이블의 열 이름이 데이터베이스와 동일하다고 가정합니다).

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

저는 bcp 유틸리티를 사용합니다. (Bulk Copy Program) 저는 매달 약 150만 개의 문자 기록을 로드합니다.각 텍스트 레코드의 너비는 800자입니다.내 서버에서는 150만 개의 텍스트 레코드를 SQL Server 테이블에 추가하는 데 약 30초가 걸립니다.

bcp 의 순서에 대해서는, http://msdn.microsoft.com/en-us/library/ms162802.aspx 를 참조해 주세요.

최근 이 시나리오(700만 행이 훨씬 넘는 경우)에서 한 번에 5,000개의 세그먼트에서 sqlcmd via powershell(생데이터를 SQL insert 문으로 해석한 후)을 사용하기 시작했습니다(SQL은 한 번에 700만 행을 처리할 수 없습니다.또한 5K로 분할하지 않으면 500,000 행을 처리할 수 없습니다).SQL Server 2012 Enterprise에서 새로운 sequence 명령을 활용해야 했기 때문에 각 5K 스크립트를 차례로 실행할 수 있습니다.시퀀스 명령으로 700만 행의 데이터를 빠르고 효율적으로 삽입할 수 있는 프로그램을 찾을 수 없었습니다.

둘째, 한 번에 100만 행 이상의 데이터를 삽입할 때 주의해야 할 사항 중 하나는 삽입 프로세스 중 CPU와 메모리 소비량(대부분 메모리)입니다.SQL은 해당 프로세스를 해제하지 않고 이 크기의 작업으로 메모리/CPU를 소비합니다.서버에 충분한 처리 능력이나 메모리가 없는 경우는, 단시간에 크래쉬 할 수 있습니다(어렵게 알았습니다).메모리 소비량이 70~75%를 넘는 경우 서버를 재부팅하면 프로세스가 정상으로 돌아갑니다.

실제로 최종 실행 계획을 세우기 전에 서버의 제한(CPU/메모리 리소스가 한정되어 있는 경우)을 확인하기 위해 수많은 시행착오 테스트를 수행해야 했습니다.실제 가동 환경에 도입하기 전에 테스트 환경에서 동일한 작업을 수행하는 것이 좋습니다.

이 방법으로 시도했더니 데이터베이스 삽입 실행 시간이 대폭 단축되었습니다.

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 connection Object를 작성한 위치에 SQL connection Object를 만듭니다.

ADO, 엔티티 및 Dapper와 연동해야 하는 솔루션의 문제에 직면했습니다.따라서 이 lib는 다음과 같은 형태로 배치가 생성됩니다.

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

이 링크에는 순서가 포함되어 있습니다.SQL Injection에 대해 안전합니다.연결 대신 매개 변수를 사용하기 때문에 필요에 따라 옵션 매개 변수를 사용하여 ID 삽입을 ON으로 설정할 수도 있습니다.

ADO에서의 사용.네트워크:

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();
        }
    }
}

Dapper에서의 사용:

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);
    }
}

엔티티 프레임워크에서의 사용:

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);
}

또한 텍스트 데이터가 Json 형식인 경우 SQL Server에서 대량 삽입에 OPENJSON 메서드를 사용할 수 있습니다.로컬 머신으로 테스트한 결과 51초 만에 100만 장의 레코드를 삽입할 수 있었습니다.

저장 프로시저는 다음과 같습니다.

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;

C#에서는 로컬파일의 데이터를 해석하여 스트링을 스토어드 프로시저에 전달했습니다.

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

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

위에서 설명한 바와 같이 1밀리 레코드를 삽입하는 데 51초밖에 걸리지 않았으며, 더 빠른 서버/워크 머신에서는 훨씬 더 빠를 수 있습니다.

언급URL : https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly