Administrar lotes para la importación masiva

En esta sección se describe cómo determinar el tamaño de lotes para una operación de importación masiva. De forma predeterminada, todas las filas de un archivo de datos se importan como un solo lote de tamaño desconocido en una transacción única. En este caso, si la operación de importación genera un error antes de completarse, se revierte toda la transacción y no se agregan datos a la tabla de destino. La operación con errores debe reiniciarse después desde el principio del archivo de datos.

La importación de un archivo de datos grande como un lote único puede ser complicada, por lo que bcp y BULK INSERT permiten importar datos en una serie de lotes, cada uno de los cuales es menor que el archivo de datos. Cada lote se importa y se registra en una transacción independiente y, una vez confirmada una transacción determinada, se confirman las filas importadas en la transacción. Si la operación genera un error, sólo se revierten las filas importadas desde el lote actual y se puede reanudar la importación de datos desde el principio del lote con errores, no desde el principio del archivo de datos.

[!NOTA] Para obtener información acerca de cómo funcionan los lotes, vea Lotes.

Por otro lado, si opta por no limitar el tamaño del lote, puede mejorar el rendimiento calculando el tamaño del archivo de datos en el comando. Su cálculo lo usa el procesador de consultas al crear el plan de consulta de la operación.

[!NOTA] Al especificar el tamaño del lote o el tamaño del archivo de datos, la precisión no es crítica.

En la tabla siguiente se resumen los calificadores que permiten estas alternativas.

Comando

Tamaño del lote

Filas enviadas por lote

Kilobytes enviados por lote

bcp1

-bbatch_size

-h "ROWS_PER_BATCH = bb"

-h "KILOBYTES_PER_BATCH = cc"

BULK INSERT2

BATCHSIZE = batch_size

ROWS_PER_BATCH = rows_per_batch

KILOBYTES_PER_BATCH = kilobytes_per_batch

Nota:

Al usar esta opción con BATCHSIZE se genera un error.

INSERT ... SELECT * FROM OPENROWSET(BULK...)

—3

ROWS_PER_BATCH = rows_per_batch

—3

1 En un comando bcp, no utilice el modificador -bbatch_size conjuntamente con la sugerencia ROWS_PER_BATCH o KILOBYTES_PER_BATCH. Si los combina, se genera un error.

2 En un comando BULK INSERT, si utiliza BATCHSIZE con ROWS_PER_BATCH o KILOBYTES_PER_BATCH, prevalece BATCHSIZE.

3 OPENROWSET no tiene las opciones BATCHSIZE ni KILOBYTES_PER_BATCH.

En las siguientes secciones se describe el uso de estos calificadores.

Especificar el tamaño de lote aproximado

Si importa un gran número de filas, dividir los datos en lotes puede ofrecer ventajas. Una vez terminados todos los lotes, la transacción se registra. Si, por cualquier motivo, la operación de importación masiva termina antes de completarse, sólo se revierte la transacción (lote) actual.

[!NOTA] El registro masivo incluye una copia de los datos importados en el registro de transacciones. Esto puede hacer que el registro crezca rápidamente, pero después de cada lote, puede hacer una copia de seguridad del registro para reclamar espacio del registro.

Para importar el archivo de datos de una serie de lotes de aproximadamente un tamaño especificado, use el siguiente calificador:

• Para bcp: -b
• Para BULK INSERT: BATCHSIZE

Cada lote de filas se inserta como una transacción independiente. Si, por cualquier motivo, la operación de importación masiva termina antes de completarse, sólo se revierte la transacción actual. Por ejemplo, si un archivo de datos contiene 1000 filas y se utiliza un tamaño de lote de 100, Microsoft SQL Server registrará la operación como 10 transacciones independientes; cada una de estas transacciones insertará 100 filas en la tabla de destino. Si la operación de importación masiva termina mientras se está cargando la fila 750, sólo se quitan las 49 filas anteriores cuando SQL Server revierte la transacción actual. La tabla de destino continúa teniendo las primeras 700 filas.

SQL Server optimiza automáticamente la carga según el valor de tamaño del lote, lo que puede mejorar el rendimiento. En general, el tamaño del lote debe ser tan grande como sea práctico. Normalmente, cuánto mayor sea el tamaño del lote, mejor será el rendimiento de la operación de importación masiva. Sin embargo, hay algunas excepciones. Si no hay ningún índice en la tabla de destino, un tamaño de lote más grande puede hacer que se ejerza presión en la memoria para ordenar. Además, durante una carga paralela realizada sin usar la opción TABLOCK, un tamaño de lote más grande puede producir más bloqueo.

[!NOTA] Los tamaños de lote no son aplicables cuando se exportan datos de forma masiva desde una instancia de SQL Server a un archivo de datos.

Especificar el tamaño aproximado del archivo de datos

Si no especifica un tamaño de lote para la operación de importación masiva, puede indicar el tamaño aproximado del archivo de datos para que el procesador de consultas pueda asignar de forma eficaz los recursos en el plan de consulta. Para indicar el tamaño aproximado del archivo de datos, calcule el número de filas o de kilobytes de los datos, como se indica a continuación:

• Calcular filas por lote
  Para calcular el número de filas, utilice la sugerencia u opción ROWS_PER_BATCH. Si especifica un valor > 0, el procesador de consulta utiliza el valor de ROWS_PER_BATCH como sugerencia para asignar recursos en el plan de consulta. Este valor debe ser del mismo tipo que el número de filas real.
  Aunque todas las filas del archivo de datos se copien en una instancia de SQL Server en un lote, bcp mostrará el mensaje "Se enviaron 1000 filas a SQL Server" después de cada 1000 filas. Este mensaje es sólo informativo y se muestra independientemente del tamaño del lote.
• Calcular kilobytes por lote
  Para calcular el tamaño del archivo de datos en kilobytes, utilice la sugerencia u opción KILOBYTES_PER_BATCH. SQL Server optimiza la operación de importación masiva de acuerdo con el valor especificado.

[!NOTA] Al importar de forma masiva un archivo de datos grande sin especificar el tamaño de lote o sin ninguna optimización de registro masivo, el registro de transacciones puede llenarse antes de que la operación de importación masiva se complete. Para evitar esta situación, puede aumentar el tamaño del registro de transacciones o permitir que crezca automáticamente.

Vea también

Conceptos

Optimizar el rendimiento de la importación masiva

Otros recursos

BACKUP (Transact-SQL)
bcp (utilidad)
BULK INSERT (Transact-SQL)
ImportRowsPerBatch Property
OPENROWSET (Transact-SQL)
sp_dboption (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005