Insira 2 milhões de linhas no SQL Server rapidamente

Eu tenho que inserir cerca de 2 milhões de linhas de um arquivo de texto.

E com a inserção eu tenho que criar algumas tabelas mestras.

Qual é a melhor e mais rápida maneira de inserir um conjunto tão grande de dados no SQL Server?

Você pode tentar com a class SqlBulkCopy .

Permite com eficiência carregar em massa uma tabela do SQL Server com dados de outra origem.

Há um post legal sobre como você pode usá-lo.

  1. Eu acho melhor você ler os dados do arquivo de texto no DataSet

  2. Experimente o SqlBulkCopy – Inserção em massa no SQL do C # App

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

ou

depois de fazer o passo 1 no topo

  1. Criar XML do DataSet
  2. Passar XML para o database e inserir em massa

você pode verificar este artigo para detalhes: Inserção em massa de dados usando C # DataTable e SQL Server OpenXML function

Mas não é testado com 2 milhões de registros, mas consome memory na máquina, já que você tem que carregar 2 milhões de registros e inseri-los.

Re a solução para SqlBulkCopy:

Eu usei o StreamReader para converter e processar o arquivo de texto. O resultado foi uma lista do meu object.

Eu criei uma class que leva Datatable ou um List e um tamanho de Buffer ( CommitBatchSize ). Ele converterá a lista em uma tabela de dados usando uma extensão (na segunda class).

Isso funciona muito rápido. No meu PC, posso inserir mais de 10 milhões de registros complicados em menos de 10 segundos.

Aqui está a aula:

 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 { public IList 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(this IEnumerable 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; } } 

}

Aqui está um exemplo quando eu quero inserir uma lista do meu object personalizado List ( ListDetections ):

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

A class BulkInsert pode ser modificada para adicionar mapeamento de coluna, se necessário. Exemplo você tem uma chave de identidade como primeira coluna (assumindo que os nomes das colunas na tabela de dados são os mesmos que os do database)

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

Eu encontrei esse cenário recentemente (bem acima de 7 milhões de linhas) e usei o sqlcmd via powershell (depois de analisar dados brutos em instruções de inserção SQL) em segmentos de 5.000 de uma vez (SQL não suporta 7 milhões de linhas em um trabalho fixo ou até mesmo 500.000 linhas, a menos que sejam divididas em partes menores de 5 K. Você pode executar cada script de 5 K, um após o outro.), pois eu precisava aproveitar o novo comando de sequência no SQL Server 2012 Enterprise. Não consegui encontrar uma maneira programática de inserir sete milhões de linhas de dados com rapidez e eficiência com o comando de sequência.

Em segundo lugar, uma das coisas a observar ao inserir um milhão de linhas ou mais de dados em uma session é o consumo de CPU e memory (principalmente memory) durante o processo de inserção. O SQL consome memory / CPU com um trabalho dessa magnitude sem liberar esses processos. Escusado será dizer que se você não tem poder de processamento suficiente ou memory em seu servidor você pode travá-lo muito facilmente em pouco tempo (que eu descobri da maneira mais difícil). Se você chegar ao ponto em que seu consumo de memory está acima de 70-75%, basta reinicializar o servidor e os processos serão liberados de volta ao normal.

Eu tive que executar um monte de testes de tentativa e erro para ver quais eram os limites para o meu servidor (tendo em conta os resources limitados de CPU / memory para trabalhar) antes que eu pudesse realmente ter um plano de execução final. Eu sugiro que você faça o mesmo em um ambiente de teste antes de colocar isso em produção.

Eu uso o utilitário bcp. (Programa de Cópia em Massa) Eu carrego cerca de 1,5 milhão de registros de texto por mês. Cada registro de texto tem 800 caracteres de largura. No meu servidor, demora cerca de 30 segundos para adicionar os 1,5 milhões de registros de texto em uma tabela do SQL Server.

As instruções para o bcp estão em http://msdn.microsoft.com/pt-br/library/ms162802.aspx