SQL Server에 2 백만 개의 행을 빠르게 삽입

SQL Server에 2 백만 개의 행을 빠르게 삽입

---

텍스트 파일에서 약 2 백만 개의 행을 삽입해야합니다.

그리고 삽입과 함께 몇 가지 마스터 테이블을 만들어야합니다.

이러한 대규모 데이터 집합을 SQL Server에 삽입하는 가장 좋고 빠른 방법은 무엇입니까?

---

SqlBulkCopy수업 과 함께 시도 할 수 있습니다 .

다른 소스의 데이터가 포함 된 SQL Server 테이블을 효율적으로 대량로드 할 수 있습니다.

사용 방법에 대한 멋진 블로그 게시물 이 있습니다.

---

1. DataSet에서 텍스트 파일의 데이터를 읽는 것이 더 낫다고 생각합니다.

2. SqlBulkCopy - C # 앱에서 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 함수를 사용한 데이터 대량 삽입

그러나 2 백만 개의 레코드로 테스트되지는 않았지만 2 백만 개의 레코드를로드하고 삽입해야하므로 컴퓨터에서 메모리를 소모합니다.

---

SqlBulkCopy에 대한 해결책 :

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

take Datatable또는 a 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();

The BulkInsert class can be modified to add column mapping if required. Example you have an Identity key as first column.(this assuming that the column names in the datatable are the same as the database)

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

---

I use the bcp utility. (Bulk Copy Program) I load about 1.5 million text records each month. Each text record is 800 characters wide. On my server, it takes about 30 seconds to add the 1.5 million text records into a SQL Server table.

The instructions for bcp are at http://msdn.microsoft.com/en-us/library/ms162802.aspx

---

I ran into this scenario recently (well over 7 million rows) and eneded up using sqlcmd via powershell (after parsing raw data into SQL insert statements) in segments of 5,000 at a time (SQL can't handle 7 million lines in one lump job or even 500,000 lines for that matter unless its broken down into smaller 5K pieces. You can then run each 5K script one after the other.) as I needed to leverage the new sequence command in SQL Server 2012 Enterprise. I couldn't find a programatic way to insert seven million rows of data quickly and efficiently with said sequence command.

Secondly, one of the things to look out for when inserting a million rows or more of data in one sitting is the CPU and memory consumption (mostly memory) during the insert process. SQL will eat up memory/CPU with a job of this magnitude without releasing said processes. Needless to say if you don't have enough processing power or memory on your server you can crash it pretty easily in a short time (which I found out the hard way). If you get to the point to where your memory consumption is over 70-75% just reboot the server and the processes will be released back to normal.

I had to run a bunch of trial and error tests to see what the limits for my server was (given the limited CPU/Memory resources to work with) before I could actually have a final execution plan. I would suggest you do the same in a test environment before rolling this out into production.

---

I tried with this method and it significantly reduced my database insert execution time.

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

*Create SQL connection object and replace it where I have written SQLconnectionObject.

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