Saltar al contenido principal

 none
Métodos para insertar múltiples filas en una tabla o varias tablas ? SQL SERVER RRS feed

  • Pregunta

  • Cómo realizar inserciones masivas, con buenas practicas, buen rendimiento y eficiencia, por ejemplo:?

    -Mediante Instrucciones SQL parametrizadas con múltiples declaraciones INSERT e insertadas individualmente en cada bucle y envuelta en una única transacción
    -Mediante Instrucciones SQL parametrizadas con múltiples declaraciones INSERT e insertadas en lote fuera del bucle en una única transacción, sin embargo, esta limitada a 2100 parámetros.
    -Mediante Instrucciones SQL parametrizadas en una sola declaración INSERT e insertadas en lote en una única transacción. Sin embargo, esta limitada a 2100 parámetros y 1000 inserciones de fila.

    -Mediante el método Update() de la clase DataAdapter.
    -Mediante Table-Valued Parameters (TVP), costo de inicio mínimo y puedes insertar hasta 1000 filas con buen rendimiento y rapidez, luego de superar las mil filas el rendimiento disminuye.
    -Mediante Bulk Insert, costo de inicio mayor, de igual manera el rendimiento disminuye rápidamente a medida que aumentan las filas.

    -Mediante SQL Bulk Copy, simplemente es necesario cargar los datos desde una DataTable o DataReader, puedes insertar hasta 700 mil filas con un rendimiento considerable, luego el rendimiento disminuye.

    -Mediante Entity Framework, Dapper, u otro ORM, que nos facilita y simplifica las cosas.

    Al usar Instrucciones SQL parametrizadas ya sea con múltiples
    declaraciones o declaraciones en lote para realizar pequeñas operaciones
    de inserción de filas (menos de 100 filas), se obtiene un buen
    rendimiento y eficacia en lugar de operaciones Bulk Insert, Table-Valued
    Parameters o Sql Bulk Copy, pero estos métodos son recomendables para
    insertar cientos o miles de filas. Sin embargo, el rendimiento disminuye
    a medida que aumentan las filas.

    sugerencias?

    jueves, 14 de noviembre de 2019 22:17

Respuestas

  • En mi experiencia, cuando tienes que insertar muchísimos miles de filas, lo que más corre es el SqlBulkCopy. Como probablemente ya sabes, metes los datos en un DataTable y se lo pasas al SqlBulkCopy, y este genera una conexión por streaming contra el servidor SQL que envía los datos a gran velocidad sin convertirlos en sentencias INSERT.

    No hay ninguna razón para que el rendimiento disminuya al llegar a las 700000 files. Puede ser un problema de lado cliente, en caso de que el tamaño del datatable ocasione algún problema tal como un disparo del Garbage Collector o algo así, pero en el lado servidor no tendría por qué variar nada. Si esto pasase, puedes usar un bucle que vaya procesando los datos por bloques, por ejemplo de cien mil en cien mil, en lugar de cargarlos todos a la vez en un único DataTable.

    También puede ser que se alcance algún límite en ese servidor concreto, por ejemplo, que se desborde la cache de buffers y se vea obligado a hacer un checkpoint al alcanzar ese número de registros, o algo parecido.

    Mencionaste el Entity Framework. Esto va a generar Insert parametrizadas y enviarlas una por una. Ojo, el rendimiento bajará rápidamente a partir de unos cientos de registros debido al Tracking de entidades. Asegúrate de deshabilitar el tracking si vas a hacer inserciones masivas, o por lo menos haz un SaveChanges cada pocos registros (por ejemplo, cada 100) en lugar de esperar a tenerlos todos cargados en memoria.

    viernes, 15 de noviembre de 2019 12:09
  • Consultando otras experiencias:

    "Una vez me enfrente con un proceso asi, encima la tabla tenia un
    indice por cada columna, la conclusion fue que lo que mas generaba
    demoras eran los indices, en cada insert se actualizan ... la solucion
    fue dropear los indices antes de iniciar el insert, hacer un bulk insert
    y volver a crear los indices.

    Si tenes muchos inidices, y en
    especial alguno clustered en una columna que no hagas insert
    secuenciales, probaria esot, mas alla de la herramienta que uses" Pablo Allois

    "Podes usar esta libreria https://github.com/borisdj/EFCore.BulkExtensions

    Es muy buena. Lo que hace por detras es:

    1) Crear una tabla temporal sin indices
    2) Insertar con Bulk todos los registros
    3) Hace un MERGE de la tabla temporal y tu tabla. 
    Todo lo hace transparente y es rapidisima.

    Si lo queres hacer sin EF, hace esos 3 pasos manualmente y vas a ver que la cosa vuela...

    Ademas con el agregado que tambien te modifica si el registro existe o borra si no existe.
    Es genial el MERGE.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15"

    Ideas: hacer esos 3 pasos con Dapper? crear tabla temporal sin índices, insertar con bulk, y merge entre tabla temporal y la tabla?

    Eugenio Serrano

    sábado, 16 de noviembre de 2019 13:48

