c# - una - sql server array parameter

¿Cómo puedo insertar 10 millones de registros en el menor tiempo posible?

Question

Tengo un archivo (que tiene 10 millones de registros) como el siguiente:

    line1
    line2
    line3
    line4
   .......
    ......
    10 million lines

Entonces, básicamente, quiero insertar 10 millones de registros en la base de datos. entonces leo el archivo y lo cargo a SQL Server.

C # code

System.IO.StreamReader file = 
    new System.IO.StreamReader(@"c:/test.txt");
while((line = file.ReadLine()) != null)
{
    // insertion code goes here
    //DAL.ExecuteSql("insert into table1 values("+line+")");
}

file.Close();

pero la inserción llevará mucho tiempo. ¿Cómo puedo insertar 10 millones de registros en el menor tiempo posible usando C #?

Actualización 1:
A granel INSERTAR:

BULK INSERT DBNAME.dbo.DATAs
FROM ''F:/dt10000000/dt10000000.txt''
WITH
(

     ROWTERMINATOR ='' /n''
  );

Mi mesa es como a continuación:

DATAs
(
     DatasField VARCHAR(MAX)
)

pero estoy recibiendo el siguiente error:

Msg 4866, nivel 16, estado 1, línea 1
La carga masiva falló. La columna es demasiado larga en el archivo de datos para la fila 1, columna 1. Verifique que el terminador de campo y el terminador de fila estén especificados correctamente.

Msg 7399, nivel 16, estado 1, línea 1
El proveedor OLE DB "BULK" para el servidor vinculado "(nulo)" informó un error. El proveedor no dio ninguna información sobre el error.

Msg 7330, nivel 16, estado 2, línea 1
No se puede obtener una fila del proveedor OLE DB "BULK" para el servidor vinculado "(nulo)".

Debajo del código trabajado:

BULK INSERT DBNAME.dbo.DATAs
FROM ''F:/dt10000000/dt10000000.txt''
WITH
(
    FIELDTERMINATOR = ''/t'',
    ROWTERMINATOR = ''/n''
);

Answer 1

En C #, la mejor solución es dejar que SqlBulkCopy lea el archivo. Para hacer esto, debe pasar un IDataReader directo al método SqlBulkCopy.WriteToServer . Aquí hay un ejemplo: http://www.codeproject.com/Articles/228332/IDataReader-implementation-plus-SqlBulkCopy

Answer 2

Si desea insertar 10 millones de registros en el menor tiempo posible para dirigir el uso de consultas SQL con fines de prueba, debe utilizar esta consulta

 CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME)
 GO

 INSERT INTO TestData(CreatedDate) SELECT GetDate()
 GO 10000000

Answer 3

la mejor manera es una mezcla entre su primera solución y la segunda, cree DataTable y en el bucle agregue filas y luego use BulkCopy para cargar en DB en una conexión; use esto para obtener ayuda en la copia masiva.

Otra cosa para prestar atención es que la copia masiva es una operación muy sensible que casi todos los errores anularán la copia, por ejemplo, si declara el nombre de la columna en la tabla de datos como "texto" y en el DB su "Texto" arrojará una excepción , buena suerte.

Answer 4

No cree una DataTable para cargar a través de BulkCopy. Esa es una buena solución para conjuntos más pequeños de datos, pero no hay absolutamente ninguna razón para cargar los 10 millones de filas en la memoria antes de llamar a la base de datos.

Su mejor opción (fuera de BCP / BULK INSERT / OPENROWSET(BULK...) ) es transmitir el contenido del archivo a la base de datos a través de un parámetro con valor de tabla (TVP). Al usar un TVP, puede abrir el archivo, leer una fila y enviar una fila hasta que finalice, y luego cerrar el archivo. Este método tiene una huella de memoria de solo una fila. Escribí un artículo, Transmisión de datos en SQL Server 2008 desde una aplicación , que tiene un ejemplo de este mismo escenario.

Una descripción simplista de la estructura es la siguiente. Asumo la misma tabla de importación y el mismo nombre de campo que se muestran en la pregunta anterior.

Objetos de base de datos 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

El código de la aplicación C # para hacer uso de los objetos SQL anteriores está a continuación. Observe cómo en lugar de llenar un objeto (por ejemplo, DataTable) y luego ejecutar el Procedimiento almacenado, en este método es la ejecución del Procedimiento almacenado lo que inicia la lectura del contenido del archivo. El parámetro de entrada de Stored Proc no es una variable; es el valor de retorno de un método, GetFileContents . Ese método se invoca cuando el SqlCommand llama a ExecuteNonQuery , que abre el archivo, lee una fila y envía la fila al SQL Server a través de IEnumerable<SqlDataRecord> y yield return construcciones de yield return , y luego cierra el archivo. El procedimiento almacenado solo ve una variable de tabla, @ImportTable, que se puede acceder tan pronto como los datos comiencen a llegar ( nota: los datos persisten por un tiempo breve, incluso si no todo el contenido, en tempdb ).

using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private static IEnumerable<SqlDataRecord> 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();
   }
}

El método GetFileContents anterior se utiliza como el valor del parámetro de entrada para el Procedimiento almacenado como se muestra a continuación:

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 adicionales:

1. Con alguna modificación, el código C # anterior se puede adaptar para agrupar los datos.
2. Con modificaciones menores, el código C # anterior se puede adaptar para enviar en múltiples campos (el ejemplo que se muestra en el artículo "Steaming Data ..." vinculado anteriormente pasa en 2 campos).
3. También puede manipular el valor de cada registro en la instrucción SELECT en el proceso.
4. También puede filtrar filas utilizando una condición WHERE en el proceso.
5. Puede acceder a la variable de tabla TVP varias veces; es READONLY pero no solo "forward".
6. Ventajas sobre SqlBulkCopy :
   1. SqlBulkCopy es INSERT-only, mientras que el uso de un TVP permite utilizar los datos de cualquier manera: puede llamar a MERGE ; puede DELETE según alguna condición; puedes dividir los datos en varias tablas; y así.
   2. Debido a que un TVP no es INSERT-only, no necesita una tabla de etapas separada para volcar los datos.
   3. Puede recuperar datos de la base de datos llamando ExecuteReader lugar de ExecuteNonQuery . Por ejemplo, si había un campo IDENTITY en la tabla de importación de DATAs , podría agregar una cláusula OUTPUT al INSERT para devolver INSERTED.[ID] (suponiendo que ID es el nombre del campo IDENTITY ). O puede pasar los resultados de una consulta completamente diferente, o ambas, ya que se pueden enviar y acceder a múltiples conjuntos de resultados a través de Reader.NextResult() . Obtener información de la base de datos no es posible cuando se usa SqlBulkCopy embargo, hay varias preguntas aquí en SO de personas que desean hacer exactamente eso (al menos con respecto a los valores de IDENTITY recién creados).
   4. Para obtener más información sobre por qué a veces es más rápido para todo el proceso, aunque sea un poco más lento para obtener los datos del disco en SQL Server, consulte este documento técnico del equipo de asesoramiento al cliente de SQL Server: Maximización del rendimiento con TVP