Como posso inserir 10 milhões de registros no menor tempo possível?

Por favor, não crie um DataTable para carregar via BulkCopy. Essa é uma solução ok para conjuntos menores de dados, mas não há absolutamente nenhuma razão para carregar todos os 10 milhões de linhas na memory antes de chamar o database.

Sua melhor aposta (fora do BCP / BULK INSERT / OPENROWSET(BULK...) ) é transmitir o conteúdo do arquivo para o database via um Table-Valued Parameter (TVP). Usando um TVP você pode abrir o arquivo, ler uma linha e enviar uma linha até terminar, e depois fechar o arquivo. Este método tem uma pegada de memory de apenas uma única linha. Eu escrevi um artigo, Streaming Data Into SQL Server 2008 de um aplicativo , que tem um exemplo deste mesmo cenário.

Uma visão geral simplista da estrutura é a seguinte. Estou assumindo a mesma tabela de importação e nome de campo, conforme mostrado na pergunta acima.

Objetos de database requeridos:

 -- First: You need a User-Defined Table Type CREATE TYPE ImportStructure AS TABLE (Field VARCHAR(MAX)); GO -- Second: Use the UDTT as an input param to an import proc. -- Hence "Tabled-Valued Parameter" (TVP) CREATE PROCEDURE dbo.ImportData ( @ImportTable dbo.ImportStructure READONLY ) AS SET NOCOUNT ON; -- maybe clear out the table first? TRUNCATE TABLE dbo.DATAs; INSERT INTO dbo.DATAs (DatasField) SELECT Field FROM @ImportTable; GO 

C # app código para fazer uso dos objects SQL acima está abaixo. Observe como, em vez de preencher um object (por exemplo, DataTable) e, em seguida, executar o Stored Procedure, neste método, é a execução do Stored Procedure que inicia a leitura do conteúdo do arquivo. O parâmetro de input do Stored Proc não é uma variável; é o valor de retorno de um método, GetFileContents . Esse método é chamado quando o SqlCommand chama ExecuteNonQuery , que abre o arquivo, lê uma linha e envia a linha para o SQL Server por meio do IEnumerable e yield return construções e, em seguida, fecha o arquivo. O procedimento armazenado apenas vê uma variável de tabela, @ImportTable, que pode ser acessada assim que os dados começam a aparecer ( observação: os dados persistem por um curto período de tempo, mesmo que não o conteúdo completo, em tempdb ).

 using System.Collections; using System.Data; using System.Data.SqlClient; using System.IO; using Microsoft.SqlServer.Server; private static IEnumerable GetFileContents() { SqlMetaData[] _TvpSchema = new SqlMetaData[] { new SqlMetaData("Field", SqlDbType.VarChar, SqlMetaData.Max) }; SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema); StreamReader _FileReader = null; try { _FileReader = new StreamReader("{filePath}"); // read a row, send a row while (!_FileReader.EndOfStream) { // You shouldn't need to call "_DataRecord = new SqlDataRecord" as // SQL Server already received the row when "yield return" was called. // Unlike BCP and BULK INSERT, you have the option here to create a string // call ReadLine() into the string, do manipulation(s) / validation(s) on // the string, then pass that string into SetString() or discard if invalid. _DataRecord.SetString(0, _FileReader.ReadLine()); yield return _DataRecord; } } finally { _FileReader.Close(); } } 

O método GetFileContents acima é usado como o valor do parâmetro de input para o procedimento armazenado, conforme mostrado abaixo:

 public static void test() { SqlConnection _Connection = new SqlConnection("{connection string}"); SqlCommand _Command = new SqlCommand("ImportData", _Connection); _Command.CommandType = CommandType.StoredProcedure; SqlParameter _TVParam = new SqlParameter(); _TVParam.ParameterName = "@ImportTable"; _TVParam.TypeName = "dbo.ImportStructure"; _TVParam.SqlDbType = SqlDbType.Structured; _TVParam.Value = GetFileContents(); // return value of the method is streamed data _Command.Parameters.Add(_TVParam); try { _Connection.Open(); _Command.ExecuteNonQuery(); } finally { _Connection.Close(); } return; } 

Notas Adicionais:

1. Com alguma modificação, o código C # acima pode ser adaptado para agrupar os dados.
2. Com pequenas modificações, o código C # acima pode ser adaptado para enviar múltiplos campos (o exemplo mostrado no artigo “Steaming Data …” linkado acima passa em 2 campos).
3. Você também pode manipular o valor de cada registro na SELECT no proc.
4. Você também pode filtrar as linhas usando uma condição WHERE no proc.
5. Você pode acessar a variável da tabela TVP várias vezes; é READONLY, mas não “somente forward”.
6. Vantagens sobre o SqlBulkCopy :
   1. SqlBulkCopy é INSERT-only, enquanto o uso de um TVP permite que os dados sejam usados ​​de qualquer maneira: você pode chamar MERGE ; você pode DELETE baseado em alguma condição; você pode dividir os dados em várias tabelas; e assim por diante.
   2. Devido a um TVP não ser INSERT-only, você não precisa de uma tabela de teste separada para despejar os dados.
   3. Você pode obter dados de volta do database chamando ExecuteReader vez de ExecuteNonQuery . Por exemplo, se houvesse um campo IDENTITY na tabela de importação de DATAs , você poderia adicionar uma cláusula OUTPUT ao INSERT para passar de volta INSERTED.[ID] (assumindo que ID é o nome do campo IDENTITY ). Ou você pode passar de volta os resultados de uma consulta completamente diferente, ou ambos, já que vários conjuntos de resultados podem ser enviados e acessados ​​via Reader.NextResult() . Obtendo informações de volta do database não é possível quando se utiliza SqlBulkCopy ainda existem várias perguntas aqui em SO de pessoas que querem fazer exatamente isso (pelo menos no que diz respeito aos valores IDENTITY recém-criados).
   4. Para obter mais informações sobre por que às vezes é mais rápido para o processo geral, mesmo que seja um pouco mais lento para obter os dados do disco no SQL Server, consulte este whitepaper da equipe de consultoria ao cliente do SQL Server: Maximizar o rendimento com TVP

Em C #, a melhor solução é deixar o SqlBulkCopy ler o arquivo. Para fazer isso, você precisa passar um IDataReader direto para o método SqlBulkCopy.WriteToServer . Aqui está um exemplo: http://www.codeproject.com/Articles/228332/IDataReader-implementation-plus-SqlBulkCopy

A melhor maneira é uma mistura entre a sua primeira solução e a segunda, crie DataTable e, no loop, adicione linhas a ela e use o BulkCopy para fazer o upload para o database em uma conexão. Use isso para obter ajuda na cópia em massa

Uma outra coisa a prestar atenção é que a cópia em massa é uma operação muito sensível que quase todo erro anulará a cópia, tal se você declarar o nome da coluna no dataTable como “texto” e no DB seu “Text” ele lançará uma exceção , boa sorte.

Se você deseja inserir 10 milhões de registros no menor tempo para direcionar usando a consulta SQL para fins de teste, você deve usar essa consulta

  CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME) GO INSERT INTO TestData(CreatedDate) SELECT GetDate() GO 10000000