Todas las respuestas

  • En mi experiencia, cuando tienes que insertar muchísimos miles de filas, lo que más corre es el SqlBulkCopy. Como probablemente ya sabes, metes los datos en un DataTable y se lo pasas al SqlBulkCopy, y este genera una conexión por streaming contra el servidor SQL que envía los datos a gran velocidad sin convertirlos en sentencias INSERT.

    No hay ninguna razón para que el rendimiento disminuya al llegar a las 700000 files. Puede ser un problema de lado cliente, en caso de que el tamaño del datatable ocasione algún problema tal como un disparo del Garbage Collector o algo así, pero en el lado servidor no tendría por qué variar nada. Si esto pasase, puedes usar un bucle que vaya procesando los datos por bloques, por ejemplo de cien mil en cien mil, en lugar de cargarlos todos a la vez en un único DataTable.

    También puede ser que se alcance algún límite en ese servidor concreto, por ejemplo, que se desborde la cache de buffers y se vea obligado a hacer un checkpoint al alcanzar ese número de registros, o algo parecido.

    Mencionaste el Entity Framework. Esto va a generar Insert parametrizadas y enviarlas una por una. Ojo, el rendimiento bajará rápidamente a partir de unos cientos de registros debido al Tracking de entidades. Asegúrate de deshabilitar el tracking si vas a hacer inserciones masivas, o por lo menos haz un SaveChanges cada pocos registros (por ejemplo, cada 100) en lugar de esperar a tenerlos todos cargados en memoria.

    viernes, 15 de noviembre de 2019 12:09
  • Consultando otras experiencias:

    "Una vez me enfrente con un proceso asi, encima la tabla tenia un
    indice por cada columna, la conclusion fue que lo que mas generaba
    demoras eran los indices, en cada insert se actualizan ... la solucion
    fue dropear los indices antes de iniciar el insert, hacer un bulk insert
    y volver a crear los indices.

    Si tenes muchos inidices, y en
    especial alguno clustered en una columna que no hagas insert
    secuenciales, probaria esot, mas alla de la herramienta que uses" Pablo Allois

    "Podes usar esta libreria https://github.com/borisdj/EFCore.BulkExtensions

    Es muy buena. Lo que hace por detras es:

    1) Crear una tabla temporal sin indices
    2) Insertar con Bulk todos los registros
    3) Hace un MERGE de la tabla temporal y tu tabla. 
    Todo lo hace transparente y es rapidisima.

    Si lo queres hacer sin EF, hace esos 3 pasos manualmente y vas a ver que la cosa vuela...

    Ademas con el agregado que tambien te modifica si el registro existe o borra si no existe.
    Es genial el MERGE.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15"

    Ideas: hacer esos 3 pasos con Dapper? crear tabla temporal sin índices, insertar con bulk, y merge entre tabla temporal y la tabla?

    Eugenio Serrano

    sábado, 16 de noviembre de 2019 13:48